Designing my database, have a few questions
Posted by:
Marc M
Date: July 27, 2005 04:45PM
Ok, I've been studying database design for my php application, and I think it's starting to take shape. However, I'm taking my sweet time to design it right, because I don't want to have problems later. I want a very solid foundation.
My main table is my products table, or catalog. It consists of:
---------
product_id : int(11) not null - auto - primary
vendor_id : int(11) not null - default 01
product_type_id : int(11) not null
category1_id : int(11) not null
category2_id : int(11)
product_files_id : int(11)
product_images_id: int(11)
product_name : char(32)
product_price : float (11,2)
product_active : tinyint(1)
keywords :
short_description :
long_description :
date_added : datetime -
date_modified : datetime -
----------
ok, so now some questions.
1. Would it be wise to put keywords, short and long description in it's own table?
2. I've already got 7 id fields. Is there any logical limit to this approach, or am I ok with it?
3. product type is like a master category. The categories available to a product will vary depending upon the product type. Right now I have that in it's own table. But with categories, I think it would make sense to put them all in one table. There would be a field to designate parent cats and perhaps product type. I want to be able to assign two categories to a product. So, can category1_id and category2_id both refer to a single category table?
4. for price, should I use decimal or float here?
5. Finally, I've seen different approaches to using char and varchar for fields such as name. I've read that char is faster, but takes up a little more space. Is the trade off worth it?
Thanks for any insight or advice you might have regarding this. It's greatly appreciated.
-marc
------------------------
Inner space - what a trip.