snippetMinor
Can't convert this type of ER diagram into SQL or relational tables
Viewed 0 times
thiscantablesconvertintosqltyperelationaldiagram
Problem
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
Dynamics of payment is a complex attribute of
As for Annexes, it is a complex attribute of
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.
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.
Solution
I will agree that @MladenUzelac's approach is probably the more common pattern for this situation, but there is a potential theoretical flaw that can expose itself over time and make life difficult. That flaw is: a Contract, at the end of the day, is not an Annex. Of course, I emphasized "potential" because currently I am not privy to whether or not they are currently considered the same thing (i.e. Contract === Annex). However, to a degree that would not change my recommendation to find out that they are the same. Anything is subject to change at any point in time, even if highly unlikely, so having the same nature today does not imply that they will always have the same nature, and often enough things do grow in different directions. So yes, they share the same attributes today, and they share the same relationship with Dynamic of Payment today; but tomorrow is another day and the nature of things changes, even for irrational reasons ;-).
Don't get me wrong: there definitely are times when a self-referencing entity is the appropriate solution, but I think that requires that the entities be of the same nature. The two most common examples I can think of are:
-
Employee / Manager: It makes sense to have a [ManagerID] (or I prefer [ManagerUserID]) field in the [Employee] table that is a self-referencing FK back to [UserID]. This works because a manager is an employee.
-
Departments: In corporate / retail hierarchies, it makes sense to have a [ParentDepartmentID] field in the [Department] table that is a self-referencing FK back to [DepartmentID]. This works because the "parent" Department is a Department.
In both of these cases, the relationship between parent and child is not a primary determining property of those entities. Instead, the relationship is a property just like the name of the entity. In both cases, the relationship is not critical to the existence of the child entities, and the entities defined as "children" exist even without the relationship. On the other hand, if an Annex requires a Contract in order to exist, well, that is something quite different.
What it comes down to is: just because two things look the same does not mean that they are the same.
Storing two "similar yet different" entities together, while reducing some amount of tables / joins / code, leads to some pragmatic concerns, even if only in terms of physical storage (i.e. the DB layer).
I have worked for years on a system, built before I got there, in which this pattern was used several times and for situations that were nearly identical to what is being described here. I am not sure if the entities had the exact same attributes on day 1, but over time at least, they began to diverge and take on different properties from the other. In some cases fields were added that were appropriate to one of the entities but not the other, so we just had to know when a NULL field was appropriate for that type of row, or if it was a bug. Other times a "multivalued attribute" approach was taken with the structure being effectively: [EntityID], [AttributeID], [AttributeValue].
As expected, in order to get a list of "parent" entities we would query with
So, I would start out heading in the same direction as @Peter in terms of separate tables for Contract and Annex (even if they are identical, or nearly identical, structures), but I would approach the handling of their relationship to Dynamic of Payment differently. I seem to recall having tried the two different FK fields thing in the past and not really liking how it played out. At the very least it seems less manageable over time as other entities are added that can relate to Dynamic of Payment a you need to keep adding FKs back to new parents. And you also need to have a CHECK CONSTRAINT on the table ensuring that one, and
Don't get me wrong: there definitely are times when a self-referencing entity is the appropriate solution, but I think that requires that the entities be of the same nature. The two most common examples I can think of are:
-
Employee / Manager: It makes sense to have a [ManagerID] (or I prefer [ManagerUserID]) field in the [Employee] table that is a self-referencing FK back to [UserID]. This works because a manager is an employee.
-
Departments: In corporate / retail hierarchies, it makes sense to have a [ParentDepartmentID] field in the [Department] table that is a self-referencing FK back to [DepartmentID]. This works because the "parent" Department is a Department.
In both of these cases, the relationship between parent and child is not a primary determining property of those entities. Instead, the relationship is a property just like the name of the entity. In both cases, the relationship is not critical to the existence of the child entities, and the entities defined as "children" exist even without the relationship. On the other hand, if an Annex requires a Contract in order to exist, well, that is something quite different.
What it comes down to is: just because two things look the same does not mean that they are the same.
Storing two "similar yet different" entities together, while reducing some amount of tables / joins / code, leads to some pragmatic concerns, even if only in terms of physical storage (i.e. the DB layer).
I have worked for years on a system, built before I got there, in which this pattern was used several times and for situations that were nearly identical to what is being described here. I am not sure if the entities had the exact same attributes on day 1, but over time at least, they began to diverge and take on different properties from the other. In some cases fields were added that were appropriate to one of the entities but not the other, so we just had to know when a NULL field was appropriate for that type of row, or if it was a bug. Other times a "multivalued attribute" approach was taken with the structure being effectively: [EntityID], [AttributeID], [AttributeValue].
As expected, in order to get a list of "parent" entities we would query with
WHERE ParentEntityID IS NULL. And to pull a list of "child" entities we would query with WHERE ParentEntityID IS NOT NULL. It got fun when we needed both entities because they were really a property of another primary entity, so we needed to show both as separate properties of the primary entity. What was fun was how that self-referencing join hurt performance (though it is possible that a slight change in indexing strategy could have fixed that). But it got super-duper fun when we placed that EntityID in other entities as FKs, and we only wanted to reference one of those two entity types, but it was not always guaranteed that we wanted the "child" entity (though that was the most common entity to refer to). In the end, as I started creating new tables and refactoring old ones, I included the entity name in the name of the FK field in the related entity, just to have it "self documented" for anyone looking through the tables needing to create new queries. And in some cases I added two FK fields, one for each entity type, just to avoid that costly self-join (and it was safe to denormalize since that parent-child relationship could never change once created).So, I would start out heading in the same direction as @Peter in terms of separate tables for Contract and Annex (even if they are identical, or nearly identical, structures), but I would approach the handling of their relationship to Dynamic of Payment differently. I seem to recall having tried the two different FK fields thing in the past and not really liking how it played out. At the very least it seems less manageable over time as other entities are added that can relate to Dynamic of Payment a you need to keep adding FKs back to new parents. And you also need to have a CHECK CONSTRAINT on the table ensuring that one, and
Code Snippets
CREATE TABLE SchemaName.[Contract]
(
ContractID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
ContractName VARCHAR(50) NOT NULL,
ContractDetails NVARCHAR(MAX) NULL
);
CREATE TABLE SchemaName.Annex
(
AnnexID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
ContractID INT NOT NULL REFERENCES SchemaName.[Contract] (ContractID),
AnnexName VARCHAR(50) NOT NULL,
AnnexDetails NVARCHAR(MAX) NULL
);CREATE TABLE SchemaName.DynamicsOfPaymentType
(
DynamicsOfPaymentTypeID TINYINT NOT NULL PRIMARY KEY, -- SMALLINT if > 255 are possible
DynamicsOfPaymentType VARCHAR(50) NOT NULL
);
CREATE TABLE SchemaName.DynamicsOfPayment
(
DynamicsOfPaymentID INT IDENTITY(1, 1) NOT NULL,
DynamicsOfPaymentTypeID TINYINT NOT NULL REFERENCES
SchemaName.[DynamicsOfPaymentType] (DynamicsOfPaymentTypeID),
Value NVARCHAR(MAX) NULL,
PRIMARY KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);CREATE TABLE SchemaName.[ContractXDynamicsOfPayment]
(
ContractID INT NOT NULL,
DynamicsOfPaymentID INT NOT NULL,
DynamicsOfPaymentTypeID TINYINT NOT NULL,
PRIMARY KEY (ContractID, DynamicsOfPaymentTypeID),
FOREIGN KEY (ContractID) REFERENCES SchemaName.[Contract] (ContractID),
FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);
CREATE TABLE SchemaName.[AnnexXDynamicsOfPayment]
(
AnnexID INT NOT NULL,
DynamicsOfPaymentID INT NOT NULL,
DynamicsOfPaymentTypeID TINYINT NOT NULL,
PRIMARY KEY (AnnexID, DynamicsOfPaymentTypeID),
FOREIGN KEY (AnnexID) REFERENCES SchemaName.[Annex] (AnnexID),
FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);INSERT INTO SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentTypeID, Value)
OUTPUT @ContractID,
INSERTED.DynamicsOfPaymentID, -- server-generated auto-increment field
INSERTED.DynamicsOfPaymentTypeID
INTO SchemaName.[ContractXDynamicsOfPayment]
(ContractID, DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
VALUES (@DynamicsOfPaymentTypeID, @Value);Context
StackExchange Database Administrators Q#82710, answer score: 4
Revisions (0)
No revisions yet.