Re: Simple data modelling problem.

keep in mind the most common answer in data modeling is, "it depends". this is another way to say there are often many ways to provide a solution. the determining factors are based on the specific details of the environment.

suppose you have three suppliers. suppose there are ten products supplied and each supplier may supply all 10 products. if you were to put the supplier and the product in one table (which is called "denormalized"), you would have a table with 30 rows ( n x m ). however, if you have three tables, Supplier, Product, and ProductSuppplier, you would have three rows in the Supplier table, ten rows in the Product table, and 30 rows in the ProductSupplier table. the difference is, however, the amount of data stored (which translates into access time for data to be loaded from disk to memory) for each row.

if for each product a description field of 256 characters were required, you would have to store 256 x 3 bytes to describe one product (three suppliers) rather than storing it only once in a Product table and then storing only ProductID and SupplierID in the ProductSupplier table. then take into account all of the fields that are replicated for each product supplied by more than one supplier. 3 x 10 is such a trivial example, but if these numbers were 3,000 x 10,000 the differences in storage and access time may become quite significant.

if there is a business rule that each product is supplied by exactly one supplier (which means the entity may pay more due to lack of competition), then denormalization is more effiicient. however, this is seldom the case, in which case a three table solution is more efficient.

so how does one perform a select?

create table Supplier (supplierID, supplier_name, supplier_addr, ....) create table Product (productID, product_name, product_SKU, product_descr, ...)
create table Product_Supplier (productID, supplierID)

notice the Product_Supplier table is a truth statement which says,

"product X is supplied by Supplier 1"
"product X is supplied by Supplier 2"
"product Y is supplied by Supplier n"

etc..., but only when such conditions are in fact true.

selecting all suppliers or all products is trivial...

    select * from supplier; -- returns all fields for all suppliers     select * from product; -- returns all fields for all products

now one must answer, "which suppliers supply product X ? "

    select supplier_name
    from supplier s, product p, product_supplier j     where s.supplierID = j.supplierID

    and     j.productID = p.productID
    and     p.product_name = 'X'

how the above query works:

1. find the productID of the product in the Product table where the product name is X.
2. using this productID, find all rows in the Product_Supplier table having this productID.
3. for each row having the given ProductID in the Product_Supplier table, use the corresponding supplierID field to select corresponding rows from the Supplier table.

"Lee Osborne" <osbornelee_at_hotmail.com> wrote in message news:990006597.13581.0.nnrp-01.c2de1f0e_at_news.demon.co.uk...
> Also!
>
> Can't I just store all of the products for every supplier in the product
> table, and for each product store the relevant supplier?
>
> Thanks,
>
> Lee.
>
> "Lee Osborne" <osbornelee_at_hotmail.com> wrote in message
> news:990005765.13303.0.nnrp-01.c2de1f0e_at_news.demon.co.uk...
> > Thanks Tasxa,
> >
> > Could you give me some example select statements for say, display a
 supplier
> > and their associated products?
> >
> > Hope no-one is laughing at me! ;-)
> >
> > Lee.
> >
> >
> > "Tasxa" <taska_at_mailno.spam.com.net> wrote in message
> > news:9dtg32$dgn$1_at_news.eunet.yu...
> > > Assuming that you have TSupplier, TProduct, create a table TSupProd
 with:
> > >
> > > ID (key, autonumber)
> > > IDProduct (from TProduct)
> > > IDSupplier (from TSupplier)
> > > and other fields necessary
> > > HTH
> > > Tasxa
> > > "Lee Osborne" <osbornelee_at_hotmail.com> wrote in message
> > > news:990002098.11778.0.nnrp-01.c2de1f0e_at_news.demon.co.uk...
> > > > Hi all,
> > > >
> > > > I'm new to this so bear with my stupidity! I have a list of
 suppliers
 whose
> > > > table structure is no problem. However, each supplier needs to have
 a
 table
> > > > of products that they supply. Surely I don't need to create a
 products
 table
> > > > for each supplier? Help!
> > > >
> > > > How do I solve this problem elegantly?
> > > >
> > > > Many thanks,
> > > >
> > > > Lee.
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Wed May 16 2001 - 14:31:07 CEST