规范化 SQL 数据库
我正在为一个项目创建一个数据库，但我对规范化如何应用于我的模式有点困惑。每次为客户批准贷款时，他们有两种选择：支票或电子转帐，所以我想知道贷款是支票还是电子转帐。
这是我的 3 个表：
贷款
id_loan (PK)
product
amount
status
支票
id_check (PK)
id_customer
amount
电子转帐
id_eft (PK)
id_customer
amount
然后我创建了第四个表来建立贷款和资金处置之间的关系。
Disposal
id_payment (PK)
id_loan (FK loans)
id_disposal (FK checks or EFT)
disposal_type
在此表中，我存储贷款是否与支票或 EFT 相关，disposal_type 字段是一个 varchar，有两个可能的值“check”或“EFT”。 id_disposal 字段充当两个表的外键。
问题是我认为我的数据库没有用这种结构标准化，对吗？解决这个问题的最佳方法是什么？
I'm creating a database for a project and I'm a little confused about how normalization applies to my schema. Everytime a loan is aproved for a customer, they have 2 options a check or an EFT, so I want to know wheter the loan was a check or EFT.
This are my 3 tables:
Loans
id_loan (PK)
product
amount
status
Checks
id_check (PK)
id_customer
amount
EFT
id_eft (PK)
id_customer
amount
Then I created a 4th table to establish a relationship between loans and money disposal.
Disposal
id_payment (PK)
id_loan (FK loans)
id_disposal (FK checks or EFT)
disposal_type
In this table I store whether the loan is related to a check or an EFT, disposal_type field is a varchar with two possible values "check" or "EFT". id_disposal field acts as a foreign key for two tables.
The problem is that I think my database isn't normalized with this structure, am I right? What would be the best way to solve this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你需要类似附件的东西。请注意，customer_loans 表有点无关紧要且矫枉过正，但如果有任何与客户和贷款相关的列，而不是客户的贷款付款，那么它就会去那里。
You need something like the attached. Note that the customer_loans table is kind of extraneous and overkill, but if there's any columns that relate to the customer and the loan, and not the customer's loan payments, that's where it would go.
在对象世界中，您可以使用继承来实现此目的。将有一个基类型 Disposal，CheckDisposal 和 EftDisposal 将派生自该基类型。现代 O/RM 支持多种将其映射到关系结构的技术。
TablePerHierarchy 将所有记录放入一个带有鉴别器列的表中，以识别特定记录保存并映射到的类型。优点是获取记录所需的连接次数较少。缺点是它需要应用程序逻辑来强制数据完整性。
TablePerType 将记录映射到不同的表中，并通过 fk 关系返回到基表。当然，这需要更多的联接（特别是对于深或宽的层次结构），但可以在数据库中强制执行数据完整性。
In the object world, you'd use inheritance for this. There would be a base type Disposal which CheckDisposal and EftDisposal would derive from. Modern O/RMs support several techniques for mapping this to a relational structure.
TablePerHierarchy puts all of the records into a single table with a discriminator column to identify what type a specific record holds and maps to. The advantage is that it requires fewer joins to get a record. Disadvantage is that it requires app logic to enforce data integrity.
TablePerType maps records into different tables with a fk relationship back to the base table. Of course this requires more joins (especially for deep or wide hierarchies) but data integrity can be enforced in the DB.