Database design

[mat]

Here is an exmaple of data that could be put in a database table / tables.

http://www.etown.co.nz/nzPlist.htm

'all digital cameras', but assuming there were other categories of equipment for sale as well perhaps 'cd players','monitors' and 'light bulbs' or something.

How would you design, set out the tables? how many, for which and what data types?


I would think:

ProductCategory table
catergoryID
catergoryName


Products table
ProductID
Brand
Model
Price
EffectivePixel
Sensor
Zoom
..whatever is appropriate for products in other catergories i.e:
monitor size
wattage etc


BrandName table ??
BrandNameID
BrandNameTitle


I'm not sure if the brandname table should be there? in this case alot of the cameras are from the same brand although you could just have this as a field in the products table, you would be repeating the brandName quite a bit,

Also for things like 'BrandName', 'Model' and 'categoryName' etc, I would specify 'TINYTEXT' this is something like 1-255 in length i think, would it be a performance hit to have TEXT(100) instead if you knew the data would be under 100 Character which in the before mentioned is probably true?

I just wanted to get some suggestions and info, insight on how others would do this, I've never really spent time on the actual structure of my tables and have always just gone for same old datatypes whether they are truely appropriate or not. Since I've only mostly been playing around thats o.k but when creating a commercial site this could of course make a huge difference to performance. There is quite a bit of mySQL/SQL documentation around but not much 'real-life' type info or examples to learn from. it's more like "this datatype supports numbers only and.."

Perhaps tips from anyone who has done this for an e-commerce type website. How you would go about it in the case of this example?

mat,

[bcarl314]

Do a search on google for "database normalization" that should help you out a little.

[mat]

I have the tables worked out, I have a few questions one which datatypes to use

for these columns and similar:


catergoryName
Model
Price
EffectivePixel
otherFeature
Sensor
BrandName

which contain a bit of text/numbers but not too much should I use
something like:

VARCHAR(100)

or

TINYTEXT

or

TEXT(100)


?


and for the column 'overview' which will contain an overview of that product probably 4 or more paragraphs of text should i use
blob?