M$SQL: Database design: Product Attributes??

[<ABX]

I am creating a database of products ....

Each product has the following:

ProductID, ProductName, ProductManufacturer, InventoryCount, PricePerUnit

these products are computer parts such as processors, ram, motherboards, even entire computers... how would you suggest storing these attributes.

for a processor i would want to have attributes like:
ClockSpeed, Multiplier, FSB, ExternalClock, InternalCache, ect

for a hard drive i would want to have attributes like:
Size, BufferSize, RPM, S.M.A.R.T., ect.

Is the only way creating multiple tables for each type of device?

[szlamany]

I think that it's totally ok to have one table with columns that sometimes don't pertain to the product. That's real world - maybe not 3rd-normal-form perfection - but it will work.

On the other hand, you create a table for each type of product - with it's product specific attributes. JOINS are tremendously complex - customers can't look at the data with EXCEL - you end up developing every query yourself.

There is a tradeoff somewhere in the middle.

[brucevde]

I tend to try and adhere to the "normalization rules". Having to create queries myself is not a big deal, just a more profitable one. Besides they are not as complex as they seem. If your customers need to create ad-hoc queries, implement Views to hide the "complexity" from them, that's why they were invented. Each to their own, I guess.

Obviously, the database schema required depends on many things.
What are the needs of the "entire" application?
Does the database interface with other systems (ie accounting, retail and/or wholesale systems)?
Will the application be sold to many customers or is it custom written for one?
Is it for a specific type of business or more on the generic side?

Based on the info given, you could get away with these tables

AttributeTypes - Different attributes that can be applied to a product.
AttributeTypeId, Description, ValueType (ie string, number, date)

ProductTypes - generic product types like ram, computer, motherboard.
ProductTypeId, Description

Manufacturers - list of manufacturers
ManufacturerId, Name (you can include typical address fields but I would go with an address table).

Products - specific product that can be inventoried (is that a word)
ProductId, ProductTypeId, ManufacturerId, DefaultPrice

ProductAttributes - list of attributes for each specific product.
ProductId, AttributeTypeId, AttributeValue (possibly break this up into specific fields for each datatype)

InventoryTransactions - Sales and Purchases of each product. If you need an inventory count just sum the values in this table.