Table relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I wonder if you can help me, I am trying to create a database for a printing
company.

What I have so far are the following tables:

tblClients
ClientID (PK)
ClientName
Address etc

tblProducts
ProductID (PK)
ProductType
Colour etc

tblJobs
JobID (PK)
ClientID
JobNumber
DateBooked etc

I believe I also need to have two junction tables so have done the following:

tblClientProducts
ClientProductID (PK)
ClientID
ProductID
Client - lookup
Price

tblJobsProducts
JobsProductsID (PK)
JobsID
ClientProductID
Price

Every client has a different price for each product, therefore I believe the
prices need to be held in the junction tables, is this correct?

I need to set some of the fields in my junction tables as foreign keys, am I
right in thinking to do this I highlight all the fields and set as primary
keys having only only JobsProductsID for example as autonumber and the others
as long integers??

Am I going the right way about this or is there an easier way?

Thanks

Lisa
 
On a quick look...

1. Take Client - Lookup out of tblClientProducts

2. You don't require tblJobsProducts. Just change clientID in tblJobs
to ClientProductID.

Client gets quoted price on a product. (tblClientProduct)
Job is built based on a price quoted to a client (therefore Jobs are
built based on tblClientProduct.

Structure:
tblClients
ClientID (PK)
ClientName
Address etc

tblProducts
ProductID (PK)
ProductType
Colour etc

tblJobs
JobID (PK)
ClientProductID (FK)
JobNumber
DateBooked etc

tblClientProducts
ClientProductID (PK)
ClientID (FK)
ProductID (FK)
Price
Effective Date *

* Note: You may change the quoted price on a product with a given
client in the future. This effective date will allow you to keep
historical data for the old price as well as maintain the new one.

Cheers,
Jason Lepack
 
Hi Jason

Thanks for that, I have made the changes and all has related ok except for
relating ClientProductID in tblJobs to ClientProductID in tblClientsProducts.
Is comes up saying its an intermediate relationship???

Where have I gone wrong?

Thanks

Lisa.
 
I'm pretty sure you mean "indeterminate"

Is ClientProductID in tblClientProducts the primary key? One of the
fields in a relatioship needs to be unique.

FK means that the field links to a primary key in another table.

Cheers,
Jason Lepack
 
Back
Top