I am in the beginning design phases of a new web site. I have a question on database structure. I am setting up a mysql database using a parent - child hierarchy or (one to many) with a categories table being the parent.
I will have approximately 10 tables. I am thinking that each table would need to contain a column referencing the id from the categories table as a foreign key. Example of the categories table structure:
Category Table Structure
catID | categoryName | urlPath
1 News index.php
2 Videos videos/index.php
3 projects projects/index.php
Examples of tables will be news, videos, projects etc. Table structure for each table would be as follows:
News table
newsID | catID | title | post_date | article_body
Videos table
vidID | catID | title | post_date | vidURLPath | description
Projects table
projID | catID | title | post_date | proj_body
I am wondering since the News table can cover news on multiple categories I would need the catID referenced from the categories table but what about in my other two examples above?
My guess is I probably wouldn't need a catID for the other tables since each table is in and of itself "a category". So the value of catID would always be the same. That is, the videos table with 10 records in the table, each record would have a catID of "2"
I would like the user to be able to click on a list of menu links to view information by category (information pulled from the categories table) and next to each link would display a total number of records in that particular table as below:
News (101)
Vidoes (10)
Projects (17)
in order to display such a menu, would this mean that I would have to code sql statements for each table represented by a category? Hope that makes sense.
I realize that when configuring a data-driven web site that one key feature is to have a properly defined database. Am I on the right track, should I have a catID for each table or do without it?
Any advise or recommendations would be appreciated.
Thanks
Gerry
