How do you solve MySQL & Django Admin Foreign Key constraint

Question

I have a Style table to identify a model number for each product type:

class Style(models.Model):
    style = models.AutoField(primary_key=True)
    name = models.CharField('Title', max_length=150)
etc..

And I need a product table which identifies all the product variations of the aforemention model number:

class Product(models.Model):
    product = models.AutoField(primary_key=True)
    style = models.ForeignKey(Style, db_column='style')
    color = models.CharField(max_length=250, blank=True)
    price = models.IntegerField(max_length=10, blank=True)
etc..

However, the client wants to be able to suggest other products that go with the aforementioned style. So I wanted to add a few "related product" columns as a Foreign Key. But neither MySQL nor Django lets me add them.

So for now I just made them integer fields. I added below lines to the Style model:

    related_1 = models.IntegerField('Related Product1',blank=True,max_length=10, default=0)
    related_2 etc..

So for example, a polo shirt has a styleId =1.
It has 4 different colors and 3 fabrics.
7 products in all for 1 style.

The product table will have seven rows all with a styleid=1.

The vendor will manually select products that are related to the style, which go into related_1, related_2 etc.

I understand Django and MySQL views some potential circular logic there, because each product has a FK to a styleId. So if that styleId has a foreign key back to the same product, clashes can occur.

But in fact, you obviously don't want to allow the related products to reference a product w/ the same styleId...which can easily be handled by the app rather than the database.

My workaround allows the user to manually enter the related products into the form, but it would be really cool if I could get Django to pull a select dropdown with a list of valid product Ids. If possible.

Is it?

P.S. I did read the django docs on related_field but couldn't really follow what they were getting at. I tried a few things, but they didn't provide much in the way of sample code.

2nd P.S. I edited it per request w/ more detailed model info. I hope it's clearer now...

Answer 1

As each of your tables should be described as a django model, it would be helpful if you directly posted the model definitions rather than the table descriptions above. I sense that is where your real problem lies.

In Django you would typically first define a style, then define the products associating each of them with a style. Something like this:

style1 = Style.objects.create(styleId=1, name='jacket')

Product.objects.create(productId=1, Name='bob',
    Color='blue', Fabric='denim', styleId=style1)
Product.objects.create(productId=2, Name='sam',
    Color='red', Fabric='denim', styleId=style1)

You can then access the style of a given product like this:

product1 = Product.objects.get(pk=1)
product1.styleId.name

Or all of the products of a given style like this:

style1 = Style.objects.get(pk=1)
style1.styleid_set.all()

Answer 2

You should not want to create the structure you describe. Doing so violates a number of relational data principles. You can find the products for style#1 by querying against the product table. What are you hoping to accomplish by structuring things that way?

It is a bad idea to duplicate the information about which products go with which styles into the style table (or any other table). It requires you to update the data in more than one place every time you add a new product, or change the style for a product.

There is a bad "relational smell" (called "repeating groups") when you have multiple columns in the same table that mean the same thing, except for the trailing digit on the column name. How many columns for related_product will you put into your table? Doesn't that limit the number of products that could involve the same style? What if the product associated with related_product2 gets deleted. Will you do anything to move one of the other related_productN values into related_product2, or will you just leave a hole in the list of values?

How would you go about using the information in that format to see if a product is involved with a particular style? You would need to locate those styles where related_product1 = sought_product OR related_product2 = sought_product OR ....

As with almost all questions, one valuable answer is "what are you really trying to accomplish?" I'm saying that what you say you want to do, you do not actually want to do, for many reasons. Can you say what it is that you need to do that would be made easier with the structure you mention? {whichhand} showed some of what you can do in Django without duplicating the information about the product/style relationship.