How Set Up This Table

--0015174be3fa8df8c4047c1792bc
Content-Type: text/plain; charset=ISO-8859-1

Hi;
I have a table with products for a store to sell. I need to autogenerate
from code a table or series of tables into which I can enter (and from which
I can retrieve) the ID numbers of products which I am going to associate
together and their package price. Product associations will vary, in that
one association may have 2 products and another 20. What is the best way to
MySQL this?
TIA,
Victor

--
The Logos has come to bear
http://logos.13gems.com/

--0015174be3fa8df8c4047c1792bc--

Re: How Set Up This Table

--00504502d567a7e394047c30dcab
Content-Type: text/plain; charset=ISO-8859-1

Hi Victor.

I think that the first thing you need to consider is whether a product can
be in more than one package, and second is whether a package can be in
another package. Also, I don't know why you need to auto-generate in either
case. It's pretty simple DDL.

Case 1: product can only be in one package:

1. Add a Packages table with columns PackageID and PackageName and probably
PackagePrice.
2. Add a PackageID column to the Products table and make it a foreign key
referencing Packages.

Case 2: product can be in multiple packages:
1. Same as above.
2. Create a ProductPackages table that contains PackageID and ProductID,
both as foreign keys into Products and Packages.
3. Decide whether you want a compund PK on this new table, or you want
instead an auto-increment column that would be the PK. (There are
differences of opinion on this one, so I'm leaving it alone; I don't want to
start a religious war :)

hth,
Arthur

On Fri, Jan 1, 2010 at 5:09 AM, Victor Subervi wrote:

> Hi;
> I have a table with products for a store to sell. I need to autogenerate
> from code a table or series of tables into which I can enter (and from
> which
> I can retrieve) the ID numbers of products which I am going to associate
> together and their package price. Product associations will vary, in that
> one association may have 2 products and another 20. What is the best way to
> MySQL this?
> TIA,
> Victor
>
> --
> The Logos has come to bear
> http://logos.13gems.com/
>

--00504502d567a7e394047c30dcab--

Re: How Set Up This Table

--0015174be3fa791ced047c31128e
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Jan 2, 2010 at 11:20 AM, Arthur Fuller wrote:

> Hi Victor.
>
> I think that the first thing you need to consider is whether a product can
> be in more than one package, and second is whether a package can be in
> another package. Also, I don't know why you need to auto-generate in either
> case. It's pretty simple DDL.
>
> Case 1: product can only be in one package:
>
> 1. Add a Packages table with columns PackageID and PackageName and probably
> PackagePrice.
> 2. Add a PackageID column to the Products table and make it a foreign key
> referencing Packages.
>
> Case 2: product can be in multiple packages:
> 1. Same as above.
> 2. Create a ProductPackages table that contains PackageID and ProductID,
> both as foreign keys into Products and Packages.
> 3. Decide whether you want a compund PK on this new table, or you want
> instead an auto-increment column that would be the PK. (There are
> differences of opinion on this one, so I'm leaving it alone; I don't want to
> start a religious war :)
>

Thanks! That's reversing my thinking! I hadn't considered working the other
direction. What is a PK and a compound PK?
TIA,
V

--0015174be3fa791ced047c31128e--

Re: How Set Up This Table

--000e0cd5ca16594e84047c317906
Content-Type: text/plain; charset=ISO-8859-1

primary key is a candidate key to uniquely identify each row in a table. A
unique key or primary key comprises a single column or set of columns. No
two distinct rows in a table can have the same value (or combination of
values) in those columns. Depending on its design, a table may have
arbitrarily many unique keys but at most one primary key.

a compound key is a key that consists of 2 or more attributes that uniquely
identify an entity occurrence.


On Sat, Jan 2, 2010 at 10:05 PM, Victor Subervi wrote:

> On Sat, Jan 2, 2010 at 11:20 AM, Arthur Fuller > >wrote:
>
> > Hi Victor.
> >
> > I think that the first thing you need to consider is whether a product
> can
> > be in more than one package, and second is whether a package can be in
> > another package. Also, I don't know why you need to auto-generate in
> either
> > case. It's pretty simple DDL.
> >
> > Case 1: product can only be in one package:
> >
> > 1. Add a Packages table with columns PackageID and PackageName and
> probably
> > PackagePrice.
> > 2. Add a PackageID column to the Products table and make it a foreign key
> > referencing Packages.
> >
> > Case 2: product can be in multiple packages:
> > 1. Same as above.
> > 2. Create a ProductPackages table that contains PackageID and ProductID,
> > both as foreign keys into Products and Packages.
> > 3. Decide whether you want a compund PK on this new table, or you want
> > instead an auto-increment column that would be the PK. (There are
> > differences of opinion on this one, so I'm leaving it alone; I don't want
> to
> > start a religious war :)
> >
>
> Thanks! That's reversing my thinking! I hadn't considered working the other
> direction. What is a PK and a compound PK?
> TIA,
> V
>



--
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--000e0cd5ca16594e84047c317906--

Re: How Set Up This Table

--0016367f956cff7d3b047c31bf5a
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Jan 2, 2010 at 12:03 PM, prabhat kumar wrote:

> primary key


Oh! PK is primary key!


> a compound key is a key that consists of 2 or more attributes that uniquely
> identify an entity occurrence.


Thanks.
V

--0016367f956cff7d3b047c31bf5a--

Re: How Set Up This Table

--0016364c67c7b3e2b5047c323bc4
Content-Type: text/plain; charset=ISO-8859-1

On Sat, Jan 2, 2010 at 11:20 AM, Arthur Fuller wrote:

> Hi Victor.
>
> I think that the first thing you need to consider is whether a product can
> be in more than one package, and second is whether a package can be in
> another package. Also, I don't know why you need to auto-generate in either
> case. It's pretty simple DDL.
>
> Case 1: product can only be in one package:
>
> 1. Add a Packages table with columns PackageID and PackageName and probably
> PackagePrice.
> 2. Add a PackageID column to the Products table and make it a foreign key
> referencing Packages.
>
> Case 2: product can be in multiple packages:
> 1. Same as above.
> 2. Create a ProductPackages table that contains PackageID and ProductID,
> both as foreign keys into Products and Packages.
> 3. Decide whether you want a compund PK on this new table, or you want
> instead an auto-increment column that would be the PK. (There are
> differences of opinion on this one, so I'm leaving it alone; I don't want to
> start a religious war :)
>

If I'm understanding this correctly, if I want products to be addable to
multiple packages, then I want to create both a Packages table and a
ProductPackages table. However, it would appear I don't need to add a
PackageID column to the Products table if I'm going to create the
ProductPackages table. Is that correct?
TIA,
V

--0016364c67c7b3e2b5047c323bc4--

Re: How Set Up This Table

--001636e0b6b7a25188047c57b5dd
Content-Type: text/plain; charset=ISO-8859-1

The ProductPackages table is what is known as an associate table, and is
used to implement a many-to-many relationship. You only need it if a given
product can be in multiple packages. If not, then you can eliminate the
associative table and just add a PackageID column to the Products table.

There's also a possible tiny wrinkle that may require another new column. I
once did an app similar to yours, with the many-to-many requirement. The
wrinkle was that a given package might contain several instances of a given
product (i.e. four jars of jam, for example). Because one of the goals of
the app was to generate a packing list, and the packages were assembled only
upon demand, the packing list told the shippers what to assemble. To meet
this requirement, I added a quantity column to the associative table, so
that in Package A there might be four jars of jam and in Package B only two.

hth,
Arthur

--001636e0b6b7a25188047c57b5dd--

Re: How Set Up This Table

--0016364c67c7004efb047c5a120a
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Jan 4, 2010 at 10:40 AM, Arthur Fuller wrote:

> The ProductPackages table is what is known as an associate table, and is
> used to implement a many-to-many relationship. You only need it if a given
> product can be in multiple packages. If not, then you can eliminate the
> associative table and just add a PackageID column to the Products table.
>
> There's also a possible tiny wrinkle that may require another new column. I
> once did an app similar to yours, with the many-to-many requirement. The
> wrinkle was that a given package might contain several instances of a given
> product (i.e. four jars of jam, for example). Because one of the goals of
> the app was to generate a packing list, and the packages were assembled only
> upon demand, the packing list told the shippers what to assemble. To meet
> this requirement, I added a quantity column to the associative table, so
> that in Package A there might be four jars of jam and in Package B only two.
>

I don't think I have to do that. I've coded it but not tested yet. I think I
have the concept, however. Thanks!
V

--0016364c67c7004efb047c5a120a--