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