1

I have a problem figuring out the relations between some of my database tables. The scenario for this buisiness case is a b2b buissnes.

I have 3 tables:

  • users
  • companies
  • distributors.

A Company has multiple users (1 to many) A Company has mutltiple distributors (Many to Many) A Distributor has multiple companies (Many to Many) A User has 1 company (Many to 1)

Now im getting stuck on the next step.

I've build some roles and permissions into the system.

A user can be: - A distributor - A Company (manager) - User

In the perfect scenario a user can be a distributor or a company. How can i make this possible in just the database. I thought of a solution but it isnt the best solution. It would make it that the user would be in a OneToMany relationship with the company and the distributor. The relation could be null to that when a user is a company the relation with the company will be set. If a user is a distributor the relation with the distributor will be set and the company relation will be null.

I dont think that would be the best solution but i cant figure out a better solution.

How can i make it that a user can be a distributor or company without setting the relation to 0.

enter image description here

Improve this question
2
  • 1
    I'm not sure it suits your need and definition but : make distributors and company one and same table and then have a many to many "relationship" tables which regroup two company id and a field defining which type of relation do the comoany have (ex : distributors). Then you can map users only to one or many company. If you map it to many companies then you would likely needs also a joint table with both ids and an extra fields defining the type of relation ship between the user and the company. Commented Sep 10, 2019 at 6:55
  • Part of your problem could be due to using "has" for all the relationships. Replace this with "manages", "works for", "distributes through" or whatever is specific for the job at hand. There will be more foreign key columns but each will be unambiguous. Commented Sep 10, 2019 at 12:15

1 Answer 1

Reset to default
3

Alternatives for the user relationship

What are the theoretical alternatives:

  1. you have a class User with an association to Company and another one to Distributor. You also have a constraint between the two associations, that only one can be active at a given moment in time. Consequence: the association can change over time and a user once assigned to a company can later be assigned to a distributor.
  2. you have a general class User with two specializations: CompanyUser inly associated with Company and DistributorUser associated with a distributor. Consequence: although a company user may change company over time, he/she might not change to a distributor (would require creation of a different user).

Choice of the alternative

What model seems the most accurate to you ? What would be the other differences between a company user and a distributor user that could justify the specialization ?

I do not have all the cards in hand, but intuitively I’d go for 1, which would be implemented exactly as you’ve described.

The approach 2 would be implemented either (also) as you’ve described (using the single inheritance table pattern) or with 2 tables (assuming User is abstract and using concrete table inheritance pattern, but the two tables might look very similar), or with 3 tables (using the class table inheritance) and a couple of additional relations requiring a lot of sql JOINs.

Now, looking at these other alternatives, unless company and distributor users would be very different, the additional tables seem to me as an overkill and I’d still stick to your initial approach, which seems to me a very good choice.

Other alternatives on the company/distributor side ?

One additional question worthwhile to investigate, however: are you sure that distributor and company are not two specialization of an abstract LegalEntity ?

In this case you could consider simplifying things on the user side by making only one relation between user and LegalEntity to which he/she belongs. You would then manage the legal entity the distributor and the company as tables of a class inheritance table scheme that implements the inheritance scheme between the classes.

May be this last alternative is an overkill, but depending on the similarities between distributor and company, you could the additional complexity could be compensate by more consistency and less redundancy in the code.

Improve this answer
1
  • Thanks for a push in the right direction. I have gone with solution 2. I've extened the relations of the user table and made the relations with the other entities nullable. If the company and distributor actually were 2 specializations of an abstract LegalEntity i would have combined those 2 in a table with a indentifier but in my case those 2 enities are not the same abstract entity. Thanks for the help! Commented Sep 10, 2019 at 10:47

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.