CODEWITHSUNDEEP
sqlumlerd
Nordseebaer
Nordseebaer

Reputation: 138

SQL: Right way to model multible relations in UML/ ERD

How do I do the UML for the this example:

Order(ID, user_id, invoice_user_id) where user_id and invoice_user_id are foreign keys and user_id != null

Both users stored in the same entity. How are the cardinalities or the UML priciple? I guessed it that way:

[Order]0..n--1..2[User]

so an order can have 2 users. Another option (I hope you understand my textpicture...) This should mean that an order can have up to 2 Users. Or is this way right:

[Order]n -- 1[User] |n |1 [(Invoice)User]

One user can have many orders and/or can be the invoice user of many orders...

Upvotes: 0

Views: 64

Answers (1)

Jim L.
Jim L.

Reputation: 6529

You should be naming your unnamed association-end properties and using two separate associations. The way you have it, there is no way to get to the Invoice User separately from the other User. And what are these users anyway? You probably need to do more analysis to understand the roles involved. Here's my guess:

  • Seller creates any number of Invoices
  • Buyer receives any number of Invoices
  • Invoice received by one Buyer
  • Invoice created by one Seller

That gives you a model like this:

 ------
|Seller|
 ------
 1 | creatingSeller
   |
 * | createdInvoice
 -------  *                              1  -----
|Invoice|----------------------------------|Buyer|
 -------  receivedInvoice   receivingBuyer  -----

Now you can navigate to the two "users".

BTW, both Buyer and Seller are likely roles played by an agent, like a Person or an Organization. I'm not going to model the whole thing for you.

Just so you are aware, there are other ways to realize this model in an RDBMS, using "reference data" / "enumeration literals" to differentiate the two associations and the two roles of agent. Your original question indicates you want a more physical model, but I think you're asking for trouble by skipping the analysis model, which can be realized as it is in an RDBMS.

Upvotes: 1

Related Questions