luoshiben's avatar

Table inheritance strategies

Hi All,

In my current project I have various types of users. For sake of example, let's say the types include admin (will access back-end administrative portals), manager (will access dashboards), and customer (uses the front-end ecommerce system). All of these user types have a set of fields in common, including username, email, name, and password. However, each type also has a number of fields that are very specific. For example, an admin will have a role, a manager will have a domain that he belongs to, and a customer will have many fields for profile/demographics like gender, birthday, home address, work address, etc. (My business rules call for more differences than those mentioned. This is just an illustration.)

To model this relationship, I have a base users table with the fore-mentioned fields, then one "child" table for users_admins, users_managers, and users_customers that contain the fields that are specific to each type. I'm using a polymorphic relationship via Eloquent (morphTo() and morphOne()) to tie these tables together, which means that the users table also has userable_id and userable_type fields.

So far so good. My problem and question revolve around the best way to handle these entities. Specifically, does anyone have a good idea on the best way to treat the child user type model and the parent user model as a single entity? I feel like there should be a factory class to get a single user entity instance, and a repository to save the instance. However, in the case of the factory I can only return a user type instance, but that's only a part of the complete user. The same issue exists with the repository. When it comes time to persist a child user entity I also need to persist the parent user entity, which means that I need to instantiate both parent and child user entities, set attributes, then pass both to a repository save method.

In the end, dealing with two models everywhere for one User just feels clunky. Maybe that means that I've modeled it incorrectly from the beginning. I could simplify the code and merge these into a single table, but would end up with dozens of nullable fields. Thoughts, ideas, suggestions?

Cheers!

0 likes
7 replies
luoshiben's avatar

To further the conversation, another approach of course is single table inheritance. This means that my data structure would involve putting all attributes on a single users table, with a large number of them being null depending on the user type. I could then override the Eloquent model newFromBuilder method to use a factory to return a specific model for the user type based on a user_type field on the users table. Of course, a table with dozens of null fields is what I was trying to avoid, though this solution does seem cleaner.

A somewhat hybrid approach is presented here: https://github.com/ThibaudDauce/EloquentInheritanceStorage. Essentially, a single table inheritance methodology is used. However, instead of creating one big master table with nullable fields, you instead create multiple "inherited" tables, as I have started to do, then use a database view to bring them together into the single master table. While this seems to abstract away my main aversion to the STI pattern, there are some drawbacks to using views that I'd like to avoid.

What I'd like is a way to use an inheritance pattern at the database level, but use a single (type-specific) model which handles working with the two underlying tables as if they were just one. Is this even a good idea? Anyone have experience in doing this?

luoshiben's avatar

Yet another approach could be to create some sort of trait that is applied to all child user type classes that essentially maps parent user attributes onto the child and includes methods to handle creating and updating of the parent user model. On the surface this wouldn't be too difficult. I'm afraid, though, that once I started using Eloquent's fluid interface to create more complex queries involving the child models that things would break down pretty quickly.

I'd love to hear from anyone in the community that has experience with this same scenario. Thanks!

RemiC's avatar
RemiC
11 years ago
Best Answer
Level 8

As long as having some nullable fields in the user table does not harm your DB performances (in most case it won't) I would choose the single table , multiple class path. Just treat the additionnal attributes as they don't exist when you don't need them.

Make a base User class with functions & relationships that are shared by every user type, then make subclasses that extend this base class. I would then use the base User class only for loggin purpose, then have a factory method that instanciate the 'correct model/repository' as needed with a User object as an input.

You can use global scope based on the user type to filter out queries.

I would use one repository per user type though as queries are likely to differ regarding the user type.

Those are just leads, never tried it myself as Eloquent + single table inheritance felt kind of 'hacky', so I'm not sure it's even possible :)

1 like
luoshiben's avatar

Thanks for the input, @RemiC. It just feels so "dirty" to have 30+ nullable fields in a single table. However, the pain points have been greater and greater the more I've used the polymorphic approach so I'm definitely leaning back towards a single table inheritance method.

This implementation here looks pretty good: https://github.com/Nanigans/single-table-inheritance

I'll probably adapt Nanigan's package for L5 and give that a whirl. I'm in early development stages, so am still open to other thought's that anyone might have on this topic!

1 like
RemiC's avatar

Ok, didn't know this package. I think you should have no difficulty porting it to L5, eloquent hasn't change so much.

The only thing that ticks me in his approach is that you have to define the subclass in the parent class itself, that's kind of weird :)

luoshiben's avatar

Yeah, agreed. I'll have to look at refactoring that. I appreciate you lending a second opinion, RemiC. I'll mark your original post as the answer. Since there's probably not a single perfect solution, though, anyone is welcome to provide other strategies and ideas.

Please or to participate in this conversation.