I just started working on a project where I am trying to implement some backend logic which includes creating a data model for the given problem. Here is a problem I came across and I am curious what approach would be more suitable for tackling it.
Model: There are various users in the system with different rights and structures but there is a very simple inheritance between them.
There is a basic user
that can browse and view some data, psot comments etc. Than, there is a member
who can do everything the user
can + something more and they share the basic data structures + member
has its own. And there is a producer
which is again just an extension of member
.
Each user
can (but does not have to) be a member
and each member
can (but does not have to) be a producer
.
1) My current idea:
Create table user
and tables member
and producer
which would reference each other using user's id. There would be 0 to 1 relationship between these tables:
USER {
id
username
email etc..
}
MEMBER {
userid
some_member_attributes
}
PRODUCER {
userid
some_producer_attributes
}
The advantage is, that it would save me a lot of NULL values in a single table and it feels somehow natural to me. The disadvatage is that I would have to query three different tables every time I would like to get some data from a producer
.
2) The other possibility is implementing this as a one table with attributes isMember
and isProducer
. But again, this will result in a lot of NULL values since most of the users wont be producers
. And its not very typical to have many 0 to 1 relationships in one model, I think...
What do you suggest? I hope its not an off-topic here, I am just trying to get this right so I wont have to reimplement is a dozen times in future. Thanks for any tips!