Union Type, TPT, DDD, ORM and RDBMS

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

A classic example of the successful use of associations for modeling business processes is the basket and order status. The user has the right to add and remove goods until he has paid for the order. But the operation of modifying a paid order is meaningless. The Remove operation for an empty basket is also meaningless. Then it’s logical to Cartdefine an interface instead of a general class ICartStateand declare one implementation for each state. This approach is described in more detail in the text here and in the video format here .

Recently, we had a task to design a database structure for specialized CRM / ERP. The first approach to modeling contracts was not successful, due to the fact that both individuals and legal entities from Russia and other countries of the world can act as parties to the contracts. The TIN is necessary for the seller to get paid, but the buyer is not always needed (for identification, passport data is often used). Formats of details of domestic and foreign legal entities do not match. The fact that individuals are individuals, but “pretend” to be legal, did not help the case.

In retrospect, we examined the mistakes of the initial design and outlined the direction of refactoring. I ask everyone interested in our history under cat.

The initial sketch 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 was clearly trying to please both the physicists and the lawyers, which did not benefit SRP. It was absolutely not clear where to put the BINs, BICs, and other attributes not interesting to physics and why the TIN is a required field for the physical. Buyers?

The second approach to the projectile

[DisplayName("Контрагент")]
[Table(nameof(Contractor), Schema = nameof(Office))]
public class Contractor : NamedEntityBase
{
    /// 

    /// Юридический адрес / Адрес для физ.лица
    /// 
    [Required]
    [Display(Name = "Юридический адрес / Адрес для физ.лица")]
    public Address MainAddress { get; set; }
    /// 

    /// Фактический адрес адрес
    /// 
    [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
{
    string Email { get; set; }
}

“Contacts” here would be more correctly called “details”. The need for an interface IContactraises questions. “Not very” here is that integrity control is beyond LegalContactand PersonContactis possible only by using Check Constraint.

How would union from LegalContactand help here PersonContact! Unfortunately, relational DBMSs do not provide such functionality. In addition, the field Typenow looks redundant. Cases of individual entrepreneurs and individuals are united in PersonContact, although from the point of view of business processes, individual entrepreneurs are closer to legal entities.

The third approach to the projectile

We declare an abstract class Contractor, heirs to each type of counterparty, and reflect on 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 name and details of the parties are enough to substitute them in an automatically generated 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 a getter for the property Type, in case you need multiple dispatch . But following YAGNI, let's leave it as it is.

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

All that's left is to transfer the “general” fields to ContractorBase. Now the base class looks logical. Details and counterparty representative are available in the system. In case of questions, you can try to contact by phone or email.

Who is an IP and what does it eat with

IP is a form of doing business without forming a legal entity. We will not go into the intricacies of the civil code. Let us dwell on the main thing: IP has OGRNIP (analog of OGRN ) and TIN. At the same time, the TIN of the individual Vasya Ivanova and IP Vasya Ivanova are one and the same number.

If we are interested in an individual in the context of labor or other contractual relations, then we can determine another entity “physical. person ”and associate it with counterparties. Counterparty - physical. person and individual entrepreneur. Then it will be impossible to make a mistake when starting an IP with an existing individual. A more extensive directory of individuals may be required in a different context (for example, SNILS will be required to pay wages). Then we can repeat the “trick” with TPT and add another table to each “section” in which we are interested in the data.

The question of how to determine that Ivanov Ivan Ivanovich from Kazan and Ivanov Ivan Ivanovich 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, which means we will need a form for each successor to the counterparty. Is logical. If it is permissible in the UI to have, instead of the list of contractors, a list of 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. Select the interface IContractorand put the abstract methods there. Rename it ContractorBaseto ContractorInfoand make this class non-abstract in order to be able to list vendors. Return ContractorTypeto distinguish the types of counterparties. EntityFramework materializes objects through Refletion, so we calmly leave the constructor and properties TypeandNameprotected. Information about counterparties does not arise by itself, but is added only together with the counterparty itself. We had to denormalize the table, which is "not very" from the point of view of consistency and the need for additional body movements when changing the fields that make up the "name", but not bad for performance.

Conclusion

Despite the fact that unions are 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, guaranteeing the completion of all required fields. It is only necessary to select all the possible cases and create an heir for each. 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, but it is much easier to set up Foreign Key for linked tables than in any other way. In addition, the approach can be applied recursively and formally, which increases reliability when designing a branched domain model.