【愚公系列】2022年01月 Django商城项目19-商品数据表的设计

一、SPU、SKU和商品数据库表设计

在电商项目里面为了准确的描述商品的区别，我们抽象出来两个概念，Spu和Sku这两个概念。

1.SPU

SPU(Standard Product Unit)：标准化产品单元。是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息的集合，该集合描述了一个产品的特性。通俗点讲，属性值、特性相同的商品就可以称为一个SPU。

2.SKU

SKU=Stock Keeping Unit（库存量单位）。即库存进出计量的基本单元，可以是以件，盒，托盘等为单位。

3.数据库设计ER图

4.模型类设计

from django.db import models from utils.models import BaseModel class GoodsCategory(BaseModel): """商品类别""" name = models.CharField(max_length=10, verbose_name='名称') parent = models.ForeignKey('self', related_name='subs', null=True, blank=True, on_delete=models.CASCADE, verbose_name='父类别') class Meta: db_table = 'tb_goods_category' verbose_name = '商品类别' verbose_name_plural = verbose_name def __str__(self): return self.name class GoodsChannelGroup(BaseModel): """商品频道组""" name = models.CharField(max_length=20, verbose_name='频道组名') class Meta: db_table = 'tb_channel_group' verbose_name = '商品频道组' verbose_name_plural = verbose_name def __str__(self): return self.name class GoodsChannel(BaseModel): """商品频道""" group = models.ForeignKey(GoodsChannelGroup, verbose_name='频道组名') category = models.ForeignKey(GoodsCategory, on_delete=models.CASCADE, verbose_name='顶级商品类别') url = models.CharField(max_length=50, verbose_name='频道页面链接') sequence = models.IntegerField(verbose_name='组内顺序') class Meta: db_table = 'tb_goods_channel' verbose_name = '商品频道' verbose_name_plural = verbose_name def __str__(self): return self.category.name class Brand(BaseModel): """品牌""" name = models.CharField(max_length=20, verbose_name='名称') logo = models.ImageField(verbose_name='Logo图片') first_letter = models.CharField(max_length=1, verbose_name='品牌首字母') class Meta: db_table = 'tb_brand' verbose_name = '品牌' verbose_name_plural = verbose_name def __str__(self): return self.name class SPU(BaseModel): """商品SPU""" name = models.CharField(max_length=50, verbose_name='名称') brand = models.ForeignKey(Brand, on_delete=models.PROTECT, verbose_name='品牌') category1 = models.ForeignKey(GoodsCategory, on_delete=models.PROTECT, related_name='cat1_spu', verbose_name='一级类别') category2 = models.ForeignKey(GoodsCategory, on_delete=models.PROTECT, related_name='cat2_spu', verbose_name='二级类别') category3 = models.ForeignKey(GoodsCategory, on_delete=models.PROTECT, related_name='cat3_spu', verbose_name='三级类别') sales = models.IntegerField(default=0, verbose_name='销量') comments = models.IntegerField(default=0, verbose_name='评价数') desc_detail = models.TextField(default='', verbose_name='详细介绍') desc_pack = models.TextField(default='', verbose_name='包装信息') desc_service = models.TextField(default='', verbose_name='售后服务') class Meta: db_table = 'tb_spu' verbose_name = '商品SPU' verbose_name_plural = verbose_name def __str__(self): return self.name class SKU(BaseModel): """商品SKU""" name = models.CharField(max_length=50, verbose_name='名称') caption = models.CharField(max_length=100, verbose_name='副标题') spu = models.ForeignKey(SPU, on_delete=models.CASCADE, verbose_name='商品') category = models.ForeignKey(GoodsCategory, on_delete=models.PROTECT, verbose_name='从属类别') price = models.DecimalField(max_digits=10, decimal_places=2, verbose_name='单价') cost_price = models.DecimalField(max_digits=10, decimal_places=2, verbose_name='进价') market_price = models.DecimalField(max_digits=10, decimal_places=2, verbose_name='市场价') stock = models.IntegerField(default=0, verbose_name='库存') sales = models.IntegerField(default=0, verbose_name='销量') comments = models.IntegerField(default=0, verbose_name='评价数') is_launched = models.BooleanField(default=True, verbose_name='是否上架销售') default_image = models.ImageField(max_length=200, default='', null=True, blank=True, verbose_name='默认图片') class Meta: db_table = 'tb_sku' verbose_name = '商品SKU' verbose_name_plural = verbose_name def __str__(self): return '%s: %s' % (self.id, self.name) class SKUImage(BaseModel): """SKU图片""" sku = models.ForeignKey(SKU, on_delete=models.CASCADE, verbose_name='sku') image = models.ImageField(verbose_name='图片') class Meta: db_table = 'tb_sku_image' verbose_name = 'SKU图片' verbose_name_plural = verbose_name def __str__(self): return '%s %s' % (self.sku.name, self.id) class SPUSpecification(BaseModel): """商品SPU规格""" spu = models.ForeignKey(SPU, on_delete=models.CASCADE, related_name='specs', verbose_name='商品SPU') name = models.CharField(max_length=20, verbose_name='规格名称') class Meta: db_table = 'tb_spu_specification' verbose_name = '商品SPU规格' verbose_name_plural = verbose_name def __str__(self): return '%s: %s' % (self.spu.name, self.name) class SpecificationOption(BaseModel): """规格选项""" spec = models.ForeignKey(SPUSpecification, related_name='options', on_delete=models.CASCADE, verbose_name='规格') value = models.CharField(max_length=20, verbose_name='选项值') class Meta: db_table = 'tb_specification_option' verbose_name = '规格选项' verbose_name_plural = verbose_name def __str__(self): return '%s - %s' % (self.spec, self.value) class SKUSpecification(BaseModel): """SKU具体规格""" sku = models.ForeignKey(SKU, related_name='specs', on_delete=models.CASCADE, verbose_name='sku') spec = models.ForeignKey(SPUSpecification, on_delete=models.PROTECT, verbose_name='规格名称') option = models.ForeignKey(SpecificationOption, on_delete=models.PROTECT, verbose_name='规格值') class Meta: db_table = 'tb_sku_specification' verbose_name = 'SKU规格' verbose_name_plural = verbose_name def __str__(self): return '%s: %s - %s' % (self.sku, self.spec.name, self.option.value) class GoodsVisitCount(BaseModel): """统计分类商品访问量模型类""" category = models.ForeignKey(GoodsCategory, on_delete=models.CASCADE, verbose_name='商品分类') count = models.IntegerField(verbose_name='访问量', default=0) date = models.DateField(auto_now_add=True, verbose_name='统计日期') class Meta: db_table = 'tb_goods_visit' verbose_name = '统计分类商品访问量' verbose_name_plural = verbose_name

二、首页广告数据库表设计

1.数据库设计ER图

2.模型类设计

from django.db import models from utils.models import BaseModel # Create your models here. class ContentCategory(BaseModel): """广告内容类别""" name = models.CharField(max_length=50, verbose_name='名称') key = models.CharField(max_length=50, verbose_name='类别键名') class Meta: db_table = 'tb_content_category' verbose_name = '广告内容类别' verbose_name_plural = verbose_name def __str__(self): return self.name class Content(BaseModel): """广告内容""" category = models.ForeignKey(ContentCategory, on_delete=models.PROTECT, verbose_name='类别') title = models.CharField(max_length=100, verbose_name='标题') url = models.CharField(max_length=300, verbose_name='内容链接') image = models.ImageField(null=True, blank=True, verbose_name='图片') text = models.TextField(null=True, blank=True, verbose_name='内容') sequence = models.IntegerField(verbose_name='排序') status = models.BooleanField(default=True, verbose_name='是否展示') class Meta: db_table = 'tb_content' verbose_name = '广告内容' verbose_name_plural = verbose_name def __str__(self): return self.category.name + ': ' + self.title

总结

商品数据库涉及要细分不变项和可变项的设计，一对一，多对多，一对多关联要尽可能细分，对于不确定的信息可以有json形式存储。对于访问量特别大的页面可以进行mongodb存储，mongodb对于存储文档数据库是个很好的选择，库存秒杀的情况redis和mq消息队列也是必不可少，幂函数等等。

Django 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。