CODEWITHSUNDEEP
database-design
user1854344
user1854344

Reputation: 321

Database design for multiple content types

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:

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!

Upvotes: 0

Views: 848

Answers (1)

Floris Velleman
Floris Velleman

Reputation: 4888

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).

Upvotes: 1

Related Questions