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
