I am currently designing a project tracking data base for a company and I have a couple questions.

Lets say that I have a T-shirt company and I am trying to track client orders. (This isn't the actual database just a close approximation) Lets say that a client chooses from a list of different prints. Now the way the company works is that with EACH print per Client we AUTOMATICALLY ship sizes x-small, small, med, large, x-large. A sales rep for this company just enters in quantities for these different sizes. ALSO we offer a 20% discount for one of the sizes of one of the prints per client.

SO here is the question, I have set this up in access in two different ways.

SalesReps(EmpID PK, Name, Position ...)

Clients(ClientID PK, Name, Address, EmpID FK to SalesRep, ...)

Prints(PrintID PK, CreatorName, Size, Colors, ...)

ShirtSizes(ShirtSizeID PK, ShirtSizeName, Dimentions)

Order({ClientID FK, PrintID FK, ShirtSizeID FK} compound PK, Date, payment, Quantity, Discount ...)

-------OR--------

SalesReps(EmpID PK, Name, Position ...)

Clients(ClientID PK, Name, Address, EmpID FK to SalesRep, ...)

Prints(PrintID PK, CreatorName, Size, Colors, ...)

ShirtSizes(ShirtSizeID PK, ShirtSizeName, Dimentions)

Order({ClientID FK, PrintID FK} compound PK) //compund to make sure that one client cant order more than one of the SAME print

PrintSizes([{ClientID, PrintID} Link to Order table, ShirtSizeID] compound PK, payment, Quantity, Discount ...) //So that the same size cant be attached to the same print PER client.

I understand that there is some redundancy in the second version of this because everything that is on the PrintSizes Table is ALSO on the Order table. However within Access this database functions the way that I would like it to function.

So after reading that I realize that it might be hard to understand hopefully this hleps. Each client can have MANY prints, and each PRINT has many ShirtSize (in actuality, every Print has EVERY shirt size, but I am not sure how I can make it so that when you add a print to a client Access automatically attaches every shirt size) A client cant have two of the SAME print and a Print cant have two of the SAME shirt sizes.

Client : Print : ShirtSize Quantity Discount?


------: -----: ShirtSize Quantity Discount?
------: -----: ShirtSize Quantity Discount?
------: Print : ShirtSize Quantity Discount?
------: -----: ShirtSize Quantity Discount?
------: -----: ShirtSize Quantity Discount?
etc.

Client : Print : ShirtSize Quantity Discount?
------: -----: ShirtSize Quantity Discount?
------: -----: ShirtSize Quantity Discount?
------: Print : ShirtSize Quantity Discount?
------: -----: ShirtSize Quantity Discount?
------: -----: ShirtSize Quantity Discount?
etc.

Also is there a way that I can make it so that the discount is only applied to ONE shirt size per ONE print per ONE client?

Thank you in advance for any and all help that you have.