Basic Database Structure Concept

[mcloan]

I am in the process of learning php and sql.  I am trying to conceptualize how a basic design may work. Here is an example my questions are below the example.  Assume I have an online store selling books, which I wanted to have the flow of the site go from main broad level to a sub category which then flows into a product page.  For example I may have a main category as computer books, which flows into a sub category page of internet, networking, mainframe etc.  Each sub category page will show a listing of the books in the sub category.  Then when you select a book it will take you to a product page.

This is what I am thinking as far as design from a database standpoint (3 tables).

[b]1.  Category table[/b]
  a. Category id (key field)
  b. Category Name
  c. Category description (for meta description of the page)
  d. Category Title (for meta Title of the page)


[b]2.  Sub Category Table[/b]
  a. Sub Category Id (key field)
  b. Category id (key field)
  c. Sub Category Name
  d. Sub Category Description (for meta description of the page)
  e. Sub Category Title (for meta Title of the page)


[b]3.  Product Table[/b]
  a. Product Id (key field)
  b. Sub Category Id (key field)
  c. Category id (key field)
  d. Product Description (for meta description of the page)
  e. Product Long Description (for meta description of the page)
  f. Product Title (for meta Title of the page)
  g. Product Features


Here are my questions:

1.  Would you expect this be an efficient database design?  If not what is a better way?

2.  What having meta description data and page titles in a database the best way to handle these items?

3.  What is the best way to build site navigation based on this design?  Just pull from category tables and loop through an array?  Also can DHTML menus be used if I pull my navigation from a database?

Your assistance with helping me understand these concepts would be much appreciated.

Thank you.

[cmgmyr]

1.  Would you expect this be an efficient database design?  If not what is a better way?

- Yes, the only thing that I see that is not totall efficient is that you have the category id in the product table. Since you can do a query with the sub category to get the main category this is not totally needed. BUT I think I would keep it just because it would be easier to make queries and to connect data together without having to do a bunch of joins.

2.  What having meta description data and page titles in a database the best way to handle these items?

- Yes, this is a very good way to keep track of meta data with your items. If you want you could make another table to meta data, but I would keep it how it is.

3.  What is the best way to build site navigation based on this design?  Just pull from category tables and loop through an array?  Also can DHTML menus be used if I pull my navigation from a database?

- Pull from the category/sub-category tables with a loop, use CSS to format them :)

Good job on the layout, you have a great start.

-Chris

[mcloan]

Chris Thanks a lot, I am glad I am on the right track!!

[c4onastick]

Looks good! One minor point I might add:

I'd add a fourth table that holds the product category relationships (if they're more than one to one, otherwise what you have will be great.)

[b]Product_Category[/b]
  a. Cat_id
  b. SubCat_id
  c. Product_id

This will allow you to more easily work with products that could be in multiple categories. If you were to set it up like that, you could remove the subcat and category id's from the product table.