我应该将“贷款"，“购买"和“销售"表归一化为一个表吗? [英] Should I denormalize Loans, Purchases and Sales tables into one table?

问题描述

根据我在下面提供的信息，请问我是否对将单独的表格非规范化为一个包含不同类型合同的表格是否是个好主意?银行系统使用CIF(客户信息文件)[母版]，其中客户可能具有不同类型的帐户，CD，抵押等，并使用交易代码[类型]，但它们是否将它们存储在一张表中?

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}

场景:

一个客户最初对商品进行典当，支付了几笔利息，然后决定要将该商品出售给当铺，当铺随后将商品放入库存，并最终将其出售给另一位客户.

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?

推荐答案

表的第二个设计更好，并且被规范化".

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.

有两种方法可以对此进行建模.如果变量数据很小，则将所有内容都保存在单个表中，并且"nullable"列中保存有事务类型特定属性. (从本质上讲，这是您的情况，您做对了！).

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).

这篇关于我应该将“贷款"，“购买"和“销售"表归一化为一个表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！