Many-Many Relationship in SSDT

I am attempting a tabular analysis using sql server data tools. I have run into an issue regarding relationships. I have one table titled orders with all my purchase orders in this table (PK = order number). I have another table with Invoices which has all my invoices. (PK invoice number). This has a one to many relationship with one order to many invoices. (One order can have many invoices, but an invoice can only refer to 1 order). This works. However I wanted to link the order details (item number, qty, amount etc.) I added a third table with order details and linked the order table. (One order can have many details). This one will work as well. The issue is if I try to select an order and select the invoices and details that go with that one order. The SSDT tells me it cannot determine the relationship. If yo look at the diagram, it looks like it is a man-many relationship between the Invoices and order details. But I have no idea how to rectify, I can do each query independantly but cannot combine. I attached a diagragm to hopefully show  alittle more detail. Thanks
diag.PNG
jsgrosskopfIS ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
After drop a look in your diagram, I also couldn't determine the relationship between Orders and Invoices. How do you relate these two tables? Invoices table should have a column named OrderNumber (foreign key) for can relate with Orders table.
0
jsgrosskopfIS ManagerAuthor Commented:
I renamed the Order number field in the Invoices table.

PK Orders [Order Number] - FK Invoices [Purchase Order]
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then should be ok.
Can you post here the SQL query?
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

jsgrosskopfIS ManagerAuthor Commented:
I have to find how to do that in SQL Server Data Tools. It's several queries making tables and the diagram. I do not know how to get the query to show the entire dataset (relationships as well). Let me see if I can find it. I believe in Transact SQL or SQL DB it all works, it just using a tabular model with SSDT where it does not
0
Vadim RappCommented:
Assuming that you can have several order lines per one order, and these lines are reflected in "order details",

1. you don't want to have "order date" in order details table, unless different order line items may have different dates.

2. Currently, since you can have several lines in each order and several invoices per order, there's no way to tell which lines are included in which invoice. If you want to relate order lines (which you call "order details") to invoices, there are two ways:
2.1 create another table "invoice details" and link order details to invoice details. This will tell which order lines are included in which invoice.
or
2.2. Create new field "invoice number" in "order details", and link it to the invoices.
3. generally speaking, the invoices should be related not directly to the orders, but to shipments (if these are customer orders, i.e. you sell to them), or to receivers (for purchase orders, i.e. you buy from them). That way you link the invoice to what was actually shipped or received, rather than just ordered. Even the model I described above, with invoice details, is not sufficient because if order line was shipped in several installments, you will have more than one invoice for this line, and without the table that shows what was actually received, it will be hard to tell what you were billed for in each invoice.
0
jsgrosskopfIS ManagerAuthor Commented:
After discussing with my manager, we decided to have two separate tabular models, one for AP (all the financials...check numbers, invoices, ledger account etc.) and one for Purchasing (POs, details, Costing, delivery info, historical cost etc.). Thanks for all your help guys. I'll be happy to award the points 1/2 and 1/2 to both of you...do you know how to do this?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I didn't give any solution. Only made questions. I shouldn't receive points for that.
0
Vadim RappCommented:
Check http://support.experts-exchange.com/customer/portal/articles/608621-how-do-i-accept-a-comment-as-my-solution-, but in order to split points you select "accept multiple solutions". However, take into account Vitor Montalvão' comment above.

"we decided to have two separate tabular models, one for AP (all the financials...check numbers, invoices, ledger account etc.) and one for Purchasing (POs, details, Costing, delivery info, historical cost etc.)."

I'm afraid you will find very soon that these "separate" models in fact have too much in common to stay separate. For example, you are expected to pay invoices (model 1) for your PO's (model 2)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jsgrosskopfIS ManagerAuthor Commented:
Probably, I'll do what they wish then after a while, I'll get the request to combine them I guess. Thanks again
0
jsgrosskopfIS ManagerAuthor Commented:
I split the points up. While a "solution" was not found, the help and info was helpful
0
Vadim RappCommented:
Actually, the solution should have been found in the comment 40290262; at least, you never said why it wouldn't work for you.

> I'll do what they wish then after a while, I'll get the request to combine them I guess

No, you will never get request to combine them, and it will be too late anyways. What you will be getting ever after, will be the requests to synchronize them, and then synchronize every their new feature as they evolve.
0
jsgrosskopfIS ManagerAuthor Commented:
I did do that suggestion and initially it appeared to work but the SSDT would crash each time I attempted to get a record out of each table. SSDT is nice but it is not as intelligent at Transact SQL. Best I can diagram is like this.

Invoices --< Invoice / Order  >-- Orders.

The only way SSDT works properly is if I have it structured like this

Invoices --< Invoice / Orders --< Orders

See how the one-many relationships seems to be in the same direction? And above will not work. If it is not, it gives all kinds of errors. I know it seems silly but I can't figure out how to make it work any other way

Thanks Again
0
Vadim RappCommented:
Here's how it is in our ERP database
0
Vadim RappCommented:
...in teh diagram above, there's also important relation from PURC_ORDER_LINE to RECEIVER_LINE by purc_order_id  and line number.
0
jsgrosskopfIS ManagerAuthor Commented:
Very cool! I'll print this and compare and try to imitate this in our system. Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.