我是否应该将“贷款”、“采购”和“销售”表非规范化为一张表？
根据我在下面提供的信息，您能否就将单独的表非规范化为一个包含不同类型合约的表是否是个好主意发表意见？.. 优点/缺点是什么？.. 有没有人尝试过这样做之前？.. 银行系统使用 CIF（客户信息文件）[主]，客户可能拥有不同类型的账户、CD、抵押贷款等，并使用交易代码[类型]，但他们是否将它们存储在一张表中？
我有单独的贷款、购买和贷款表。销售交易。每个表中的行都通过以下方式连接到相应的客户：
customer.pk_id SERIAL = loan.fk_id INTEGER;
= purchase.fk_id INTEGER;
= sale.fk_id INTEGER;
由于这些表之间有很多共同属性，这些属性都围绕相同的商品：典当、购买和出售，因此我尝试将它们合并到一个表中称为“合同”并添加以下列：
Contracts.Type char(1) {L=Loan, P=Purchase, S=Sale}
场景：
客户最初典当商品，支付几笔利息，然后决定将商品出售给当铺，当铺随后将商品放入库存并最终将其出售给另一位客户。
我设计了一个通用表，例如：
Contracts.Capital DECIMAL(7,2)
在贷款合同中它保存典当本金，在购买中保存购买价格，在销售中保存销售价格。
这个设计是个好主意还是我应该将它们分开？
Based on the information I have provided below, can you give me your opinion on whether its a good idea to denormalize separate tables into one table which holds different types of contracts?.. What are the pro's/con's?.. Has anyone attempted this before?.. Banking systems use a CIF (Customer Information File) [master] where customers may have different types of accounts, CD's, mortgages, etc. and use transaction codes[types] but do they store them in one table?
I have separate tables for Loans, Purchases & Sales transactions. Rows from each of these tables are joined to their corresponding customer in the following manner:
customer.pk_id SERIAL = loan.fk_id INTEGER;
= purchase.fk_id INTEGER;
= sale.fk_id INTEGER;
Since there are so many common properties among these tables, which revolves around the same merchandise being: pawned, bought and sold, I experimented by consolidating them into one table called "Contracts" and added the following column:
Contracts.Type char(1) {L=Loan, P=Purchase, S=Sale}
Scenario:
A customer initially pawns merchandise, makes a couple of interest payments, then decides he wants to sell the merchandise to the pawnshop, who then places merchandise in Inventory and eventually sells it to another customer.
I designed a generic table where for example:
Contracts.Capital DECIMAL(7,2)
in a loan contract it holds the pawn principal amount, in a purchase holds the purchase price, in a sale holds sale price.
Is this design a good idea or should I keep them separate?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的表第二个设计更好，并且是“标准化”的。
您的第一个设计是非规范化的！
您基本上遵循称为“子类型/超类型”的数据库设计/建模模式
用于处理诸如事务之类的事务，其中存在大量通用数据和特定于每种事务类型的一些数据。
有两种公认的建模方法。如果变量数据很小，那么您可以将所有内容保存在一个表中，并将事务类型特定的属性保存在“可为空”列中。 （这本质上是你的情况，你做了正确的事情！）。
另一种情况是，“不常见”数据根据交易类型而有很大差异，在这种情况下，您有一个包含所有“常见”属性的表，以及每个类型的一个表，其中包含该“类型”的“不常见”属性。
然而，“贷款”、“购买”和“销售”作为交易是有效的。我认为库存是一个不同的实体，应该有自己的表格。本质上，“贷款”将添加到库存交易中，“购买”将库存状态更改为“可销售”，“销售”将从库存中删除商品（或将状态更改为已售出）。一旦一个项目被添加到库存中，只有它的状态应该改变（它仍然是一个小部件，小提琴或其他）。
Your table second design is better, and, is "normalised".
You first design was the denormalised one!
You are basiclly following a database design/modelling pattern known as "subtype/supertype"
for dealing with things like transactions where there is a lot of common data and some data specific to each tranasaction type.
There are two accepted ways to model this. If the the variable data is minimal you hold everthing in a single table with the transaction type specfic attributes held in "nullable" columns. (Which is essentially your case and you have done the correct thing!).
The other case is where the "uncommon" data varies wildly depending on transaction type in which case you have a table which holds all the "common" attributes, and a table for each type which holds the "uncommon" attributes for that "type".
However while "LOAN", "PURCHASE" and "SALE" are valid as transactions. I think Inventory is a different entity and should have a table on its own. Essentially "LOAN" wll add to inventory transaction, "PURCHASE" wll change of inventory status to Saleable and "SALE" wll remove the item from inventory (or change status to sold). Once an item is added to inventory only its status should change (its still a widget, violin or whatever).
我认为它不是非规范化的。我没有看到重复的组；所有属性都依赖于唯一的主键。对我来说听起来是一个很好的设计。
这里有问题吗？您只是在寻找是否可以接受的确认吗？
假设一下，如果压倒性的共识是您应该为每种类型恢复到单独的表，您会怎么做？您会忽略自己经验中的证据（更好的性能、更简单的编程）并遵循 Stackoverflow.com 的建议吗？
I would argue that it's not denormalized. I see no repeating groups; all attributes depending on a unique primary key. Sounds like a good design to me.
Is there a question here? Are you just looking for confirmation that it's acceptable?
Hypothetically, what would you do if the overwhelming consensus was that you should revert back to separate tables for each type? Would you ignore the evidence from your own experience (better performance, simpler programming) and follow the advice of Stackoverflow.com?