Union Type, TPT, DDD, ORM and RDBMS

Combinations and pattern-matching are widely used in functional programming to increase the reliability and expressiveness of programs.

A classic example of successful use of associations for business process modeling is the shopping cart and order status. The user has the right to add and remove goods until he paid the order. But the operation itself modification paid order is meaningless. The Remove operation for an empty basket is also meaningless. Then, instead of the common class Cart it ICartState to define the ICartState interface and declare one implementation for each state. In more detail, this approach is described in the text here and in the video format here .

Recently, we had the task to design the database structure for specialized CRM / ERP. The first approach to the modeling of contracts was not successful, due to the fact that both physical and legal entities from Russia and other countries of the world can act as parties to contracts. The TIN is necessary for the seller to receive payment, but it is not always necessary for the merchant (passport data are more often used for personal identification). Formats details of domestic and foreign legal entities do not match. It did not help the case that the individual entrepreneurs are individuals, but they “pretend” to be legal.
')
In retrospect, we dismantled the mistakes of the initial design and outlined the direction of refactoring. All interested in our history, please under the cat.

The initial outline of the domain model looked like this:

  public enum BusinessEntityType { [Display(Name = ". : , LTD, ...")] LegalEntity = 0, [Display(Name = "")] IndividualEntrepreneur, [Display(Name = ". ")] Person, [Display(Name = "   ")] NonProfitEducationalInstitution } [DisplayName("")] public class Contractor : NamedEntityBase { [Required] [Display(Name = "  /   .")] public Address MainAddress { get; set; } [Display(Name = "  ")] public Address ActualAddress { get; set; } [Display(Name = " ")] public BusinessEntityType Type { get; set; } [Display(Name = ""), StringLength(127), RegularExpression("\\d+")] public string Inn { get; set; } [Display(Name = " ")] public virtual string Email { get; set; } [Display(Name = "")] public virtual string PhoneNumber { get; set; } public Contractor([NotNull] string name, BusinessEntityType type, [NotNull] Address mainAddress, Address actualAddress = null) : base(name) { if (mainAddress == null) throw new ArgumentNullException(nameof(mainAddress)); Type = type; MainAddress = mainAddress; ActualAddress = actualAddress; } protected Contractor() { } } 

The “counterparty” clearly tried to please both the “physicists” and the “lawyers”, which did not benefit the SRP. It became absolutely not clear where to go OGRNY, BIKI and other not interesting physics attributes and why the TIN is a mandatory field for physical. Buyers?

The second approach to the projectile

 [DisplayName("")] [Table(nameof(Contractor), Schema = nameof(Office))] public class Contractor : NamedEntityBase { /// <summary> ///   /   . /// </summary> [Required] [Display(Name = "  /   .")] public Address MainAddress { get; set; } /// <summary> ///    /// </summary> [Display(Name = "  ")] public Address ActualAddress { get; set; } [Display(Name = " ")] public ContractorType Type { get; set; } [Display(Name = ""), StringLength(127), RegularExpression("\\d+")] public string Inn { get; set; } [Display(Name = " ")] public string Email { get; set; } [Display(Name = "")] public string Phone { get; set; } public string FullName => string.Join(" ", Name.Split(',')); public virtual LegalContact LegalContact { get; set; } public virtual PersonContact PersonContact { get; set; } public Contractor([NotNull] string name, ContractorType type, [NotNull]Address mainAddress, Address actualAddress = null) : base(name) { if (mainAddress == null) throw new ArgumentNullException(nameof(mainAddress)); Type = type; MainAddress = mainAddress; ActualAddress = actualAddress; } protected Contractor() { } } public class PersonContact : EntityBase, IContact { [Display(Name = ""), StringLength(127), RegularExpression("\\d+")] public string Inn { get; set; } public string Email { get; set; } public string Phone { get; set; } public string Name { get; set; } public string Surname { get; set; } public string Patronymic { get; set; } public Address Address { get; set; } } public class LegalContact : EntityBase, IContact { [Display(Name = ""), StringLength(127), RegularExpression("\\d+")] public string Inn { get; set; } public string Kpp { get; set; } public string Ogrn { get; set; } public string Okpo { get; set; } public string Okved { get; set; } public string Name { get; set; } public string FullName { get; set; } public string Email { get; set; } public string Phone { get; set; } public Address MainAddress { get; set; } public Address ActualAddress { get; set; } } public interface IContact : IHasId<int> { string Email { get; set; } } 

"Contacts" here would be more correctly called "details". The need for an IContact interface raises questions. “Not so” here is that integrity control for LegalContact and PersonContact is possible only by using Check Constraint.

How would the union from LegalContact and PersonContact ! Unfortunately, relational database management systems do not provide such functionality. In addition, the Type field now looks redundant. Cases of IP and individual face are combined in PersonContact , although from the point of view of business processes, IP caps are closer to legal entities.

The third approach to the projectile

Let's declare an abstract Contractor class, heirs for each type of counterparty, and consider what unites them. The main use of the counterparty is the signing of contracts. In this case, we do not work with EDS, therefore, the full name and details of the parties are sufficient to substitute them into an automatically created document that will be printed and signed.

  [DisplayName("")] [Table(nameof(Contractor), Schema = nameof(Office))] public class Contractor : EntityBase { [Display(Name = " ")] public ContractorType Type { get; set; } [Required] public string Email { get; set; } public string Phone { get; set; } public Contractor(ContractorType type) { Type = type; } protected Contractor() { } } [Table(nameof(CompanyContractor), Schema = nameof(Office))] public class CompanyContractor : Contractor { [Display(Name = "/VAT"), StringLength(12)] public string Vat { get; set; } [Display(Name = ""), StringLength(9)] public string Kpp { get; set; } [Display(Name = ""), StringLength(13)] public string Ogrn { get; set; } [Display(Name = ""), StringLength(10)] public string Okpo { get; set; } [Display(Name = ""), StringLength(10)] public string Okved { get; set; } public string Name { get; set; } public string FullName { get; set; } public Address MainAddress { get; set; } public Address ActualAddress { get; set; } public CompanyContractor(string name) { Name = name; } protected CompanyContractor() { } } [Table(nameof(PersonContractor), Schema = nameof(Office))] public class PersonContractor : Contractor { [Display(Name = ""), StringLength(12)] public string Vat { get; set; } [Display(Name = " "), StringLength(15)] public string Ogrnip { get; set; } public string Name { get; set; } public string Surname { get; set; } public string Patronymic { get; set; } public Address Address { get; set; } public string FullName => string.Join(" ", Name, Surname, Patronymic); public PersonContractor(string name, string email) { Name = name; Email = email; } protected PersonContractor() { } } 

You could add getter to the Type property, in case you need multiple dispatch . But following YAGNI we will leave everything as it is.

As ORM, we use the Entity Framework. This structure can be mapped to a relational database using the table per type (TPT) approach.

All that remains is to transfer the “common” fields to ContractorBase . Now the base class looks logical. The system is available details and representative of the counterparty. In case of questions you can try to contact by phone or e-mail.

Who is IP and what it eat

IP - is a form of economic activity without a legal entity. We will not go into the subtleties of the civil code. Let us dwell on the main thing: the PI has the OGRNP (analogue of the OGRN ) and the TIN. In this case, the TIN of the physical person of Vasi Ivanov and IP Vasi Ivanov is the same number.

If we are interested in a person in the context of labor or other contractual relations, then we can define another entity “nat. face "and connect it with counterparties Counterparty - physical. face and sp. Then it will be impossible to make a mistake when establishing an IP with an existing individual. A more expanded reference book of individuals may be required in another context (for example, to pay wages, SNILS will be required). Then we can repeat the “trick” with TPT and add another table for each “cut” in which we are interested in the data.

The question of how to determine that Ivan Ivanov Ivan from Kazan and Ivan Ivan Ivan from Penza - I will not consider different people, because this topic is worthy of a separate article.

UI organization

Creating different counterparties will require filling in different fields, so we will need a form for each heir of the counterparty. Is logical. If in the UI it is permissible to have instead of this list of counterparties on the list for each type, then we can finish. If it is more convenient to work with a single list of counterparties, you will have to change something again. IContractor interface and IContractor abstract methods there. Rename ContractorBase to ContractorInfo and make this class non-abstract to be able to display the list of suppliers. We return ContractorType to distinguish the types of counterparties. EntityFramework materializes objects through Refletion, so we calmly leave the constructor and the Type and Name properties protected. Information about counterparties does not appear by itself, but is added only together with the counterparty itself. We had to denormalize the table, which is “not very” in terms of consistency and the need for additional gestures when changing the fields that are part of the “name”, but not bad for performance.

Conclusion

Despite the fact that the union is not supported out of the box by either relational DBMS or C #, using TPT, you can design a data structure of any complexity and branching, ensuring that all required fields are filled. It is necessary only to select all possible cases and create one for each heir. In the most extreme case (when there is nothing in common between the heirs), the base class will contain only Id. Such a table looks strange, however, it is much easier to set the Foreign Key for related tables than in any other way. In addition, the approach can be applied recursively and formally, which increases reliability when designing an extensive domain model.