php mysql - should i add the field "category-name" to a table or not?

Question

what do you think would be performance-wise the better way to get the category-names of a news-system:

1. add an extra field for the cat-names inside a table, which allreade contains a field for the cat-ids

2. no extra field for the cat-names, but cat-ids and read in the cat-names (comma-seperated string: "cat1,cat2,cat3,cat4") into the php-file by an existing config-file and then build the cat-names with the help of the db-field "cat-ids" an array and a for-loop?

Thanx in advance,

Jayden

edit: cant seem to add a "hi" or "hallo" on top of the post, the editor just deletes it...

Answer 1

I would create a categories table like this:

Categories
-----------
category_id       name
-------------------------
1                 Weather
2                 Local
3                 Sports

Then create a junction table, so each article can have 0 or more categories:

Article_Categories
-------------------
article_id        category_id
-----------------------------
1                 2
1                 3
2                 1

To get the articles with their categories (comma delimited) from MySQL server, you can use GROUP_CONCACT():

SELECT a.*, GROUP_CONCAT(c.name) AS cats
FROM Articles a
LEFT JOIN Article_Categories ac
  ON ac.article_id = a.article_id
LEFT JOIN Categories c
  ON c.category_id = ac.category_id
GROUP BY a.article_id

Answer 2

If you are measuring milliseconds and the disk IO of your system is not extremely slow, then option 2 would yield better performance. But, we are talking a negligible gain in execution time. Since you already will be querying the DB to get the news item it would be highly optimized to just get the category name at the same time. I would add a mapping table of category-name-id to category-names. And the join on that when getting news items.

From a flexibility standpoint and the standpoint of eliminating as many possible sources of error I would also go with my above idea. Since it adds flexibility to your system and keeps all your data in one spot. Changing the name of a category would require editing one column i the database instead of editing a php config file or, if option 1 was used, updating each and every news record.

So my best advise, add a table with category-name-id to category-names mappings and then have the news-items contain the id of the category they belong to.

For performance you could then cache the data you retrieve about existing categories and other data so you don't have to poll the DB for that information all the time.

For instance. You could, instead of joining at all, get all the categories from the category table I described above. Cache it in the application and only get it once the cache is invalidated. i.e. a timeout occurs or the data in the db is manipulated.

Answer 3

I may be wrong, but since you already query the database, it's probably faster if you add a name field there..

Please also take into account that having the name in the same table as the ID provides consistency - if you have a config file you'll have to add a new category there plus in the table. Also think of possible errors that may put wrong data into your config file - if this'd be the case your category names might get messed up..

Answer 4

I think your first option is the suitable one. Because it make sense with the relationship with your data. And in a situation you want to display the category name with your news you can simply get everything by single select query with join.

So I recommend Option 1 You have mentioned.

And performance also can measure in two ways. Execution performance and development performance I feel both performance are in good position with your option 1. You don't need to do much just a one query. If you go for the option 2, then you have to load from config file, explode it with comma, then search using array elements which is time consuming.

Answer 5

I think of two possible ways.

1. Have a category table, a articles table and a relationship table, and have a many-to-many relationship between categories and articles (as described in the relationship table).
2. If you feel smart today, declare each category as a binary number (0, 1, 2, 4, 8, 16 etc), and add them in a field on the articles table. If an article has a category value of 11, it has categories 1+2+8.

I like the first solution better, quite frankly.

Answer 6

Add an additional table, that will save lots of issues in future for you. It is just the recommended way.

By the way, that idea of multiple id's in one field, don't try that way. It will give lots of code and issues which are totally unnecessary. If you really find performance issues you can always decide to take a step further and de-normalize or cache some of the data. There are lots of caching options available.