Like many others before me, I am currently trying to design a database to handle product management and sale for Organizations. My problem is that I created a main class Product (and it’s associated Prices for a future Stripe integration) and I need to add some specifc fields to “child classes” of Products like MembershipType or Event (and even more). Membership has range duration validity while Event has a specific date validity (and a merchandising product type would have it’s own). Eventually, there would be more type of products.
MembershipType is an important model for my business logic as a Member of an organization will need to have a valid Membership.
As a customer will possibly buy many different types of products in an order, it would be easy to use the multi table inheritance ( Models | Django documentation | Django ). But I came up to this article ( Django's three types of model inheritance ) where the author give some issues about this implementation.
So the three options that I see are:
- a fat Product with a type field and (too) many fields possibly set to None
- multi table inheritance: which ‘duplicate’ tables in my db but give me the ability to see all my products from Product.objects.all()
- Abstract base class which allow me to add specific model types fields but, in the same time, I will need to add manytomany relationship between orders and types of products
- A base Product class and child classes with product as a OnetoOne field without
parent_link=True but make the code a bit complex especially in django Admin. The ‘UX’ in not great as an admin would need to create a Product and then to associate the MembershipType to this product and fill the specific fields.
Is anyone here can help me solve this design issue.
Thanks in advance !
1 Like
Personally, I’m in favour of a single model with many nullable fields in this scenario. I’d add a category field so I can easily distinguish between different types of products. I believe the simplicity of this approach offsets the hassle of having to deal with nullable fields. And it’s probably the easiest option to migrate away from, if you happen to decide it was the wrong choice down the road.
Also take a look at django-polymorphic, it provides a bunch of shortcuts to make the multi-table approach much more ergonomic.
BTW if you want to do more research on the topic, this concept is often referred to as “polymorphism” or “polymorphic relationships”. Might be helpful for your search terms.
<opinion>
Any of the options you’re showing could be right - but the decision would depend on a lot of information that you haven’t posted here and might not even have available yet. There are tradeoffs for each, and you want to figure out the right balance between performance, scalability, and ease of development.
</opinion>
When I have a situation like this, the first thing I do is gather as many details about the implementation as possible. For example, how many variations of models and fields are you looking at for all the different types? Then you would want to identify all the different ways in which this data will need to be used - pages, forms, reports, etc.
You’ll also want to have a very realistic view of the total amount of data (number of rows of each type) and the level of activity of the site.
All these factors can influence the decision process.
Thank’s for the advice ! I will take a look at it
<respect>
First of all, thank you for your response. I can’t count how often your contributions to this forum helped me in this Django world ! Thank you for your time.
</respect>
For a first iteration, I was thinking about 5 variation of Product with ~5-10 fields each.
Each ProductType will have it’s own page (site and admin) and will be linked to a Financial view of the organization in accounting year (really classic stuff).
If I think about the volume of data, I would say there will be about a dozen of each ProductType per organization with about 100 members to manage (but only 5-10 users of the software). The amount of organizations will be linked to my ability to make my Saas valuable for them of course, so difficult to predict, but 1000 will already be a tough work.
With those insights would you have any recommendation based on your experience ?
I know how I would do it - I would go with the multi-table structure. But then, I’ve also been working with normalized relational data structures since 1987 so it’s a set of concepts that I’m quite comfortable with. It’s just “naturally” how I now think of such things.
But I’m very pragmatic about such things as well.
I also agree with @villager above suggesting the “wide table” approach as a first step for the reasons mentioned. It is something you can start with, then refactor away from if necessary. And my gut reaction is that based on the quantities of data you’re talking about, that refactoring may never be necessary.
(I’m currently working on a project having two wide tables, one with 46 columns and the other with 32. I’m not going to blink at a model with 30-50 columns.)
1 Like