DB design for multiple types of entities

Question

I need to develop an application where there will be 4 types of user entities (administrators, partners, companies and clients), each user type has it's own set of details and they all should be able to do common operations like send messages, make payments and so on. These operations should be kept on a single table but they need to reference the exact user despite it's type.

What database design would be more appropriate?

Answer 1

user
================
id
user_type_id
name
etc

user_type
================
id
name (admin, partner...)
etc

user_detail
================
id
user_id
user_detail_type_id
value

user_detail_type
================
id
name

user_type_to_user_detail_type
================
id
user_type_id
user_detail_type_id
(maps which user types have which detail types)

Answer 2

I'd say this is a perfect case for inheritance. Put the common attributes in one table and inherit that to add custom attribute for your different user types.

Chaos answer seems a bit messy to me, alltough it'd be useful if you don't know in advance what the properties you need to store are.

Answer 3

Have a look at the three ways to do that in the Patterns of Enterprise Application Architecture:

http://martinfowler.com/eaaCatalog/singleTableInheritance.html

http://martinfowler.com/eaaCatalog/classTableInheritance.html

http://martinfowler.com/eaaCatalog/concreteTableInheritance.html

The choice depends on how many properties the 4 types of user entities will be sharing and also on the use cases that your system will require.

Answer 4

"I would just like to add one more thing, you suggest I have a table per each user type... I prefer this approach however how would I design a schema where I can say that user id 7 (admin) sent a message to user id 537 (client)? Or that a payment was received by user id 70 (company)?"

There is nothing to stop you from doing that. Have a table {sender recipient message(-id)} with primary key all three attributes and two FK {sender} and {recipient}. The FK's refer to the primary key of the table that holds the COMMON attributes of all users.

Now, your next question may be, "but I want a rule to say that no user of type X can directly send a message to any user of type Y".

That is the point where any current IMPLEMENTATION of a (so-called) relational DBMS shows its weaknesses. Even Oracle or DB2 can't do that declaratively. There is simply too very much for me to say about that subject to fit in this response.

BTW You seemed to have taken an interest in my response despite all the downvotes. Really appreciate that.