需要数据库设计建议
我需要设计一个用于药品日常销售的表格。
有数百种产品类型{名称，代码}。
雇用了数千名销售人员来销售这些产品{名称，代码}。
他们从不同的仓库{名称，代码}收集产品。
他们在不同的领域工作 ->区域 ->市场->网点等{都有名称和代码}
每种产品都有各种类型的价格{生产价、贸易价、商业价、折扣价等}。而且，销售人员可以自由地从这些组合中进行选择来估算销售价格。
问题是，日常销售需要大量的数据输入。几年之内可能会有千兆字节的数据（如果不是太字节的话）。如果我需要显示每日、每周、每月、每季度和每年的销售报告，我将需要各种类型的 sql 查询。
这是我的初始设计：
Product {ID, Code, Name, IsActive}
ProductXYZPriceHistory {ID, ProductID, Date, EffectDate, Price, IsCurrent}
SalesPerson {ID, Code, Name, JoinDate, and so on..., IsActive}
SalesPersonSalesAraeaHistory {ID, SalesPersonID, SalesAreaID, IsCurrent}
Depot {ID, Code, Name, IsActive}
Outlet {ID, Code, Name, AreaID, IsActive}
AreaHierarchy {ID, Code, Name, PrentID, AreaLevel, IsActive}
DailySales {ID, ProductID, SalesPersonID, OutletID, Date, PriceID, SalesPrice, Discount, etc...}
现在，除了索引之外，我如何规范我的 DailySales 表以实现未来几年不需要更改的细粒度设计？
请根据上述信息向我展示仅DailySales 数据输入表（从中查询所有类型的报告）的示例设计。
我不需要详细的设计建议。我只需要有关 DailySales 表的建议。有什么方法可以打破这个特定的表以达到粒度吗？
I need to design a table for daily sales of pharmaceutical products.
There are hundreds of types of products available {Name, code}.
Thousands of sales-persons are employed to sell those products{name, code}.
They collect products from different depots{name, code}.
They work in different Areas -> Zones -> Markets -> Outlets, etc. {All have names and codes}
Each product has various types of prices {Production Price, Trade Price, Business Price, Discount Price, etc.}. And, sales-persons are free to choose from those combination to estimate the sales price.
The problem is, daily sales requires huge amount of data-entry. Within couple of years there may be gigabytes of data (if not terabytes). If I need to show daily, weekly, monthly, quarterly and yearly sales reports there will be various types of sql queries I shall need.
This is my initial design:
Product {ID, Code, Name, IsActive}
ProductXYZPriceHistory {ID, ProductID, Date, EffectDate, Price, IsCurrent}
SalesPerson {ID, Code, Name, JoinDate, and so on..., IsActive}
SalesPersonSalesAraeaHistory {ID, SalesPersonID, SalesAreaID, IsCurrent}
Depot {ID, Code, Name, IsActive}
Outlet {ID, Code, Name, AreaID, IsActive}
AreaHierarchy {ID, Code, Name, PrentID, AreaLevel, IsActive}
DailySales {ID, ProductID, SalesPersonID, OutletID, Date, PriceID, SalesPrice, Discount, etc...}
Now, apart from indexing, how can I normalize my DailySales table to have a fine grained design that I shall not need to change for years to come?
Please show me a sample design of only the DailySales data-entry table (from which all types of reports would be queried) on the basis of above information.
I don't need a detailed design advice. I just need an advice regarding only the DailySales table. Is there any way to break this particular table to achieve granularity?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您要生成大量数据并需要根据过去的数据生成报告，您应该考虑使用 商业智能引擎。通常，这些引擎允许您将历史数据存档在单独的数据存储中（这样您就不会弄乱日常工作数据库），但还可以从存档数据中获取统计数据和报告。
If you are going to generate massive amounts of data and need to generate reports based on past data, you should consider using a business intelligence engine. Typically these engines allow you to archive historical data in a separate data store (so that you don't clutter the daily work database), yet to obtain statistical data and reports out of the archived data.
为什么不在产品上注明日期和价格，这样您就可以从每日销售表中删除价格，因为您可以通过加入获得它。
除非销售员可以在数据库中没有任何理由的情况下更改价格。
在给定日期，一名推销员只能在一个商店吗？如果是这样，那么您可以删除outletid。
您有 PriceiD、SalesPrice 和 Discount。如果我知道折扣、折扣店 ID 和原价，那么我可以确定税费，从而计算 SalesPrice，这样您就可以放弃它。
但是，这意味着您要按日期存储税务信息，以跟踪销售发生时的情况。
我的观点是，您应该查看另一个表中已有的内容，然后可以简化每日销售表。
例如，您需要按日/月/年将其中的信息提取到暂存表中，以帮助按日期聚合数据，以便更快地生成报告。
您的问题中有很多未知数，但希望这会对您有所帮助。
更新：根据评论，
我有一个表，其中包含有关某人何时使用资源的使用信息，并且该表很快就变大了。因此，我们决定只保留 2 或 3 年的数据，其余数据将被汇总，并将原始数据转储到文件中以供存档。
在查看行数时，您需要决定需要保留多少数据，以及如何归档旧数据，以便在绝对需要时使其可用，但是，您可以生成应该的报告事先需要。
通过减少列数，您将对存储空间产生很大的影响，如果这是一个问题，因为许多列可能不为空。
Why not put a date and price on the product, so that you can drop the price from the dailysales table, since you can get it by joining.
Unless the price can be changed by the salesman, without any rationale in the database.
On a given date, can a salesman only be in one outlet? If so then you can drop the outletid.
You have PriceiD, SalesPrice and Discount. If I know the discount and outlet id and original price then I can determine taxes and so calculate the SalesPrice, so you can possibly drop that.
But, this would imply that you are storing tax info by date, to track what it was when the sale took place.
My point is that you should look at what already exists in another table and you can then simplify the daily sales table.
You will want to pull information from it into staging tables by day/month/year, for example, to help aggregate the data by date, so that your reports will be generated more quickly.
You have a lot of unknowns in your question, but hopefully this will help.
Update: Based on comment
I had a table that contained usage info on when resources were used by someone, and that table got large quickly. So, we decided that we would only keep 2 or 3 years of data, and the rest would be aggregated, and the raw data dumped to a file for archival purposes.
When looking at the number of rows, you will need to decide how much data you need to keep, and how you can archive the old data, to make it available if it is absolutely needed, but, you can generate the reports that should be needed beforehand.
By reducing the number of columns you will have a large impact on storage space, in case that is a concern, as many of the columns will probably not be null.
您正在寻找的称为数据仓库（DW）。我建议您看一下 Ralph Kimball 的“数据仓库工具包”——它提供了零售销售数据仓库设计的示例。这是一个非常简化的（初稿）示例，展示了它的外观。您会注意到这是一个针对报告和分析而优化的非规范化结构。事实表的粒度通常是收据上的一项（行）。希望这能为您指明解决方案。 DW 几 TB 就可以了。
What you are looking for is called data warehouse (DW). I suggest you take a look at "The Data Warehouse Toolkit" by Ralph Kimball -- it has examples of data warehouse designs for retail sales. Here is a very simplified (first draft) example of how it may look like. You will notice that this is a de-normalized structure optimized for reporting and analytic. The grain of the fact table is usually one item (line) on a receipt. Hope this points you towards your solution. Few terabytes for a DW is ok.