Complex Relationships

[Mozencrath]

Hi All, I am creating a db for my company we are a military contractor. They procure parts under what is called an NSN or National Stock Number for example they might need a sewing machine and the NSN which is unique to that machine might have 2 or 3 different part numbers from different manufacturers. Additionally, my contracts may contain multiple NSN numbers..I am trying to do a simple many to many relationship from the NSN table ("products") to the contracts table (orders). The problem is that my tables are as such:

Table 1 NSNs
ID
NSN (often duplicates because of NSN having multiple PNs)
PN
Description



Table 2 Contracts
ID
Contract No
NSN
Awardee
Amount

Normally, I would create a third table to make the many to many relationship but the issue is that in the Contracts table, I already have all of the order details and it will not allow me to cut and paste it into the third table...

In other words, how do I setup a many to many relationship between an Orders and Products table if the order details are already in the table? I do not want to do this with a query...thanks!

[orange]

I haven't worked with NSNs for many years, but it seems to me that different suppliers could have different part numbers for a "similar part" and yet all such parts could fall under the same NSN.

Here's how you handle many to many

Good luck

[recyan]

Sorry, couldn't help myself.
Few thoughts coming to mind & just wanted to speak out loud. Is below the scenario at your end ?

1) We ORDER EQUIPMENT ( Having UNIQUE NSN ).
2) Each EQUIPMENT has Many PARTS.
3) Each PART is Unique.
4) Each PART may belong to One or Many EQUIPMENTS.
5) We may not necessarily ORDER EQUIPMENTS and ORDER only PARTS for One or Many NSM's.
6) Each PART may have One or Many SUPPLIERS.
7) An ORDER may contain One or Many EQUIPMENTS
8) An ORDER may contain One or Many PARTS
9) An ORDER may contain One or Many EQUIPMENTS AND One or Many PARTS
10) An ORDER will essentially be against One & One SUPPLIER
11) There is a possibility of SUB-ASSEMBLY ( consisting of many PARTS ) being a PART of the NSN.
12) There is a possibility of SUB-ASSEMBLY being ordered in totality.

Thanks

[Mozencrath]

Ill try and elaborate.
1. The government issues contracts for a part.
2. Each part has a unique NSN
3. An NsN may have multiple part numbers, one for each manufacturer who makes the part.
4. An order may have multiple nsns.
5. The contract will be for an NSN not a PN
6.each nsn is unique.
7. Not every NSN has an order.Some have never been ordered.
Here are my table fields

Products:
NSN
PN
Description
Weight

Orders:
Order Number
NSN
Amount
Date
Awardee

So as you can see I have a duplicate field and the orders table already has the order details....im trying to figure a way to relate the products to the orders through a third table called order details.

[orange]

If you are quite good at learning by Watching/Listening
rather than reading, here are some links to tutorials all dealing with Customer, Order, OrderDetails, and Items/Product.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

https://www.youtube.com/watch?v=lXAG...6FE5448948D9B4 E-R Diagramming Price and Quantity