?
Solved

Many-Many Relationship in SSDT

Posted on 2014-08-27
15
Medium Priority
?
266 Views
Last Modified: 2014-08-28
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
0
Comment
Question by:jsgrosskopf
  • 7
  • 5
  • 3
15 Comments
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40289996
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
 

Author Comment

by:jsgrosskopf
ID: 40290085
I renamed the Order number field in the Invoices table.

PK Orders [Order Number] - FK Invoices [Purchase Order]
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 498 total points
ID: 40290091
Then should be ok.
Can you post here the SQL query?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jsgrosskopf
ID: 40290100
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
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 1002 total points
ID: 40290262
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
 

Author Comment

by:jsgrosskopf
ID: 40290359
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
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40290370
I didn't give any solution. Only made questions. I shouldn't receive points for that.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 1002 total points
ID: 40290387
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
 

Author Comment

by:jsgrosskopf
ID: 40290427
Probably, I'll do what they wish then after a while, I'll get the request to combine them I guess. Thanks again
0
 

Author Closing Comment

by:jsgrosskopf
ID: 40290441
I split the points up. While a "solution" was not found, the help and info was helpful
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40290704
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
 

Author Comment

by:jsgrosskopf
ID: 40290720
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40290824
Here's how it is in our ERP database
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40290850
...in teh diagram above, there's also important relation from PURC_ORDER_LINE to RECEIVER_LINE by purc_order_id  and line number.
0
 

Author Comment

by:jsgrosskopf
ID: 40290871
Very cool! I'll print this and compare and try to imitate this in our system. Thanks again!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question