Many to Many relationship

  • Thread starter Thread starter an
  • Start date Start date
A

an

Hello.
Happy new year for all!
I have
T_Products: IdProduct, Product, UnitPrice
T_Clients: IdClient, Product(acquired),
T_Places: IdPlace, Place, Km

I'm difficult is to make relationships between theese
tables, because:
One Client acquire one or Many Products
but
Equals Products it may be to Many Clients
and
One Place has one or more Clients.

Thanks in advance.
an
 
In ACCESS, we use "junction" tables for many-to-many relationships, which is
what you want in your example.

Look at this setup:

T_Clients
IdClient (primary key)
NameClient
IdPlace (foreign key to T_Places)

T_Places
IdPlace (primary key)
AddressPlace

T_Products
IdProduct (primary key)
NameProduct
UnitPriceProduct

T_ClientProduct (this is the junction table to relate clients and
products)
IdClient (composite primary key with IdProduct) (foreign key to
T_Clients)
IdProduct (composite primary key with IdClient) (foreign key to
T_Products)

This last table lets you relate a client to a product. You can have multiple
products for each client, and multiple clients for each product.
 
Ok!

I'm go to try. :-)
Many thanks Ken Snell

an
-----Original Message-----
In ACCESS, we use "junction" tables for many-to-many relationships, which is
what you want in your example.

Look at this setup:

T_Clients
IdClient (primary key)
NameClient
IdPlace (foreign key to T_Places)

T_Places
IdPlace (primary key)
AddressPlace

T_Products
IdProduct (primary key)
NameProduct
UnitPriceProduct

T_ClientProduct (this is the junction table to relate clients and
products)
IdClient (composite primary key with IdProduct) (foreign key to
T_Clients)
IdProduct (composite primary key with IdClient) (foreign key to
T_Products)

This last table lets you relate a client to a product. You can have multiple
products for each client, and multiple clients for each product.

--
Ken Snell
<MS ACCESS MVP>





.
 
Back
Top