回收数据数据库设计
我正在努力想出一个适当的设计来保存回收数据。数据以三个月为周期，针对该月的每周进行收集。所以数据如下所示（他们现在收集 Excel 中当前的数据）：
Month 1 | Plastic | Metal | Newspaper | Cardboard | Paper
---------------------------------------------------------------------------------
Measurement: | (x) 19 Gal | 19 Gal | 19 Gal | 19 Gal | 19 Gal
| 36 Gal | 36 Gal | 36 Gal | 36 Gal | 36 Gal
| Lbs | Lbs | Lbs | Lbs | Lbs
| Other:(txt) | Other | Other | Other | Other
---------------------------------------------------------------------------------
Week 1 | 1
Week 2 |
Week 3 |
Week 4 |
Week 5 | 2
----------------------------------------------------------------------------------
Total (Lbs) | Z
在这个示例中，总数 (Z) 将是将 3 个 19 加仑箱转换为磅
让我头疼的部分是每个可回收的产品还具有多种附加属性，因此塑料具有垃圾箱尺寸、如何回收等......
我已阅读EAV 和类表继承，但他们觉得这个问题不“正确”。提前致谢。
I am struggling to come up with a proper design to hold recycling data. The data is collected on a three month cycle, for each week of that month. so the data looks like the following (they collect the data currently in excel right now):
Month 1 | Plastic | Metal | Newspaper | Cardboard | Paper
---------------------------------------------------------------------------------
Measurement: | (x) 19 Gal | 19 Gal | 19 Gal | 19 Gal | 19 Gal
| 36 Gal | 36 Gal | 36 Gal | 36 Gal | 36 Gal
| Lbs | Lbs | Lbs | Lbs | Lbs
| Other:(txt) | Other | Other | Other | Other
---------------------------------------------------------------------------------
Week 1 | 1
Week 2 |
Week 3 |
Week 4 |
Week 5 | 2
----------------------------------------------------------------------------------
Total (Lbs) | Z
In this sample, the total (Z) would be a conversion of 3 19 gallons bins to lbs
The part that is making my head hurt is each Recyclable product also has multiple attributes attached to them, so Plastic has a bin size, how it was recycled, etc....
I have read up on EAVs and class Table inheritance, but they don't feel 'right' for this problem. Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的案例看起来像是 Gen-Spec 设计模式的一个实例。 Gen-spec 通过超类-子类层次结构为面向对象程序员所熟悉。不幸的是，关系数据库设计的介绍往往会跳过如何为 Gen-Spec 情况设计表。幸运的是，它很好理解。在网络上搜索“关系数据库泛化专业化”将产生几篇关于该主题的文章。您的一些点击将是之前关于 SO 的问题。
诀窍在于子类（专用）表的 PK 的分配方式。它不是由某种自动编号功能生成的。相反，它是超类（通用）表中 PK 的副本，因此是对其的 FK 引用。
因此，如果情况是车辆、卡车和轿车，则每辆卡车或轿车都会在车辆表中拥有一个条目，卡车也会在卡车表中拥有一个条目，其 PK 是车辆表中相应 PK 的副本。轿车和轿车桌也是如此。只需进行联接即可轻松确定车辆是卡车还是轿车，并且您通常希望在此类查询中联接数据。
Your case looks like an instance of the Gen-Spec design pattern. Gen-spec is familiar to object oriented programmers through the superclass-subclass hierarchy. Unfortunately, introductions to relational database design tend to skip over how to design tables for the Gen-Spec situation. Fortunately, it’s well understood. A web search on “Relational database generalization specialization” will yield several articles on the subject. Some of your hits will be previous questions here on SO.
The trick is in the way the PK for the subclass (specialized) tables gets assigned. It’s not generated by some sort of autonumber feature. Instead, it’s a copy of the PK in the superclass (generalized) table, and is therefore an FK reference to it.
Thus, if the case were vehicles, trucks and sedans, every truck or sedan would have an entry in the vehicles table, trucks would also have an entry in the trucks table, with a PK that’s a copy of the corresponding PK in the vehicles table. Similarly for sedans and the sedan table. It’s easy to figure out whether a vehicle is a truck or a sedan by just doing joins, and you usually want to join the data in that kind of query anyway.
首先，请告诉我您从来没有使用“第 1 周”等，而是使用间隔（即起始日期、结束日期）。
现在...您对表继承有什么问题？这看起来是一个经典的例子。
基类/表将是具有一些共同属性的可回收产品......
这是问题所在吗？除了名字之外找不到任何“共同点”？
计量单位又如何呢？即参考另一张详细说明公斤、加仑、立方英尺等的表格？
也许是“危险”标志？
从那时起，您可以专门为“塑料”添加“回收方法”字段。它指向另一个列出可用替代方案的表。
“Bin Size”是一个计量单位吗？这应该是一个基本的“类别”属性（即所有塑料均按重量测量）还是应该取决于不同的事物？ （即塑料类型？收集点类型？）
无论如何，除了数字 ID 和一些描述性字段之外，基表中没有太多内容是完全可以的。
For a start, please tell me that you are not using "Week 1" etc., ever, but an Interval (i.e. int-start date, int-end date).
Now... what are your problems with Table Inheritance? This look a classic example.
The base class/table would be Recyclable product with a modicum of common attributes...
is this the problem? Can't find anything "in common", apart from the name?
What about the unit of measurement? I.e. a reference to another table detailing Kg., Gallons, cubic foot, etc.?
Maybe an "Hazardous" flag?
From then you can specialize "Plastic" adding a field for "Recycle method". Which points to another table listing the available alternatives.
"Bin Size" would be a unit of measurement? Should this be a basic "class" attribute (i.e. all plastics are measured by weight) or should it depend on different things? (i.e plastic type? collecting point type?)
It's perfectly ok non to have much in the base table apart from a numeric ID and some descriptive fields, anyway.
难道你不想要这样的东西：
Month|Recycling_Type|Recycling_IDWeek1，Plastic,2342第 2 周、塑料、2343Week2,Metal,4然后为每种回收类型建立一个单独的表：
PlasticID，Bin 大小2342，52343、6等
Don't you want something like:
Month|Recycling_Type|Recycling_IDWeek1,Plastic,2342Week2,Plastic,2343Week2,Metal,4Then have a seperate Table for each Recycling type:
PlasticID,Bin Size2342,52343,6etc