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
"characteristic"s ? 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]
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 "characteristic"s ? 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
yep, that first example was roughly what i was getting at, don't think i
explained it anywhere near as well as Rick though.
-Original Message-
From: Rick Emery [mailto:[EMAIL PROTECTED]]
Sent: 16 November 2001 14:17
To: PHP DB
Subject: 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
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
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]
