Two mutually exclusive many-to-many relationships

Question

Imagine an online shop. You have goods. Some goods have size, some don't. I've got an orders table:

id int not null,
...

orders_products table:

order_id int not null,
product_id int null,
size_id int null,
...

products table:

id int not null,
...

sizes table:

id int not null,
product_id int not null,
...

Right now either product_id or size_id is not null. In other words, primary key is order_id + (product_id xor size_id). Not both.

In Django's terms that would be:

class OrderProduct(models.Model):
    product = models.ForeignKey(Product, null=True, on_delete=models.CASCADE)
    size = models.ForeignKey(Size, null=True, on_delete=models.CASCADE)
    order = models.ForeignKey('Order', on_delete=models.CASCADE)
    amount = models.PositiveSmallIntegerField()

class Order(models.Model):
    products = models.ManyToManyField(Product, through=OrderProduct, related_name='orders')
    sizes = models.ManyToManyField(Size, through=OrderProduct, related_name='orders')
    ...

At least that's what I have right now. But I don't like having two mutually exclusive foreign keys in orders_products table. Or two attributes in Order model. One of which (sizes) is probably redundant.

So, I probably have to remove sizes attribute from the Order model. Is that it? Or should I make product_id not null in orders_products table? And have size_id only when the product comes in different sizes? Any other suggestions?

I've marked the question with django, python, postgresql tags. That's because those are what I'm using right now. But I'm not stuck on any particular language, but SQL.

UPD I just realized I have denormalized sizes table. There are mostly S, M, L sizes there.

And right now I see four options:

1. The way I have it now. Order.products and Order.sizes appear to work. They get to nonintersecting sets of products. But there is a possibility for inconsistencies in database (both orders_products.product_id and orders_products.size_id are set or not set).

2. What suggested maverick: generic foreign keys.

3. Normalize sizes table (many-to-many relationship):

   products table:

   id int not null,
   ...
   

   products_sizes table:

   product_id int not null,
   size_id int not null,
   ...
   

   sizes table:

   id int not null,
   ...
   

   Then, having orders_products table this way:

   order_id int not null,
   product_id int null not null,
   size_id int null,
   ...
   

   kind of makes more sense. Well, there's still possibility for orders_products.size_id being null for products having size. And for orders_products.size_id being linked to a size the product doesn't have.

   Generic foreign keys won't most likely do in case of normalized tables.

4. Extract product_variants table (what consumer basically buys):

   products:

   id int not null,
   ...
   

   sizes:

   id int not null,
   ...
   

   product_variants:

   id int not null,
   product id int not null,
   size_id int null
   

   orders_products:

   order_id int not null,
   productvariant_id int not null,
   amount int not null
   

   The statement about generic foreign keys seems to hold here as well.

Which one is better?

Answer 1

I decided to settle on my last idea. Normalize sizes table, by creating intermediate productvariants table, which holds entities representing things customers buy.

orders table:

id int not null,
...

orders_productvariants table:

order_id int not null,
productvariant_id int not null,
...

productvariants table:

id int not null,
product_id int not null,
size_id int not null,
...

products table:

id int not null,
...

sizes table:

id int not null,
...

Additionally, I have a size with empty name, for items that have no size.

Answer 2

Consider using Generic Foreign Key for OrderProduct on Product and ProductSize. It stores the object type and object id which provides mutual exclusivity among two Foreign keys.

class OrderProduct(models.Model):
    ...
    limit = models.Q(app_label = 'app', model = 'Product') | models.Q(app_label = 'app', model = 'ProductSize')
    content_type = models.ForeignKey(ContentType, limit_choices_to = limit)
    object_id = models.PositiveIntegerField()
    content_object = generic.GenericForeignKey('content_type', 'object_id')