Can't convert this type of ER diagram into SQL or relational tables

Question

0.00/5 (No votes)

See more:

I have stumbled upon a problem during database design. Let me explain what it is all about:

Contract has some simple attributes that describe him, and may have annexes and dynamics of payment.

Therefore I have decided to make main table Contracts, and placed simple attributes as columns.

Dynamics of payment is a complex attribute of Contracts table. It can have none, one or multiple values. Searching through the Internet I have learned that this is called multivalued attribute, and have found this example[^] that seems to illustrate my case very well ( Dynamics of payment is equivalent to the Hobbies table in the linked example ).

As for Annexes, it is a complex attribute of Contract. Contract can have many of them, one or none.

Annex has exactly the same simple attributes as contract, and can also have Dynamics of payment. Annex' relationship with Dynamics of payment is the same as the relationship between Contract and Dynamics of payment.

To sum-it-up, Annex and contract have everything the same, the only difference is that Annex is a complex attribute of Contract.

Using this[^] as a reference, I have made the sketch of my ER diagram[^].

This is the first time I see this type of relationships so I ask the community to help me convert this ER diagram into SQL or relational tables.

I apologize for not providing more info, if you have further requests please leave a comment and I will update my post immediately.

Thank you for your understanding and help.

Best regards.

Posted 14-Nov-14 13:50pm

AlwaysLearningNewStuff

Add a Solution

Comments

PIEBALDconsult 14-Nov-14 20:18pm

I didn't look at the diagrams -- I wouldn't understand them.
What I do wonder though, is mightn't it help to extract what you currently have in the Contract into an Annex and then say that a Contract has one-or-more annexes (annices?), then Contract is essentially empty, just a logical container, probably defines just a common ID for a group of other things.
Compare to a List<t> -- it's not a T itself, it serves no purpose other than to be a container.
I think that concept may help reduce your maintenance, by having the definition in one place rather than two.

AlwaysLearningNewStuff 14-Nov-14 22:35pm

I have trouble figuring out how to implement your suggestion. Still, I think I will "go that way" because it seems so efficient and easy to maintain...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Answer 1 · 2014-11-14T16:53:00

Solution 1

Now I have looked at the diagram. I whipped up an ASCII art version of it, and the alternative I propose, it means removing the direct relationship between Contract and Dynamic.

         |-----------------o<
CONTRACT                      DYNAMIC
         |----o< ANNEX |---o<



CONTRACT |-----< ANNEX |---o< DYNAMIC

If Contract has attributes ABCDEF, and Annex has DEF, then simply remove DEF from Contract and replace it with an Annex.

Posted 14-Nov-14 16:53pm

PIEBALDconsult

Comments

AlwaysLearningNewStuff 15-Nov-14 0:45am

I think we misunderstood each other. Let us say Contract has ABCDEF, then Annex has the same attributes. If Dynamics had xyz, then this would be an example from "real life" :

Contract :

A = BlaBla
B = 123,34
C = BC-123
...

Dynamics ( for this Contract ) :

x = 444.54
y = Some Investor Name
z = 444

Annex ( for this Contract ) :

A = YapYap
B = 668, 99
C = ANX-61
...

Dynamics ( for this Annex ) :

x = 123. 55
y = Some Other Name for Investor
z = 990

Just because table schema for Contract and Annex is the same, doesn't mean the values of those fields will be the same.

If I understood your example right, I would lose DEF values for Contract if I do remove those fields from that table.

If I misunderstood you, please correct me.

Thank you again. Best regards.

PIEBALDconsult 15-Nov-14 1:50am

"Just because table schema for Contract and Annex is the same, doesn't mean the values of those fields will be the same."

No, of course not, but you allow multiple Annex records per contract, so create one to stand in for those values that _would_be_ in the Contract