我想建立一个非常基本的每日销售报告。我正在努力决定如何构建数据库以最好地实现这一点。下面是它的一个用例:
- 2011年1月5日,供应商A的产品总收入为500美元
- 2011年1月5日,供应商A的产品总收入为200美元
- 2011年1月6日,供应商B的产品总收入为450美元
- 1月6日,供应商B的产品总收入为75美元
我目前的结构是:
PROVIDER table
- pk
- 提供者
PRODUCT table
- 提供者(FK)
- 产品
- 开始日期(销售)
- 结束日期
start_date
和end_date
是可能发生产品销售的时间。它仅用于参考,并不真正影响其他任何内容。
SALES table
- 产品(FK)
- 销售额
- 如何存储日期
sales
将是该产品的每日销售额($)。
我不太清楚如何储存销售额。销售额只能按每种产品的每日销售额计算。构造SALES
表的最佳方式是什么?非常感谢。
产品表:
- 这张表本质上应该是"暂时的"。这意味着它可以随着时间的推移而灵活变化
- 在2011年,你可能会以15.99美元的价格出售产品A,但在2012年,你想以16.99美元的价钱出售同样的产品,所以你希望你的桌子能够灵活应对这种变化
- 尽管存储在该表中的数据具有灵活性,但如果删除产品,则必须小心。如果删除产品,它将孤立销售表中任何匹配的销售交易记录,或者删除它们(取决于FK行为)
销售表:
- 该表本质上应该是"事务性的"。这意味着一行代表与销售相关的产品,在时间上被冻结
- 如果买家A在2011年以15.99美元的价格购买了产品A,您希望按原样记录这笔交易,在任何时间点数据都不会发生变化,这反映了一笔交易
- 如果买家B在2012年以19.99美元的价格购买了相同的产品A,您需要将其记录为单独的交易。当然是同一个产品,但是这一行代表一个新的交易
使用上述设置,您可以根据product
表中的情况更改价格,但这不会影响sales_transaction
表中记录的已发生销售额。
伪模式:
product
:
- id int(11)unsigned not null auto_increment
- name varchar(255)
- 价格小数(14.2)
- 主键(id)
- 唯一密钥(名称)
sales_transaction
:
- id int(11)unsigned not null auto_increment
- product_id int(11)unsigned非null
- provider_id int(11)unsigned非null
- 价格小数(14.2)
- created_at datetime默认值为"0000-00-00 00:00:00"
- 外键(product_id)引用删除级联中的产品('id')
- 外键(provider_id)引用删除级联上的提供程序('id')
provider
:
- id int(11)unsigned非null
- name varchar(255)不为null
- 主键(id)
- 唯一密钥(名称)
现在,您可以根据问题中的要求,运行查询以获取任何供应商在任何日期的任何产品的汇总。
示例查询:
# On Jan 5, 2011, Provider A makes $500 total off of its products
SELECT prov.*, SUM(sales.price)
FROM
provider AS prov
INNER JOIN
sales_transaction AS sales on sales.provider_id = prov.id
WHERE
provider.name = 'Provider A'
AND
sales.created_at BETWEEN '2012-01-05 00:00:00' AND '2012-01-05 23:59:59'
GROUP BY
prov.id
所提供的模式本质上是骨架式的,所以可以根据业务需求随意添加列,但它应该会让您朝着正确的方向前进。
另外,最后一条建议是,我建议您将日期时间存储在UTC中。如果你选择在当地时区购物,如果你做任何需要从当地时区转换的销售,你会遇到很多麻烦。
为什么销售表中没有日期字段?这将使每个记录在某一日期某一产品的销售总额。