A little advice with database organization

[haynest]

Hello guys. Quick question. I have a project for school that I have to accomplish. I would like to know what you all think would be the best way to set up my database. The site that my group picked to do is supposed to be kind of like ebay, but not so intense and definitely not as many products. I'll have just a few categories like furniture, entertainment, books, electronics, and maybe just a few more, it is just a simple project. I am thinking about having a customers TABLE, a products TABLE, and maybe and orders TABLE to keep track of things. The main thing I need advice on is the products table. Would it be better to create multiple tables based on the product? Such as furniture, books, and electronics table? And then stuff all the different types of furniture in one database? Or should I do a database for each different category, and another database for the type of product, such as furniture TABLE, lamp TABLE, couch TABLE, carpet TABLE, and so on?

[frost110]

TABLE: category
catid
catname
catdesc

TABLE: products
prodid
catid
prodname
proddesc
prodprice


CatID 1
CatName Furniture
CatDesc Furntiure for home and office

ProdID 1
CatID   1
ProdName Computer Desk
ProdDesc  Solid oak computer desk.
ProdPrice $500.00

It would be horrible and very unwise to create a table for each when you can just tie them to each other in the database. This will work great. The orders table would look like this

TABLE: orders
orderid
custid
dateordered


TABLE: orderitems
orderitemid
orderid
prodid


TABLE: customers
custid
custname
custaddress
custemail
... etc

Questions let me know.

--FrosT

[haynest]

That looks like a very good way to do it. I was trying to follow the process of db "normalazation" to try and get this project going. I only have 1 question. What about sub categories, such as furniture->couches, or furniture->chairs, and furniture->tables. It looks to me like I would just put each of the sub categories into into the category TABLE as well. And the category and product tables would be linked together by the catid? And this way it does not seem like I would have any redundant data.

[haynest]

That looks like a very good way to do it. I was trying to follow the process of db "normalazation" to try and get this project going. I only have 1 question. What about sub categories, such as furniture->couches, or furniture->chairs, and furniture->tables. It looks to me like I would just put each of the sub categories into into the category TABLE as well. And the category and product tables would be linked together by the catid? And this way it does not seem like I would have any redundant data.
Another thing too, say I have a category called 'Entertainment'. I would have a TABLE named 'entertainment'. but under that category the product would be 'tv'. am I to create anther TABLE called 'tv' or just add all of the tv's under the products TABLE. Because I also have to make a user interface to the site that has to allow someone not so computer savy to be able to add products to the site. I think that would be more easily done if the user was able to add the product right into a certain category later on.