CODEWITHSUNDEEP
mysql
panthro
panthro

Reputation: 24069

Separate tables for categories or use a join?

My site has various articles, always and only shown under specific categories, e.g. news, sport, business.

Would it be more efficient to have a table design where all articles were in a single table with a relational link to an article types table, then I could do a join to get all sports articles for example.

Or would it be more efficient to have separate tables for each type of article, one for news, one for sports and one for business.

Please note, I will never have any more than these three categories.

--- Edit

The table structure would be the same. e.g. title, article content, date etc.

Upvotes: 0

Views: 45

Answers (1)

Caius Jard
Caius Jard

Reputation: 74680

If the only difference between a news article, a sport article and a business article, is the category (i.e. all kinds of article only ever have a Title, Author, PublishDate, WordCount, Body, CommentsAllowed etc and there are no specialised columns) then you use one Articles table with a CategoryID

If a sport article is so massively different to a business article that the only property they share is the Body, then different tables would be the way to go; you don't want an article table that has a bunch of null columns on row A "because those columns are only for sports articles and this is a business article" and a different bunch of nulls on row B "because this is a sports article and those columns are only for business"

it's acceptable to have the occasional null.. Like if you have a rule that "people can write tweets to the author of a business article because they'll be nice and polite, but putting the author's twitter handle on a sports article will incite rival team thuggery and fighting" then your Articles table AuthorTwitterHandle column can be null for a sports article and populated for a business article - but essentially here it's not specifically a property of a business article that can never be applied to other articles. One day a new CEO might decide to enable twittering on sports articles.. if you can think of e.g. 10 legitimate extra properties that a business article would have that other articles wouldn't, you can always make an extra table for those properties, and relate them back to the articles table via BusinessArticlesExtraProperties.ArticleID = Articles.ID type join.. This is something akin to object oriented programming's inheritance mechanism

Upvotes: 1

Related Questions