Circular database relationships. Good, Bad, Exceptions?

Question

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.

Answer 1

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

Answer 2

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:

• Customer: id, Current Card
• Credit Cards: id, number, customer_id
• Order: id, Card_id, Customer_id

Edit: Oops, forgot a field, thanks.

Answer 3

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.

Answer 4

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.

Answer 5

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.