CODEWITHSUNDEEP
sqlforeign-keysrelational-databaseforeign-key-relationship
Eric Dubé
Eric Dubé

Reputation: 482

Best solution for relational db design with different types of content

Edit: title is pretty broad. It would be more accurate as "Best solution for relational db design where users can post content, and the available fields would vary depending on the type of content".

I'm working on a website where users can post different types of content. All posts have text and a feed_id value relating it to another (many posts to one feed). Based on the idea that some posts will only contain text, what would be the best solution?

Ideas I've thought of so far:

Problem: How would text-only posts be queried?

Problem: Doesn't seem very efficient. For every post found in text_posts, the post would again have to be found in posts to get the text field. Then again, this was already the case with special types like pictures. Is there a proper way to accomplish this with JOIN?

problem: Lots of null fields, and rules have to be maintained by the application.

cons: rules have to be maintained by the application (case sensitivity, posts should only have a valid type), harder to query pro: this field could also represent different views for similar content (i.e. I could make a value for post_type called "blog_entry" which is exactly the same as a text post, but have the application show the content it differently). This pro has a bit of a code-smell to it though...

Also, if it makes any difference, my application is written in PHP.

Edit: It seems that my first solution, "Add a table for each special type that refers to a post, leaving unreferenced posts to be text posts", would work fine if I queried all posts like this:

SELECT posts.title, posts.text, picture_posts.id, picture_posts.src FROM posts
LEFT JOIN picture_posts
ON picture_posts.post_id=posts.id
ORDER BY posts.date DESC

and then some pseudo-code for the application would look like this:

print(posts.title, posts.text);
if (picture_posts.id is not null) {
    showPicture(picture_posts.src);
}

Should I use this design?

Upvotes: 0

Views: 86

Answers (1)

Anon
Anon

Reputation: 10918

That's a very open-ended question. If you are doing this as an academic exercise, I'd recommend reading about database normalization and database normal forms. If you are doing this to produce a functional website, it sounds like you are reinventing several wheels from scratch and your time would be better spent searching for an off-the-shelf solution that meets your needs.

Upvotes: 1

Related Questions