1

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!

Improve this question
2
  • How many extra attributes and how many users are you talking about when thinking of waste of space if putting all into one table. For me that smells like PrematureOptimization. If there are no other benefits than waste of space when having seperate tables for user,member/producer than this is a violation of the KISS_principle – k3b Jul 29 '14 at 15:09
  • This is definitely not a case of premature optimization. Some things are just the "better" way to do it because you know it is likely going to end up happening and it makes life easier anyways. Right now, a user has only member and producer attributes but it just seems extremely likely that other categories of users are going to popup. Other attributes of each type are going to be added. Both are easy additions if you plan for it. Not easy when you don't. There's planning your project and then there's choosing to remain ignorant to future needs under the guise of premature optimization. – Dunk Jul 30 '14 at 20:23
2

Your data model seems a tad insufficient. Someone logged in can no doubt be a user today, a member next week and a producer next year. This suggests that inheritance is the wrong way to go. You need to have a logged in user table, linked to a secondary table that hands out producer and member rights over a defined time period.

Peter Coad has done some excellent work in this field -try a Google search on his name.

Improve this answer
6
  • His idea is fine, other than the fact that there is no constraint to make sure that a Producer is also a Member. – InformedA Jul 29 '14 at 8:28
  • That sounds like a business rule rather than something that can be embedded in the data model. When someone is applying for Producer rights, only allow it if they are already a member. – kiwiron Jul 29 '14 at 8:31
  • I don't understand what you say at all (the answer part). I would just have 3 tables just like he does. The two solutions he proposed are precisely the ways people have used so much to model entity in SQL database that they turn them into a commonly used standard (JPA). – InformedA Jul 29 '14 at 8:38
  • I think you need to explore methods for mapping object hierarchies to relational tables. The important take-out is that inheritance only applies if object B is an object A forever. for instance, I fully expect to be a human for the rest of my days, but may spend no time at all as a producer on your site - or perhaps a year as a user and a couple more as a producer. Inheritance seems wrong here. This is a data modelling concept, and has no direct connection with the ORM framework, be it Hibernate, MyBatis, JPA or JDBC. – kiwiron Jul 29 '14 at 8:45
  • Now I understand why your answer makes a lot of sense. This would come much earlier if he mentioned that the modeling in this case is dynamic. That would gear the thinking toward composition than inheritance. Great answer! – InformedA Jul 29 '14 at 8:54
0

Your preferred answer has a name in the literature. Look up Class Table Inheritance. The single table solution also has a name. Look up Single Table Inheritance

Which one is better depends on the use case.

In your answer, you have also made use of a technique known as Shared Primary Key

Shared Primary Key can be very useful in combination with Class Table Inheritance. It enforces the 1-to-1 nature of the subclass/class relationship, and it provide keys for simple and fast joins.

If coding the joins becomes a nuisance, code the joins in views. You can even code a view that is the union of all the subclass views, and gives the appearance of a single table inheritance, with all those nasty NULLs in there.

I'm only validating what you already came up with on your own. But it might be useful to know that others have thought the same way.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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