Carlo,
Your question is the reason for the invention of the relational database.
Please do NOT add columns for "planned expansion". How many extra columns
would you need? 4? 10? 25? 100? Such a schema is wasteful of storage.
There are several ways to go about this. For example:
CREATE TABLE products(
product_id int auto_increment primary key,
description char(50) default "",
quantity int not null,
unit enum ("each","lb","ounce","gallon","quart"),
price decimal(5,2) not null
)
CREATE TABLE traits(
characteristic char(25) default "",
product_id int,
description char(25)
)
In the above example, when you wanted to add a characteristic for a
particular product, you simply add a record into traits and set
traits.product_id equal to products.product_id. This will make it REAL EASY
doing joins on this combo as well. For example:
INSERT INTO products VALUES(NULL, "Coca Cola",1,"ounce",0.39);
INSERT INTO traits VALUES("Liquid",LAST_INSERT_ID(),"Seltzer-based");
INSERT INTO traits VALUES("Added Chemicals",LAST_INSERT_ID(),"Sugar");
INSERT INTO traits VALUES("Added Chemicals",LAST_INSERT_ID(),"Brown Dye");
INSERT INTO traits VALUES("Added Chemicals",LAST_INSERT_ID(),"Caramel
coloring");
To get all the characteristics for a given product:
SELECT * FROM products LEFT JOIN traits USING(product_id) ORDER BY
char_1.characteristic
In another example, if multiple products will come in multiple styles, you
would create a table called STYLES and insert a record for each style
available:
CREATE TABLE styles(
color char(10) default "",
product_id int,
)
INSERT INTO products VALUES(NULL,"Bread",1,"each",1.49);
INSERT INTO styles VALUES( "White",LAST_INSERT_ID() );
INSERT INTO styles VALUES( "Black",LAST_INSERT_ID() );
INSERT INTO styles VALUES( "Wheat",LAST_INSERT_ID() );
INSERT INTO styles VALUES( "Hawaiian",LAST_INSERT_ID() );
-----Original Message-----
From: matt stewart [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 16, 2001 5:55 AM
To: 'Carlo Loiudice'; PHP DB
Subject: RE: [PHP-DB] help me on projecting some tables
not really sure how to do this, other than planning for as much as you can
in your original tables, then have a spare table with four columns - Row_ID,
Characteristic_Name, Characteristic_Value, and Product_Refer_ID.
so then if you get a new characteristic (eg colour) then you could have
values 1, colour, blue, 4(the product with this characteristic).
then the next line might be 2,colour,green,8
then 2,density,45kg/m3,8
etc.
not sure this is the best way, but the only way i can think of to have
various additional characteristics for some products.
hope it helps??
Matt
-----Original Message-----
From: Carlo Loiudice [mailto:[EMAIL PROTECTED]]
Sent: 16 November 2001 10:48
To: PHP DB
Subject: [PHP-DB] help me on projecting some tables
Hi,
I've to store in a BD some alimentary product, and
there are lot of informations like chemical, physical,
nutritional characteristics , ...
I don't know how to store this info.
So I've a lot of product with a lot of corresponding
characteristics.
I've thought about to create a table where to store
all chemical characteristics, a table for nutritional,
ecc. and put in the product table the refer ID to all
this caracteristic tables.
But in this way, there's the problem that now I'm
storing 3 chemical charact. but tomorrow I'd like to
add a new charact., and I wouldn't change tha table
structure adding a new column every time I've a new
field that I haven't expected.
Can someone show me the right strategy to resolve this
kind of problem?
Ciao, Carlo
______________________________________________________________________
Abbonati a Yahoo! ADSL con Atlanet!
Naviga su Internet ad alta velocitą, e senza limiti di tempo!
Per saperne di pił vai alla pagina http://adsl.yahoo.it
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.295 / Virus Database: 159 - Release Date: 01/11/01
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.295 / Virus Database: 159 - Release Date: 01/11/01
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
