I have to develop a credit card applicatoin where you will see 4 kinds of user organizations (managers, partners, companies and clients), each user type has it's own group of particulars plus they all should have the ability to do common procedures like send messages, make obligations and so forth. These procedures ought to be continued just one table but they have to reference the precise user despite it's type.
What database design could be appropriate?
I'd say this can be a perfect situation for inheritance. Place the common characteristics in a single table and inherit that to include custom attribute for the different user types.
Chaos answer appears a little untidy in my experience, alltough it would be helpful if you do not know ahead of time exactly what the qualities you have to store are.
Take a look in the 3 ways to achieve that within the Designs of Enterprise Application Architecture:
The option is dependent on the number of qualities some kinds of user organizations is going to be discussing as well as on the utilization cases that the system will need.
"I'd much like to include yet another factor, you suggest I've got a table per each user type... I favor this method however wouldso would I design a schema where I'm able to state that user id 7 (admin) sent a note to user id 537 (client)? Or that the payment was received by user id 70 (company)?"
There's nothing to prevent you from doing that. Possess a table with primary key the 3 characteristics and 2 FK and . The FK's make reference to the main key on the table store the COMMON characteristics of customers.
Now, the next question might be, "however i desire a rule to express that no user of type X can directly send a note to the user of type Y".
That's the stage where any current IMPLEMENTATION of the (so-known as) relational DBMS shows its weak points. Even Oracle or DB2 can't do this declaratively. There's way too greatly that i can say about this susceptible to easily fit in this response.
BTW You appeared to possess taken a desire for my response despite all of the downvotes. Really appreciate that.
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)