1

I'm working on an e-commerce platform build using Django and Postgresql. In my platform I have an entity called "Category" and an entity called "Attribute". A category can have multiple attributes, but an attribute can only belong to one category.

For example, the "digital camera" category can have lens, image quality, etc. as its attributes.

I have come to realize that some attributes can belong to all categories. For example: packaging, shipping, customer service, etc.

In addition to these general attributes, in the future I may end up having attributes that belong to multiple categories. For example battery can belong to all categories in the Electronics department (categories belong to a department). But I'm not sure if this is a good idea as it may make things unnecessarily complex.

What's the best way to approach this? Please note that I need to be able to query the general attributes often. I have thought of the following solutions:

  1. Make a default category and assign those attributes to that category. In the code write a special logic that will always look at these categories.
  2. Allow a nullable foreign key in the attributes table. So an attribute can belong to no specific category indicating that it belongs to all categories.
  3. Make another table for general attributes.
  4. Store the category-attribute relationship in a third table. But then my question is how can I query for attributes that don't belong to any specific category?

I appreciate your help in advance.

UPDATE: Not sure if this is the best solution, but I took the easy route and ended up making nullable m2m relationship between attribute and the other models. Thank you all for your help.

Mehdi
  • 19
  • 2
  • Why not use a join table to connect Categories to Attributes? You can query for attributes not belonging to any specific category if there pks are not in the join table. – hancho Sep 05 '18 at 17:18
  • You wrote `some attributes can belong to multiple or all categories`. The solution might be very different depending on whether you only need some attributes for all categories or for multiple but not all. – Roman-Stop RU aggression in UA Sep 05 '18 at 17:24
  • Thanks for the quick reply, @hancho. The join table will soon have millions of items in it due to the nature of the platform. Do you think this solution will still be a reasonably fast solution then? And just so I understand better, do I have to store unique attribute PKs in that table in an array, and then compare that array to PKs in the attributes table? – Mehdi Sep 05 '18 at 17:28
  • @RomanKonoval, some attributes will belong to only one category (for functional reasons, and simplicity's sake) and some attributes will belong to all categories. The only reasons I said "multiple" is because I was trying to account for the unknown requirements that maybe in the future some attributes will belong to multiple categories. For example, battery can belong to all categories in the "Electronics" department. – Mehdi Sep 05 '18 at 17:31
  • 3
    @Mehdi you are dealing here with a classical [Entity-Attribute-Value](https://en.wikipedia.org/wiki/Entity–attribute–value_model) problem. One thing that I don't quite understand is whether `Category` table stores only types of entities from EAV or entities themselves? – Roman-Stop RU aggression in UA Sep 05 '18 at 17:33
  • @Mehdi Category describes data type here (basically a list of attributes, each having name and type). You want to be flexible in defining categories. So that you do not repeat attributes in all (or some) categories. The approach here is the same as for the problem of defining and reusing datatypes. Either use inheritance between categories or composition of categories. The topic is rather broad. Actually the question is off topic here and should be in software Engineering. – Roman-Stop RU aggression in UA Sep 05 '18 at 17:41
  • @RomanKonoval I guess I need to read the EAV article before I can answer your question. However, just to keep the ball rolling: category table stores entities like "digital camera", "mobile phone", etc. The attributes table stores attributes like "battery", "shipping", etc. I think I need to look into inheritance as you mentioned while I was typing this comment. :) – Mehdi Sep 05 '18 at 17:43
  • Let me see if I can move the question to software engineering. I didn't know there are subsections in stackoverflow. – Mehdi Sep 05 '18 at 17:44
  • 1
    YAEAV question! – Neil McGuigan Sep 05 '18 at 19:43
  • Read about sql/database subtyping/inheritance. Except instead of your database containing straightforward tables for your business state you are encoding those tables via EAV. EAV has a place but it's an anti-pattern for subtyping/inheritance. Find straightforward tables for your state. PS Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover – philipxy Sep 05 '18 at 19:51
  • My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". https://meta.stackexchange.com/q/204461 – philipxy Sep 05 '18 at 20:12
  • Please clarify via post edits, not comments. Please don't cross-post to other [se] sites. If you post elsewhere please delete this before it has answers. Otherwise use a flag to ask a moderator to migrate. Please give some actual designs in DDL & DML so we can speak precisely about rearranging them to other designs. PS [Is using a Master Table for shared columns good practice for an entire database?](https://stackoverflow.com/q/24150888/3404097) See relevant parts of [this](https://stackoverflow.com/a/47725225/3404097). See my other answers re EAV. – philipxy Sep 05 '18 at 20:41
  • Thanks for the comments @philipxy. I'm looking into EAV and database inheritance right now. I did search with different strings, but couldn't really find anything related. I guess that's due to my limited knowledge since I couldn't relate the answers to my questions. As you can tell I'm new to stackoverflow, so I really appreciate your directions. – Mehdi Sep 06 '18 at 01:45

0 Answers0