循环数据库关系。 好、坏、例外？
我已经推迟了一段时间开发我的应用程序的这一部分，纯粹是因为我想以循环的方式做到这一点，但从我记得我的讲师在学校告诉我的事情来看，我感觉这是一个坏主意。
我有一个订单系统的设计，忽略与此示例无关的所有内容，我留下：
- 信用卡
- 客户
- 订单
我想要这样，
- 客户可以拥有信用卡（0-n）
- 客户有订单（1 -n)
- 订单有一个客户(1-1)
- 订单有一张信用卡(1-1)
- 信用卡可以有一个客户(1-1)（唯一的 ID，因此我们可以忽略抄送号码的唯一性，丈夫/妻子可以共享抄送实例等）
基本上最后一部分是问题出现的地方，有时信用卡被拒绝，他们希望使用不同的信用卡，这需要更新他们的“当前”卡，但这只能更改当前使用的卡该订单，而不是客户磁盘上可能有的其他订单。
这实际上在三个桌子之间创建了一个圆形设计。
可能的解决方案： 抄送
创建循环设计，提供参考：
- 订单参考、
- 客户参考抄送
- 客户参考订单
或
- 客户参考抄送
- 客户参考订单
- 创建引用所有三个表 ID 的新表，并在订单上设置唯一的 ID，以便只有一个cc 可能随时符合该顺序
本质上两者都采用相同的设计，但翻译不同，我此时最喜欢后一个选项，因为它看起来不那么循环并且更中心。 （如果这甚至有道理）
我的问题是，
- 如果有的话，每个的优点和缺点是什么？
- 循环关系/依赖关系的陷阱是什么？
- 这是规则的有效例外吗？
- 我有什么理由应该选择前者而不是后者吗？
谢谢，如果您需要澄清/解释任何内容，请告诉我。
--更新/编辑--
我注意到我所说的要求中有一个错误。 当试图简化事情时基本上失败了。 还有另一个用于付款的表，它添加了另一层。 问题是，订单可以进行多次付款，并且可以使用不同的信用卡。 （如果您确实想了解其他付款方式）。
在这里声明这一点是因为我认为根本问题仍然是相同的，这只真正增加了另一层复杂性。
I have been putting off developing this part of my app for sometime purely because I want to do this in a circular way but get the feeling its a bad idea from what I remember my lecturers telling me back in school.
I have a design for an order system, ignoring the everything that doesn't pertain to this example I'm left with:
- CreditCard
- Customer
- Order
I want it so that,
- Customers can have credit cards (0-n)
- Customers have orders (1-n)
- Orders have one customer(1-1)
- Orders have one credit card(1-1)
- Credit cards can have one customer(1-1) (unique ids so we can ignore uniqueness of cc number, husband/wife may share cc instances ect)
Basically the last part is where the issue shows up, sometimes credit cards are declined and they wish to use a different one, this needs to update which their 'current' card is but this can only change the current card used for that order, not the other orders the customer may have on disk.
Effectively this creates a circular design between the three tables.
Possible solutions:
Either
Create the circular design, give references:
- cc ref to order,
- customer ref to cc
- customer ref to order
or
- customer ref to cc
- customer ref to order
- create new table that references all three table ids and put unique on the order so that only one cc may be current to that order at any time
Essentially both model the same design but translate differently, I am liking the latter option best at this point in time because it seems less circular and more central. (If that even makes sense)
My questions are,
- What if any are the pros and cons of each?
- What is the pitfalls of circular relationships/dependancies?
- Is this a valid exception to the rule?
- Is there any reason I should pick the former over the latter?
Thanks and let me know if there is anything you need clarified/explained.
--Update/Edit--
I have noticed an error in the requirements I stated. Basically dropped the ball when trying to simplify things for SO. There is another table there for Payments which adds another layer. The catch, Orders can have multiple payments, with the possibility of using different credit cards. (if you really want to know even other forms of payment).
Stating this here because I think the underlying issue is still the same and this only really adds another layer of complexity.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
一个客户可以关联 0 张或更多张信用卡，但这种关联是动态的 - 它可以来来去去。 正如您所指出的，一张信用卡可以与多个客户关联。 因此，这最终成为一个 n:m 表，可能带有一个表示“活动”的标志列。
一个订单与 0 或 1 张信用卡有静态关系，并且在销售完成后，无论 cc 与客户之间的关系发生什么变化，您都不能乱动 cc 值。 订单表应独立存储销售时有关 cc 的所有相关信息。 没有理由将销售与任何其他表中的任何其他信用卡列相关联（这可能会改变 - 但不会影响销售）。
A customer can have 0 or more credit cards associated, but the association is dynamic - it can come and go. And as you point out a credit card can be associated with more than one customer. So this ends up being an n:m table, maybe with a flag column for "active".
An order has a static relationship to 0 or 1 credit card, and after a sale is complete, you can't mess with the cc value, no matter what happens to the relationship between the cc and the customer. The order table should independently store all the associated info about the cc at the time of the sale. There's no reason to associate the sale with any other credit card column in any other table (which might change - but it wouldn't affect the sale).
我认为问题出在骑士团的建模上。 一个订单不应与一张信用卡相关联，而是一个订单应能够与多张信用卡关联，且任何时候只有一张信用卡处于活动状态。 本质上，订单和信用是多对多的。 为了在数据库中对此进行建模，您需要引入一个关联表，例如 PaymentHistory。 现在，当订单需要新信用卡时，您只需创建一张新信用卡，并将其与订单关联，并将关联的 PaymentHistory 标记为活动状态。
I think the problem is with the modeling of the Order. Instead of one Order has one credit card, an order should be able to be associated with more than one credit card of which only one is active at any time. Essentially, Order and Credit is many-to-many. In order to model this in DB, you need to introduce an association table, let's say PaymentHistory. Now when an order requires a new credit card, you can simply create a new credit card, and associate it with the order and mark the associating PaymentHistory as active.
唔？
一位客户拥有多张信用卡，但只有一张当前可用的信用卡。 订单有一张指定的卡。 顾客买东西的时候，会先尝试他的默认卡，否则可能会换主卡？
我在这里没有看到循环引用； 当用户的信用卡发生变化时，他的订单保持不变。 您的表格最终将如下：
编辑： 哎呀，忘记了一个字段，谢谢。
Hmm?
A customer has several credit cards, but only a current one. An order has a single assigned card. When a customer puchases something, his default card is tried first, otherwise, he may change his main card?
I see no circular references here; when a user's credit card changes, his orders' stay the same. Your tables would end up as:
Edit: Oops, forgot a field, thanks.
无论您的数据出于何种原因具有循环关系，如果您“忘记”声明其中之一以便您的表具有批量加载顺序，您会更高兴。
当你最意想不到的时候，这会派上用场。
No matter the reason your data has circular relationships, you'll be a lot happier if you "forget" to declare one of them so that your tables have a bulk-load order.
That comes in handy when you least expect it.
虽然已经过去一年了，但有一些观点值得提出。
注意：对于在线非帐户流程：客户可以更好地定义为买方，并且可能还有另一种类型的客户 - 受益人/接收人。 您可以为其他人购买/购买机票和鲜花等，因此这两个角色需要明确区分，因为它们涉及不同的业务流程（一个用于付款，另一个用于发送货物）。
如果这是一个非帐户流程，那么您不应该保留信用卡详细信息。 这是一个安全风险——保留这些信息会让买家面临风险。 信用卡是实时处理的，然后信息就应该被丢弃。
帐户客户：唯一的例外是有人开设了帐户并提供了信用卡信息以供后续购买使用。 在这种情况下，信用卡信息的更改将在交易之外进行——作为账户管理流程的一部分。
要点是确保在开始建模和编码之前完全理解业务流程。
This is a year old but there's some points worth making.
NB For on-line NON-ACCOUNT processes: The Customer would be better defined as Buyer and there would also probably be another type of customer - the Beneficiary/Recipient. You can buy/purchase airline tickets and flowers etc. for other people so these two roles need to be clearly separated as they involve different business processes (one to pay and the other to be sent the goods).
If it is a non-account process then you shouldn't be retaining credit card details. It's a security risk - and you're putting the buyer at risk by keeping this information. Credit cards are processed in real-time and then the information should be thrown away.
ACCOUNT CUSTOMERS: The only exception would be when someone has opened an account and provided their credit card information for use in subsequent purchases. In such a case changes to the credit card information would take place outside of the transaction - as part of the Account Management process.
The main point is to make sure that you fully understand the business processes before you start modelling and coding.