Table relationships

[LisaMO]

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

 

[Jason Lepack]

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

 

[LisaMO]

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.

 

[Jason Lepack]

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