Database design for multiple content types

Question

I'm designing a database for my new project. I'm wondering if my database design is correct as I'm not sure in 100%.

Users are allowed to add content: images, texts, URLs. Content will be listed on single page, so I think creating single content type will be the best choice.

So far, I have the following structure:

post
  - id
  - type ENUM
  - title
  - image NULL
  - text NULL
  - URL NULL

post_image
  - id
  - filesystem
  - filename

post_text
  - id
  - body

post_url
  - id
  - link

Of course, I have 1:1 associations created for post tabke and post_* types tables. I use Doctrine as ORM in my application. To get content, I have a custom method that checks for type and returns correct content (text, URL or image path).

Is it the approach you'd recommend?

Two alternatives I have in my mind:

creating nullable image, text and url fields in post table
creating a single content field

While using the two above methods I get very simple and manintainable database structure, but this may cause some problems in the future.

Looking forward to hear some community voice.

Best!

This depends on a lot of things, what kind of usage are you expecting (10 users? 1 million?). An advantage of this design is that you can use multiple types of content in your post. — Floris Velleman
– Floris Velleman, Commented Jul 7, 2013 at 14:22
@Floris Velleman - Users? Less than one milion, for sure. Another thing that will never change is that there can be only single type of content per post. The structure I currently have gives me a lot more flexibility, I'm just not sure if it's the best option available. Thanks for your comment. — user1854344
– user1854344, Commented Jul 7, 2013 at 14:26

Floris Velleman · Accepted Answer · 2013-07-07 14:45:14Z

Like you have stated in your comment this database design will give you a lot of flexibility. It includes the possibility to use multiple types of content into one post. As you mention that this will never be a used functionality you can decide to alter your database design based on your needs. Flexibility is unlikely to cause problems if you keep it in mind or document it.

An alternative design would be creating a single table like you said. Using a single table has quite a big downside in that it is often pretty empty. For example say you were to put everything in a table called: "content". Now when a user posts a url you will have a database row which contains empty values for image and text, which will take up more space. A big advantage to this approach is that it is very easy to access (as you will only have 1 table it will likely result in easy queries).

The third possibility is that you implement the post table into your sub-items. Which would result in:

Post_image
Post_text
Post_url

Using this design will only allow you to have 1 type for the post (as you will either create a row for image, text or url). A disadvantage to this design is that you will have a lot of the same columns (especially when you decide to add more items).

Collectives™ on Stack Overflow

Database design for multiple content types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related