I am currently designing a database for a project. I'd like to normalize it and I have come across a question that I would like to ask in a larger group, especially with regard to previous experiences.

About the situation: An organization has 0 to 4 contact partners. Of the four contact partners, two are on-site to the organization (local) and two are available throughout the country (global).

On an input mask, a user can select which contact from the contacts table is so-called "local contact partner 1", "local contact partner 2", "global contact partner 1" etc., so the order (order, "1" or "2") is relevant.

What would be the best way to structure the database? I have attached three examples, but please feel free to make other suggestions.

CodePudding user response：

I would recommend The 3rd solution. At one day in the future the business will come up with an exception and state an organization can have 5 or more contacts. If the primary key of OrganiationContacts is organizationId ContactId you also have the guarantee the same contact cannot be both local and global or defined twice as contact. The other structures cannot guarantee this and require additional validation logic.