---

一、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 BaseModelclass 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_namedef __str__(self):return self.nameclass GoodsChannelGroup(BaseModel):"""商品频道组"""name = models.CharField(max_length=20, verbose_name='频道组名')class Meta:db_table = 'tb_channel_group'verbose_name = '商品频道组'verbose_name_plural = verbose_namedef __str__(self):return self.nameclass 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_namedef __str__(self):return self.category.nameclass 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_namedef __str__(self):return self.nameclass 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_namedef __str__(self):return self.nameclass 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_namedef __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_namedef __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_namedef __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_namedef __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_namedef __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_namedef __str__(self):return self.nameclass 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_namedef __str__(self):return self.category.name + ': ' + self.title

总结

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