>为每个付款类型设置一个表,每个支付类型有几个常用的列. (现设计)
>使用带有公共列的中央表来协调所有付款(统一支付ID,无论类型如何),并标识另一个具有专门针对该付款类型列的表和行ID.
>为所有付款类型提供一张表,并将不用于任何给定类型的列空白.
>使用中心表的想法,但将专门的列存储在键/值表中.
我的目标是：尽可能不自我缓慢,自我记录,并在保持其他目标的同时最大限度地提高灵活性.
我不喜欢1,因为每个表中的重复列.它反映了支持类型类继承基类,为所有支付类型提供功能… ORM反向？
我最倾向于2,因为它像现在的设计一样是“安全的”和自我记录.但是,与1一样,要添加新的支付类型,我需要添加一个新表.
我不喜欢3,因为它的“浪费的空间”,并不立即清楚哪些列用于哪些支付类型.文档可以减轻这一点的痛苦,但是我公司的内部工具没有有效的存储/查找技术文档的方法.
我给出的4个理由是,它将减轻在添加新的付款方式时需要更改数据库,但是由于缺乏明确性,它比3更糟糕.目前,改变数据库并不是一个问题,但如果我们决定开始让客户将自己的数据库放在一起,那么这可能会成为一个后勤噩梦.
所以,当然我有我的偏见.有人有什么更好的想法吗？你觉得最适合哪种设计？我应该做出什么准则？
解决方法
比尔·卡尔温(Bill Karwin)所接受的答案涉及到通常被称为实体属性值(EVA)的关键/价值表的具体论点,
.. Although many people seem to favor
EAV,I don’t. It seems like the most
flexible solution,and therefore the
best. However,keep in mind the adage
07001. Here are some of the
disadvantages of EAV:
- No way to make a column mandatory (equivalent of
NOT NULL).- No way to use sql data types to validate entries.
- No way to ensure that attribute names are spelled consistently.
- No way to put a foreign key on the values of any given attribute,e.g.
for a lookup table.- Fetching results in a conventional tabular layout is complex and
expensive,because to get attributes
from multiple rows you need to do
JOINfor each attribute.The degree of flexibility EAV gives
you requires sacrifices in other
areas,probably making your code as
complex (or worse) than it would have
been to solve the original problem in
a more conventional way.And in most cases,it’s an unnecessary
to have that degree of flexibility.
In the OP’s question about product
types,it’s much simpler to create a
table per product type for
product-specific attributes,so you
have some consistent structure
enforced at least for entries of the
same product type.I’d use EAV only if every row must be permitted to potentially have a distinct set of attributes. When you have a finite set of product types,EAV is overkill. Class Table Inheritance would be my first choice.
