Database Design: Many-to-Many relationships

[rmohaisen]

Hello all,



I am sure the DB experts out there this should be simple, I cannot seem to wrap my head around it.

I want to create a database to track Purchase Agreements. Here some points:

1) One 'purchase agreement' can have many 'purchase agreement lines'

2) One 'purchase agreement' can have many 'revisions'

3) Each 'revision' can include many 'purchase agreement lines'

4) Each 'purchase agreement line' can be 'revised' many times

How would I create the tables and relationships?

[June7]

Depends what you want to document. Do you want to retain 'lines' even if the agreement is modified so they no longer apply? Possibly have a field that sets them as 'inactive'. Have a related table for the version information. This table will have purchase agreement ID as foreign key. Then in the table for 'line' have a field for the version ID. The original agreement would be version 1. Consider:

tblAgreements
AgreeID (PK)

tblVersions
VersionID (PK)
AgreeID (FK)
DateIssue

tblAgreeDetails
VersionID (FK)
LineStatement

[rmohaisen]

hi, thanks a lot for the response.

I drew out your suggestion and the logic makes sense but there are still issues I think.


Step by step here is what I would do (as the db user).

1) create a new agreement by entering the agreement ID in tbl Agreements
2) create the first revision (i.e. Version 1- first issue)
3) enter the line details

This works great so far.


But now, lets say I would like to revise "line number 1" and adjust the quantity by 1000 units (for example)

I would select the agreement, create a new revision, but i need to select that original line and simply enter the transaction (units + 1000).

This way, when i look at the line, i will see the sum of the transactions.


I hope I am explanation makes sense.

[June7]

Each 'line' is just a number field with a quantity?

You want to edit the original line, instead of marking it inactive and creating a new line with the new quantity? Or having two active lines?

I suggest a memo type field in tblVersions to describe each version, reason for changes and exactly what changed.

Exactly what is a 'purchase agreement'? Is it an order for a specific quantity of a product? Or is it just setting up an account with a vendor against which orders will be placed?

[rmohaisen]

Hi, thanks again, I am giving a more detailed description of what I "vision" below:

Purchase agreement is a actual contractual document stating terms and conditions, description of the products, fixed unit prices, etc., (majority of this information is not required in the db). I just need a method to track each agreement and the changes through the year.


The 'agreementID' is simply the effective year & a sequential number.

For example, the first agreement this year is "PA-2014-0001" and the second "PA-2014-0002" and so on.

Each agreement can have numerous 'products' which are included. So for example,

PA-2014-0001:
Line 01 - Product A, Quantity, Unit Price
Line 02 - Product B, Quantity, Unit Price


These lines cannot change (only if the revision is adding a new line to accommodate a new product). So the quanitity against each line is really a sum of the revision transactions referencing that specific agreement and line.

The only piece of information which can be 'revised' in an agreement is essentially the quantities. Prices are fixed. However as mentioned one could 'add' a new product by creating a new line in a revision.


So, I assume the agreements table would have the following fields:

tblAgreements
AgreementID (PK) {since this is a calculated field it actually cannot be a PK but lets just assume so for the time}
AgreementYear
AgreementNumber
VendorID (FK)


Now once I complete tblAgreements I need to create the first "Revision" to add the lines. Essentially a revision is just a set of transactions against those lines. So I think the revisions table would look like this:

tblRevisions
RevisionID (PK) (auto number I guess)
AgreementID (FK)
RevisionNumber (i.e. 01, 02, 03 etc)
RevisionDate
Remarks (i.e., "first issue")


Now to the actual transactions of the revision I would need a third table:

tblRevisionDetails
RevisionID (PK)
AgreementLineID (FK)
RevisionQuantity


This is the part I do not understand. I need a table of agreement details, i.e. the lines, but the first lines are not created until there is a revision but the revision must specify a line (which doesnt exist yet if it is the first revision).


tblAgreementDetails
AgreementLineID (auto number)
AgreementID (FK)
ProductID
Quantity (this quantity is calculated as a sum of the transactions in the revisiondetails table which reference this agreementline)

[rmohaisen]

Further,

If I wanted to make a line "inactive" I would do create a new revision transaction specifying a negative quantity which would make the sum zero. This way when I open the tblAgreementsDetails I see the latest status and quantity.

[June7]

That's why I called the table Version instead of Revision. The first entry will be the initial agreement date and remarks (version 1 or 0).

Have a table that is a listing of the products and pricing for the contract. Then another table that has 'transaction' entries for the authorized quantities. Possibly compound key with line ID from products and version ID. The first entry in this table will be the initial agreement quantity.

[rmohaisen]

Thanks again for your help. I have taken some of your suggestions and made a few sketches and here is were I have ended. The first two tables are related as 1:M and are mandatory to 'create' an agreement.



'Agreement Header Table'

(Primary Key Agreement Header Table)
Agreement Year
Agreement Number
Vendor Name
Latest Revision (calculated field: Look-up largest agreement revision number in 'revision table' where PK=FK)



'Agreement Detail Table'

(Primary Key Agreement Detail Table)
(Foreign Key Agreement Header Table)
Agreement Line Number
Product Description
Unit Price
Projected Quantity (calculated field: Sum of quantity transactions in 'Transaction Table' where PK=FK)






Revisions and Transactions (the naming convention I have decided to use)

- A revision is a collection of transactions
- Each 'Agreement Header' can have multiple revisions but each revision is to a single 'Agreement Header' so the relationship here is 1:M
- Each 'revision' can include multiple 'transactions' but each transaction can only be to one particular 'revision' so 1:M
- Each 'transaction' can only be against one 'Agreement Detail Line' but each 'Agreement Detail Line' can have many 'transactions'



'Revision Table'

(Primary Key Revision Table)
(Foreign Key Agreement Header Table)
Agreement Revision Number
Revision Date
Remarks



'Transaction Table'

(Primary Key Transaction Table)
(Foreign Key Revision Table)
(Foreign Key Agreement Detail Table)
Transaction Quantity (+/-)




I think this is correct. There is no redundant data, everything is entered once. If this table design is correct (and the relationships I have mentioned):


1) Which relationships should I actually create in Access. I read an article which says something about tables being indirectly related so not all have to be defined?????

and more importantly,

2) how do I build my queries, forms and sub-forms to enable me to create a revision with transactions (i.e., writing data to the revision table and transaction table simultaneously) ?

[Xipooo]

I see June7 is doing his usual great work.

I did a Youtube video on Many-To-Many relationships that might help.

http://www.youtube.com/watch?v=d5mQYTVaq7c

[orange]

You may also find this youtube video helpful re Many to Many relationships; and there are others.
Good luck with your project.

[Xipooo]

You may also find this youtube video helpful re Many to Many relationships; and there are others.
Good luck with your project.

Train Signal was pretty good. Didn't they turn into Pluralsight?

[orange]

Yes. But they've been around a while and known as Train Signal---not sure if PluralSight will get the old youtubes.