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]
RE: [PHP-DB] help me on projecting some tables
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]
RE: [PHP-DB] help me on projecting some tables
A couple of questions: INSERT INTO traits VALUES(Added Chemicals,LAST_INSERT_ID(),Sugar); 1. Will LAST_INSERT_ID() work reliably in a multi-user environment? What happens if you're in the middle of inserting a dozen records and someone else inserts a record? Does MySQL keep the LAST_INSERT_ID()s separate (since the web application will see both transactions as being done by the same user)? 2. Is there any benefit to having a 3rd table to keep track of characteristics ? I guess that quesiton is best answered by examining the business needs. If the characteristics are few and don't change often, it seems to me that you'd want a 3rd table to keep them. That way, the spelling is similar, etc, and therefore you can do (accurate) queries based on the characteristics (if the need ever came up) TIM -Whenever you hear a man speak of his love for his country, it is a sure sign he expects to be paid for it. -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] 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); -- 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]
RE: [PHP-DB] help me on projecting some tables
1. Don't know answer to question. However, surround the INSERTs with LOCK/UNLOCK Table commands to prevent the problem you desecribe. Or, the script (PHP,PERL,C,Python...) can insert product record, retrieve the product_id, then use that for the product_id into the traits table. -Original Message- From: Tim Foster [mailto:[EMAIL PROTECTED]] Sent: Friday, November 16, 2001 9:42 AM To: PHP DB Subject: RE: [PHP-DB] help me on projecting some tables A couple of questions: INSERT INTO traits VALUES(Added Chemicals,LAST_INSERT_ID(),Sugar); 1. Will LAST_INSERT_ID() work reliably in a multi-user environment? What happens if you're in the middle of inserting a dozen records and someone else inserts a record? Does MySQL keep the LAST_INSERT_ID()s separate (since the web application will see both transactions as being done by the same user)? 2. Is there any benefit to having a 3rd table to keep track of characteristics ? I guess that quesiton is best answered by examining the business needs. If the characteristics are few and don't change often, it seems to me that you'd want a 3rd table to keep them. That way, the spelling is similar, etc, and therefore you can do (accurate) queries based on the characteristics (if the need ever came up) TIM -Whenever you hear a man speak of his love for his country, it is a sure sign he expects to be paid for it. -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] 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); -- 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]
