should i have a table for each product type in a database-CodePudding

i am very confused about this, i have three different types of products, first i have physical products which simply are art products and affiliate products, and digital products such as courses , books, art pictures, my first approach was to create a separate table for each one like this:

class Category(models.Model):
   name = models.CharField()

class ArtType(models.Model):
    name = models.CharField()
class PhysicalArtProduct(modes.Model):
    category = models.ForeignField(Category)
    madeOf = models.ManyToManyField(AffeliateProduct) 
    artType = models.ForeignKey(ArtType)
    ........

class AffeliateProduct(models.Model):
    ........
class Course(models.Model):
    artType = models.ForeignKey(ArtType)
    .......
class Book(models.Model):
    .......

because PhyscialArtProduct are related to AffeliateProduct because PhysicalArtProduct are made of AffeliateProduct that's why i had to add many-to-many field in PhysicalArtProduct

my second approach is to make a single Product table and a ProductType table and build the relationship between the products themselves and the category and arttype by adding the necessary tables

which approach i should go with here, any help would be really appreciated

CodePudding user response：

If courses, books, art pictures, art products and affiliate products all have different fields then you need a separate table for each of them.

They probably also have common fields. You can have a table product for all the common fields and the other tables will have a one-to-one relationship with product. This is the design I recommend:

class Product(models.Model):
    name = models.CharField(blank=False, max_lenght=200)
    

class ArtType(models.Model):
    product = models.OneToOneField(Product, null=True, blank=True, on_delete=models.CASCADE)

class AffeliateProduct(models.Model):
    product = models.OneToOneField(Product, null=True, blank=True, on_delete=models.CASCADE)

class Course(models.Model):
    product = models.OneToOneField(Product, null=True, blank=True, on_delete=models.CASCADE)

class Book(models.Model):
    product = models.OneToOneField(Product, null=True, blank=True, on_delete=models.CASCADE)

You can set the Category to be a property inside Product. You just need to check which related record is created for the current product to set the appropriate category.

You can use validation for making sure that one product only has one related record in the possible related tables.

CodePudding user response：

this is basically what I was trying to do , i hope it's clear, i didn't add a lot of details yet but I hope this will provide enough information to understand

class Category(models.Model):
   name = models.CharField()
   
class ArtType(models.Model):
   name = models.CharField()


class Product(models.Model):
    user = models.ForeignKey(User, models.PROTECT)
    productId = models.UUIDField(default=uuid4, primary_key=True, 
    editable=False)
    picture = models.ImageField()
    discription = models.TextField(max_length=2000)
    price = models.FloatField()
    createdAt = models.DateTimeField(auto_now_add=True)
    updatedAt = models.DateTimeField(auto_now=True))
    cart_items = models.ManyToManyField(CartItem)


class PhysicalProduct(Product):
    quantity = models.PositiveBigIntegerField()
    colors = models.ManyToMany(Color)
    title = models.CharField(max_length=100)
    class Meta:
        abstract = True

class AffeliateProduct(PhysicalProduct):
    storeUrl = models.URLField()

class MstProduct(PhysicalProduct):
    purposes = models.ManyToManyField(ProductPurpose, null = True, blank = True, related_name='products')
    madeOf = models.ManyToManyField(AffeliateProduct, null = True, blank = True)
    category = models.ForeignKey(Category, on_delete=models.PROTECT, related_name = "physical_products")
    theme = models.ForeignKey(Theme, on_delete= models.SET_NULL, null = True, blank = True, related_name = 'physical_products')
    art_type = models.ForeignKey(ArtType, on_delete=models.SET_NULL, null = True,blank = True)


class DigitalContentLanguage(models.Model):
    languages = (
        ("ar","ar"),
        ("en","en")
    )
    language = models.CharField(max_length=2, choices=languages)

   class Course(Product):
       languages = models.ManyToMany(DigitalContentLanguage)
       artType = models.ForeignKey(ArtType)

  class CourseChapers(models.Model):
      course = models.ForeignKey(Course)
      ..............
   
  class Book(Product):
      languages = models.ManyToMany(DigitalContentLanguage)
       artType = models.ForeignKey(ArtType)
       file = models.FileField()
       ISBN = models.Charfield()
      author = models.CharField()