Mark Sargent <[EMAIL PROTECTED]> wrote on 06/03/2005 03:04:23 AM: > Hi All,
> I'm not sure the best design approach for a product table for a number > of different hardware devices. Some devices have IP, Port, CPU, Memory > specs, whilst some don't. Current desing is below. > Products: > product_id > product_name > maker_id > controller_id > product_type_id > product_model_number > product_serial_number > product_age > condition_id > product_price > product_sold_price > product_sold > product_auctioned > product_qty > product_last_updated > product_data_output > product_desc > ProductTypes: > product_type_id > product_type_detail > Makers: > maker_id > maker_detail > Some products we'll have are, switches/routers/dedicated > servers/firewalls etc. Should I just make a Specs table, or, specific > table for each type of product.? Appreciate any thoughts on this. Cheers. > Mark Sargent. In my opinion, you have identified the "core" descriptors for any product already (your Products table). Now what you need to be able to provide is a list of descriptors and their values: (# of ports, 16), (# of cpus,2), (# expansion bays,6), (expansion option 1, TELCO-card), etc.... That kind of flexible design (where you are not limited to the number of extra items you can tack onto a Product) fits best into it's own, vertically organized table (ProductFeatures, below) CREATE TABLE Features ( feature_id int auto_increment primary key , feature varchar(75) not null , UNIQUE (feature) ) ENGINE=InnoDB; CREATE TABLE ProductFeatures ( prodfeat_id int auto_increment primary key , product_id int not null , feature_id int not null , value varchar(255) , UNIQUE(product_id, feature_id) , KEY(feature_id) , FOREIGN KEY (product_ID) References Products(product_id) , FOREIGN KEY (feature_ID) References Features(feature_id) ) ENGINE=InnoDB; Just to keep your data (and your GUI design) more manageable, I normalized the list of possible features into their own table. That way you don't have someone typing in "# of ports" for one product and "port count" for another. The UNIQUE index on the ProductFeatures should help to prevent having more than 1 of the same feature described for the same product. The FK constraints prevents you from adding non-existent features to a product that may or may not exist. Just my 2 cents... Shawn Green Database Administrator Unimin Corporation - Spruce Pine