Database Design help

[bean45]

I want to design a database for a computer store to store all types of computer hardwares such as harddrive, memory, monitor etc.. I have made a product table to store all the common attributes such as price, name, model number and I also want to store different attributes for each hardware types such as Capacity for harddisk, Resolution for monitor, Transfer Rate for Modem etc. I have build a lookup table to store all these attributes but that would create a very long table. Can someone suggest me a better way to store solve this problem?

Thanks very much

bean

 

[repete]

If you're not wanting to interogate some of the product specifications on a comparative basis, then you could store multiple specifications in a "memo" type field (i work with foxpro dbf type databases).

This overcomes the problem of a seemingly limitless definition of product specifications, but of course does not help ypou if you want to compare and evaluate products for their different specifications.

Just a thought...
Pete Bloomfield
Down Under

 

[bean45]

Yes I do need to compare and evaluate products. I have thought of creating different tables for each type of products, but in this case, I have to create a lot of tables. I just wonder if that's the only way to store those attributes without causing data redundancy?

bean

 

[repete]

I understand your concern over many tables, the following isn't I think great, but it may help you with your thoughts.

After your basic common fields, have a bunch of fields available to store the types of attributes that do not cover all types of products, with each attribute having 2 fields related to it.

One field would be a char field, and contain the 'name' of the attribute (eg 'Capacity', 'Speed', 'Resolution'.)

The second field would contain the data (eg 40gig, 56k, high)

The name of the fields would be (something like) attribname01, attribdata01. (and of course for more attributes attribname02, attribdata02; attribname03, attribdata03 etc)

This way, your database only needs to have the number of fields ( x2 ) of the most attributes for one product (say to attribname12, attribdata12) plus the comon fields for each product (price, name, model number)

for a hard drive, attribname01 would be 'Capacity'
for a modem, attribname01 would be 'Speed'
etc

If you would not be doing data evaluations across product types, this would help. (and I can't see why you would want to do evaluations across product types)


You would need a 'product type (or category)' field so that all products with the same product type would have the same attribnamexx information (eg 'Speed'), and record its own specific information in the attrindataxx field (eg 56k, 33k). Pete Bloomfield
Down Under

 

[DanJR]

Hi there,

I would consider storing a list of Attributes for each Product Type. e.g.

(The reference to PK as just there to show the relationships - i'm not suggesting the use of an autonumber of a composite key)

Table1: tblProdTypes
-TypeID (PK)
-TypeName

Table2: tblAttributes
-AttrID:
-TypeID: (FK)
-AttrName:

Store a list of products, with reference to type of product:

Table3: tblProducts
-ProdID
-ProdName
-ProdType (from TypeID)
-ProdPrice
-ProdModel
-other common fields to all products

Then store the product specific attributes in a one-many relationship to the Products Table

Table4: tblProdAttr
-ProdAttrID (PK)
-AttrID (from Table 2)
-AttrValue

This should allow comparison between attributes. Also, by storing the attributes by 'type', this will allow quick filtering e.g. combo box, when doing data entry on products.

This would be my inital starting point in the logical desgin - which is along the same lines as you were linking...ie, i haven't come with a better way

Cheers,
Dan