Best way to store category and subcategories of an item in Django models

Question

I want to store something like an Apple which will be stored as "Tree Fruit - Apple - Fuji - Organic" while there will be some items with more categories like Wheat which is "Cereal Grain - Wheat - Soft White Winter - Stephens - Organic".

class Item(models.Model):

    user = models.ForeignKey(User)
    type = models.CharField(max_length=100, default=None ,blank=True) #Cereal Grain
    commodity = models.CharField(max_length=100, default=None ,blank=True) #Wheat
    cl = models.CharField(max_length=100, default=None ,blank=True) #Soft White Winter
    variety = models.CharField(max_length=100, default=None ,blank=True) #Stephens
    market = models.CharField(max_length=100, default=None ,blank=True) #Organic

    def __str__(self):
        return u"%s" % (self.user)



class Shorten_Item(models.Model):

    user = models.ForeignKey(User)
    type = models.CharField(max_length=100, default=None ,blank=True) #Tree Fruit
    commodity = models.CharField(max_length=100, default=None ,blank=True) #Apple
    variety = models.CharField(max_length=100, default=None ,blank=True) #Fuji
    market = models.CharField(max_length=100, default=None ,blank=True) #Organic


    def __str__(self):
        return u"%s" % (self.user)

There will cost items associated reference to those tables.

class User_Variable_Items(models.Model):

    #variety = models.ForeignKey(User_Variety)
    category = models.CharField(max_length=100,default=None ,blank=True)
    sub_category = models.CharField(max_length=100,default=None ,blank=True)
    item = models.CharField(max_length=100,default=None ,blank=True)
    price = models.DecimalField(max_digits=20,decimal_places=2,default=None ,blank=True)
    unit = models.CharField(max_length=100,default=None ,blank=True)
    quantity = models.FloatField(default=0,blank=True)
    total = models.DecimalField(max_digits=20,decimal_places=2,default=None ,blank=True)

    class Meta:
        verbose_name_plural = _("User_Variable_Items")

class User_Fixed_Items(models.Model):

    #variety = models.ForeignKey(User_Variety)
    category = models.CharField(max_length=100,default=None ,blank=True)
    price = models.DecimalField(max_digits=20,decimal_places=2,default=None ,blank=True)
    unit = models.CharField(max_length=100,default=None ,blank=True)
    quantity = models.FloatField(default=0,blank=True)
    total = models.DecimalField(max_digits=20,decimal_places=2,default=None ,blank=True)

    class Meta:
        verbose_name_plural = _("User_Fixed_Items")

What would be the best way to have the User_Variable_Cost_Items and User_Fixed_Cost_items to reference to one of the table above (not both)?

Thanks

Answer 1

If you can reorganize your models, you can make use of models inheritance (https://docs.djangoproject.com/en/1.8/topics/db/models/#model-inheritance)

Your Item model could have been a sub class of Shorten_Item, given that it only contains one extra field, the cl = CharField()

You can keep Shorten_Item as it is, then make Item inherit from that model:

class Item(Shorten_Item):
    cl = models.CharField(max_length=100, default=None ,blank=True)

Now in your other class,

class User_Variable_Items(models.Model):

    ...
    # Add a foreign key to the base Item class
    # Which by inheritance can contain both keys from Shorten_Item and Item
    item = ForeignKey('Shorten_Item')

When you retrieve the ID of the Shorten_Item, use your business logic to determine if you want to use it as Shorten_Item or Item (by downcasting it to Item when needed)

If you cannot reorganize your models to benefit from inheritance, your only solution might be to use an item_id = IntegerField() as a "foreign key" that will be able to point to any table, and you'll have to write some more queries yourself