MAKING 3 TABLES WORK TOGETHER

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geeves1293 - 25 Jul 2006 17:12 GMT

I have a tables named Customers, Products and Transactions.

The products table is filled with all sorts of STANDARD cars.
The customers table is filled with customers (obviously).
The transaction table ties a customer to particular products (cars).

What if a customer bought a non-standard car (special like a hot rod or
something), which wasn't in my products table. I have so far created
another table called Special_Products, but cannot seem to connect it to the
transactions table. Below is how the transaction table looks: -

CUSTOMERID PRODUCTID SPECIALPRODUCTID
C001 P001
C001 P002
C002 P003
C003 Left Blank SP001

Any ideas on this would be most appreicated.

Many Thanks
Graham

Reply to this Message

Jeff C - 25 Jul 2006 18:23 GMT

Transaction_ID = PKey Transaction Table = Autonumber
Customer_ID = PKey Customer Table = Autonumber
Product_ID = PKey Product Table = Autonumber
SProduct_ID = PKey SpecialProduct Table = Autonumber

SProduct_ID = Number Long Integer in Transaction Table and related
one-to-many to the SProduct_ID in Special Product Table

Product_ID = Number Long Integer in Transaction Table and related
one-to-many to the Product_ID in the Product Table

Customer_ID - Number Long Integer in Transaction Table and related
one-to-many to the Customer_ID in the Customer table

Try this concept

Signature

Jeff C
Live Well .. Be Happy In All You Do

> I have a tables named Customers, Products and Transactions.
>
[quoted text clipped - 17 lines]
> Many Thanks
> Graham

Reply to this Message