Does a many-to-many relationship with a recursive

问题:

I have the following relationships (Business rules):

• User_Company : Many-to-many (multiple companies per user, multiple users per company)
• Ownership : One-to-One (For each entry in relationship table, it specifies whether user is an owner or employee. If owner then a percentage must be there)
• Company_ownership : One-to-many for company (Recursive relationship), as another company can also be an owner in a company, percentage must be given.

So ownership or a company can be made up of a number of companies and users.

So I have developed the following:

So does there have to be at least 4 tables for this sort of relationship or can it be simplified. I feel it is quite complicated and would not be intuitive for another developer? How could it be optimized and elegantly arranged?

回答1:

I think ownership and employment are different concepts, that would be more advised to have them separated.
Think about John who is one of the owners of A company and in the mean time he is the CTO of A.

Company and People can have a base to reduce redundancy of entities.

回答2:

Based on the suggestions of Thilo

A separate relationship table was created for owners and employees respectively. Furthermore, the company_ownership and ownership tables were removed as having a company as an owner is solved by adding an owning_user_id and owning_company_id where one will always be null. The percentage figure is added to this relationship table.

See below: