3

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:

enter image description here

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.

  • Are any of the relationships mandatory? Is there always a contract? Can an annex exist without a corresponding contract? And in the case where you have both an annex and a contract and assuming you have an entry in the dynamics table can it relate to either contract or annex or must it relate to both? – Sir Swears-a-lot Nov 15 '14 at 9:14
  • @Peter: Are any of the relationships mandatory? Is there always a contract? Contract must exist. It may have Dynamics of payment. It may have Annex. Annex may have its own Dynamics of payment. Can an annex exist without a corresponding contract? No. And in the case where you have both an annex and a contract and assuming you have an entry in the dynamics table can it relate to either contract or annex or must it relate to both? Annex has its own entry in the Dynamics, Contract has its own. Dynamics for Contract is not the same as Dynamics for Annex. – AlwaysLearningNewStuff Nov 15 '14 at 15:21
4

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 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 only one, of those FK fields is NOT NULL.

Instead, I would create a separate bridge / relationship table for each of Annex and Contract to relate to Dynamic of Payment. Yes, when new entities are added that can relate to Dynamic of Payment you will need to add new bridge / relationship tables, but somehow I feel better about that because it is not altering the structure of Dynamic of Payment: the Dynamic of Payment entity is the same whether 1 or 100 entities relates to it.

(the SQL below is use Microsoft SQL Server T-SQL semantics)

Main Entities

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
);

Dynamics of Payment

The [DynamicsOfPaymentType] table is a lookup table that assumes that the "dynamics" are named properties, possibly mimicking an enum in the app layer. I chose TINYINT (0 - 255) as typically most things have less than 255 distinct properties / attributes. If needing more than 255 then go up to the next higher size. But don't go higher than you need as that field is copied into the relationship tables which can get large, especially with lots of Contracts and Annexes that each have several DynamicOfPayment entries.

Please note that the PK on DynamicsOfPayment is a composite key made up of [DynamicsOfPaymentID] and [DynamicsOfPaymentTypeID]. While technically only the [DynamicsOfPaymentID] field is needed in the PK as it is guaranteed unique, having the [DynamicsOfPaymentTypeID] field allows us to push that property up to the relationship table for use in constraining each entity to having only one instance of any particular DynamicsOfPayment property. Please see notes in next section for more details.

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)
);

Main entity to Dynamics of Payment relationships

Please note that the PK of both relationship tables is a composite key made up of the main entity ID and the [DynamicsOfPaymentTypeID] field--not the [DynamicsOfPaymentID] field. The reason, as mentioned in the prior sections notes, is to enforce a single instance of any particular DynamicsOfPayment property for each main entity. If an entity can have multiple instances of a DynamicsOfPayment property, then it is a simple matter of adjusting the PK on that particular relationship table to be a composite key made up of the main entity ID and the [DynamicsOfPaymentID] field (or if you prefer, both the [DynamicsOfPaymentTypeID] and the [DynamicsOfPaymentID] fields). As you can see, this model can allow for not only the relationships to enforce singular instances of related properties OR for the relationships to accept multiple related properties, but it also allows for mixing the two as the relationship tables don't have to use the same combination of fields for their PK: some relationships can be constrained while others not. So maybe Annex is limited to a single instance of any particular DynamicsOfPayment property, but Contract is allowed to have multiple instances of a particular property.

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)
);

Pro Tips

  1. Yes, there will be slightly more tables, joins, and app code using this model. But, just keep in mind that refactoring app code is much easier than refactoring tables, especially once the app is live and data is in there. Take the small hit of slightly more time now to code for this and save yourself many, many hours over the years of trying to make changes to simpler models, or as projects come up in a few years, just deciding to put in hacks / band-aids because the business won't go for the 10 - 20 (or more) hours it will take to make changes to the simplified model that saved you 1 - 2 hours now. Trust me, database programmers / architects are not cheap (at least not these days) and my employers have spent an egregious amount of time and money--in the form of my salary (and the salaries of my fellow DB coworkers)--for me to refactor poor design choices that saved maybe even 1 day of initial dev time, but over the next 3 years cost them a week (a week that I was not adding new features)

  2. You can coordinate the cleanup of the relationship tables by marking the FK as ON DELETE CASCADE. The syntax might differ slightly between RDBMS's, but I would expect that most, if not all, of them support cascaded deletes. This way, if you delete a Dynamic of Payment entry, you don't need to worry about its entry in either of the relationship tables.

  3. Depending on what RDBMS you use, you might even be able to coordinate the creation of the relationship records. Microsoft SQL Server has a very cool OUTPUT clause that returns the changes made by a DML operation. Hence, you can insert a record into [DynamicsOfPayment], and within the context of that operation (and hence, transaction), you can do the insert into the relationship table. Assume you have an insert proc that accepts an extra parameter for @ContractID that is not initially needed for the [DynamicOfPayment] table. It can be used as follows:

    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);
    

    Now you just need two stored procedures (since each one need to reference a different relationship table):

    • DynamicsOfPayment_CreateForContract (@DynamicsOfPaymentTypeID, @Value, @ContractID)
    • DynamicsOfPayment_CreateForAnnex (@DynamicsOfPaymentTypeID, @Value, @AnnexID)
|improve this answer|||||
  • 1
    I don't know how you always do it, but again you have managed to understand my problem. Your pros and cons are the only reason why I was seriously rechecking solution provided by member Mladen Uzelac. Looking forward to see your recommendation. – AlwaysLearningNewStuff Nov 15 '14 at 20:46
  • @AlwaysLearningNewStuff Done (hopefully ;-). Regarding your observation, I think it helps that I not only have a passion for this theory stuff, but also a philosophy background (which helps with modeling as it's a more abstract exercise), and the (un)fortunate experiences of trying these various approaches and seeing what worked and what didn't (and proved me wrong) on designs that I thought were good/great at the start but ended up causing me pain when I had to also adapt them to changing business needs over the years. Be cautious of anyone who hasn't had to maintain their own designs :-D. – Solomon Rutzky Nov 15 '14 at 21:56
  • One question : In tables AnnexXDynamicsOfPayment and ContractXDynamicsOfPayment, why is the DynamicsOfPaymentTypeID part of the composite primary key? Shouldn't it be DynamicsOfPaymentID? Sorry for asking, I am just not that experienced, but those fields seem odd to me to be part of the primary key... – AlwaysLearningNewStuff Nov 15 '14 at 22:18
  • 1
    Sorry, I missed that. It is late at night here, so I will give this a fresh look tomorrow, or day after tomorrow. If any confusion still exists after that I will leave a comment. Thank you, we shall hear soon. Best regards. – AlwaysLearningNewStuff Nov 15 '14 at 22:48
  • 1
    I agree, we can move to chat ( I will delete this comment and all subsequent others ). – AlwaysLearningNewStuff Nov 17 '14 at 21:49
2

It seems that it's better that contract have self join for contract and annex (hierarchical structure).
Naming of contract and annex may be different, and id and self_id may differ.

create table contract(
   id serial primary key,
   self_id int,
   document_id text,
   constraint contract_self_fk foreign key(self_id) references contract(id)
);

create table dynamics_of_payment(
    id serial primary key,
    contract_id int,
    constraint dynamics_of_payment_contract_fk foreing key(contract_id) references contract(id)
 );

Since PostgreSQL supports Writeable Common Table Expression (CTE) you can update, insert, insert and delete values.

I will point you to the guides that will you explain you more:
Great video explaining recursive structure
Common table expressions in PostgreSQL - Official doc
Recursive queries with postgres
Recursive 3
Tree structure with PostgreSQL

Here is an Oracle tutorial about that problem:
Oracle and CTE

I will demonstrate how can you preserve ordering easily of contract and annexes.
When you store values in Contract table follow this pattern:

(id, self_id, document_id)
  1,   1,       contract xxx
  2,   1,       annexx xxx_01
  3,   2,       annexx xxx_02
  4,   3,       annexx xxx_03

So when you have recursive query you can easily get ordered official documents.

And here is a free short course about recursive structure with SQL: https://class.stanford.edu/courses/DB/Recursion/SelfPaced/about

|improve this answer|||||
  • Your suggestion is interesting, but I do not fully understand the following part: Naming of contract and annex may be different, and id and self_id may differ. – AlwaysLearningNewStuff Nov 15 '14 at 15:55
  • Name it as you like it. :) It's boss and employee hierarchy. Where the boss is the contract and annexes are the employees. – Mladen Uzelac Nov 15 '14 at 16:18
  • I am not fully understanding how to do INSERT, UPDATE or DELETE because your concept is still new to me. How would I differentiate Annex from a Contract in your table contract, for example? I am sorry for disturbing, but I like the maintenance potential of your solution, yet I have some confusions as described above... – AlwaysLearningNewStuff Nov 15 '14 at 17:23
  • I expanded the answer, it should be clear now. You distinguishe depending on his parent relation with self_id, if it is the same or null that it is the contract, if it has other value than it is an annex. When you see examples I believe it would be much clearer how it is implemented. ;) – Mladen Uzelac Nov 15 '14 at 17:44
  • I have upvoted your answer as a small token of gratitude. Since my superiors are prone to change their minds and introduce new things into the mix, I officially accepted more flexible solution offered by member srutzky. Thank you again for your time and help. Best regards until next time. – AlwaysLearningNewStuff Nov 17 '14 at 19:00
0

I would create 3 tables:

Contract: Contract id (pkey) Attrib 1 Attrib 2 etc

Annex: Annex id (pkey) Contract id (fkey) Attrib 1 Attrib 2 etc

Dynamics: Payment id (pkey) Contract id (fkey) Annex id (fkey) Attrib 1 Attrib 2 etc.

As I understand your description this would allow entries in the dynamics table to relate to a contract or an annex or both.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.