Skip to main content
A
Anonymous
April 23, 2012
Question

Database Structure For Articles and Categories

  • April 23, 2012
  • 1 reply
  • 1323 views

Hi - This is a question about database structure - please help. Thanks

For a website with many subjects or themes is it best to use 1 big table to hold or the articles or many small tables to hold articles for different subjects - I will explain what I mean.

Say my website has a 'cooking' section, a 'sports' section and a general 'blog' section.

In each section the articles will be organized into different sub-categories but I can't see a way to do this.

Case 1: All the articles are stored in 1 table. There is a second table which 'assigns' an article to a category (the cateogries themselves are stored in a separate table) and also assigns a sub-category (sub-categories stored in a separate table???) and then what if there needed to be a sub-sub-cateogry?? This just doesn't seem to work well!

Case 2: For each theme there is a separate table - Cooking Table, Sports Table, Blog Table. And for each separate table there is a categories table Cooking-Categories which assigns each cooking article a sub-cateogry (and the sub-categories are stored in a separate table also) - and what if I needed to make a sub-sub-category - then would I need to make another table? This just sounds like a lot of tables even though the end result would probably be easier!

Case 3: All categories are stored in 1 table - as well as all subcateogires of those categories as well as all sub-sub categories, etc (each with an ID)... Each article is 'assigned' a category ID

Please someone show me the light! I am confusing myself.

Thanks

Chris

This topic has been closed for replies.

1 reply

sudarshan.t
sudarshan.t
Inspiring
April 23, 2012

There is no 'best' way of creating a database. It's more logic that is involved in examining the parameters that determine what DB will suit your current requirement. These parameters may include but aren't limited to:

  • Latency
  • Performance
  • Sortability
  • Accessibility
  • Ease-of-use
  • Scalability

If I were you, for the said purpose, I'd create with 4 tables as follows:

  • Categories - this will hold your main categories
    • CatID, Name, Order ID
  • Sub-Categoties
    • SubCatID, CatID (that the SubCat is linked to), Name, Order ID
  • Sub-Sub Categories
    • SubSubCatID, SubCatID, CatID, Name, Order Id
  • Articles
    • ArticleID, SubSubCatID (can hold multiple Sub-Sub-categories) SubCatID (can hold multiple Sub-categories), CatID (can hold multiple Categotries), Name, Order ID, Article Title, Article Description, Article Thumbnail, URL Name, Metatags (for SEO, if you're looking at doing SEO)

The CatID in Article Table will usually be an INT value with limited chars, the SubCatID and SubSubCatID in Article Table will ideally be a varchar value with about 200 chars so you could assign multiple SubCatIDs to one article.

I'd like to add here that this is very much a debatable post. What suits me may not suit you and/ or the others who may read this post! But this way, I can accomplish my programming/ coding task/ CMS integration with ease-of-use.

Good luck with your DB!

~ST

A
Anonymous
April 24, 2012

Thanks for your help!

Sorry but:

1) Is your OrderID field used to manually group/order the categories? Or for another use? Because I suppose in the sub- and sub-sub- category tables there will be many unrelated categories - and so I am guessing the OrderID field is used to group them?

2) If I want to assign multiple cateogries to the article would the data be something like this: '2, 14, 28, 31'

Would I then use a php function to create an Array of the data? to 'split-up' / 'extract' the individual IDs?

Sorry I am a novice.

Thank you very much for your help and time.

Chris

B
bregent
Participating Frequently
April 24, 2012

>If I want to assign multiple cateogries to the article

>would the data be something like this: '2, 14, 28, 31'

>Would I then use a php function to create an

>Array of the data? to 'split-up' / 'extract' the individual IDs?

I would not suggest doing it that way. Storing more than one value in a field violates rules of data normalization. Even though PHP has methods for dealing with this type of data, it makes queries much more difficult. What you are asking about is a many-to-many relationship. For this, you create an association table (aka link table). This table stores the primary keys of both the article table, and the category table.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices