So I'm starting on a new script, and am starting to think about table structure and design. Let me lay out my specific question:
Let's say you have two tables:
Table One (categories) has the following columns:
catid
category (the name of the category)
Table Two (stories) has the following columns:
storyid
catid OR category (this is my specific question)
storytext
(and various other columns that don't have anything to do with my question 😉
Now, let's say that someone writes a story, and the script records the relevant category info into the database--should I use the catid or the category name? Pro's and Cons of each:
Using the catid:
Pros: Allows the admin to change the category name at a later date, and that change will reflect correctly in the display of the story (assuming that one wants the category info to display correctly. Also, a unique identifier, which is always a good thing.
Cons: If you want the category name to display in the story results, you have to do another query to the database to match up the catid with the category name. If several stories are listed on a page, that is several SQL queries, which may be a bad thing.
Using the category name:
Pros: The cat name is right there in the story table, and so only one query is needed to display both the story results and the name of the category.
Cons: No unique id. If the category name is changed, it will not reflect that in the stories table.
So. Which is better? Are my client's concerns over multiple queries valid? Is there another way to do this that I'm not thinking of, i.e. is it possible to query multiple tables at once and match up the catid with the category name AND display the story results with the category name at the same time? Any input would be appreciated...I haven't worked with a complex table structure before, and would appreciate any guidance or advice...
