Link to home
Start Free TrialLog in
Avatar of Joe Grosskopf
Joe Grosskopf

asked on

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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.
Avatar of Joe Grosskopf
Joe Grosskopf

ASKER

I renamed the Order number field in the Invoices table.

PK Orders [Order Number] - FK Invoices [Purchase Order]
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
I didn't give any solution. Only made questions. I shouldn't receive points for that.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Probably, I'll do what they wish then after a while, I'll get the request to combine them I guess. Thanks again
I split the points up. While a "solution" was not found, the help and info was helpful
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.
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
...in teh diagram above, there's also important relation from PURC_ORDER_LINE to RECEIVER_LINE by purc_order_id  and line number.
Very cool! I'll print this and compare and try to imitate this in our system. Thanks again!