0

I'm working on an app that has mainly two entities: Company and User. A User can be an employee, owner, or a partner.

My current approach of modelling this is: enter image description here

Capacity is enum with possible values: employee, owner, partner

Someone got me confused suggesting I should have three separate tables for each of these capacities, which I don't think is right.

I'm terribly unsure whether the capacity could grow to more in future.

Should I create another capacity table and reference it in Personnel as a foreign key, in case we wish to add more capacity? Would that be a scalable (and right) approach?

Thank you.

3
  • Schema design is an iterative process (look at the links and links within) and providing advice on schema design is a bit beyond the scope of this forum. What you want to do is to implement a solution, play around with it, fix the bugs (and there WILL be bugs), change the schema - then rinse and repeat. I suggest that you do that and then come back to us if you have more specific questions. p.s. welcome to the forum! :-)
    – Vérace
    Commented Feb 29, 2020 at 12:35
  • In your model a company can have more than 1 owner. Is this what you want? Also, a user can be an owner, employee or partner of multiple companies. Is this what you want? Commented Feb 29, 2020 at 12:56
  • @Colin'tHart yes, you're right Commented Feb 29, 2020 at 13:27

1 Answer 1

1

I would not have separate tables but you might want to consider not using enum for the capacity column.

Instead use a table named, perhaps, capacity that has an id referenced in the personnel table (much like user ID and company ID). That makes it trivial to add new titles/positions without disturbing anything else.

2
  • And here's why you don't want to use ENUMs!
    – Vérace
    Commented Feb 29, 2020 at 12:31
  • yup, that's exactly what I had in my mind. Thanks very much. Commented Feb 29, 2020 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.