Printable View
This thread is related to the second article in the SqlCredit series: Part 2: Creating the Database, Tables, CRUD Procedures.
One requirement of the system is the ability to identify the primary cardholder. This is the person whose name, address, and phone number will be associated with the account.
There may be multiple Cardholder records associated with a single account, and we need to be able to choose one record as the primary.
The question is this, how should this be modeled?
Rob
If the requirement is simply to know which cardholder is primary, I'd go with Option 2: Add a PrimaryCardholderID column to Account. The downside of having to update the Account record after inserting (and generating a PK for) the Cardholder table is minor. The downsides of the other options are worse.
Besides, this extra update only needs to occur when the account is first created. All subsequent reads, joins, etc. will benefit from the simpler join logic, and the referential integrity is easier to ensure. Because a credit card system will presumably have many more reads than inserts on the Account table, the overall pain of the downside is insignificant.
That said, in a more real-world system, the primary cardholder requirements will probably become more complex. For example, your data model may require relationships to primary cardholders that don't make sense for non-primary cardholders. Or you may need additional columns for a primary cardholder. In either case, you'd have (in your logical data model anyway) seperate entities for PrimaryCardHolder (1:1 with Account) and SecondaryCardHolder (1:M with Account).
Then the decision is whether to have an AccountID FK in PrimaryCardHolder or PrimaryCardHolderID in Account. I know what I'd do, but I'll leave that argument for the next person. :-)
With these requirements, definitely Option 2: adding a PrimaryCardHolderID to Account. It's the only solution whose downsides don't indicate a design problem. Having to use a transaction is not a design problem, just slightly more coding. In an insert-once-select-many scenario like this, the simpler join logic will be much easier to live with and there are no RI worries.
But if the requirements become more complex and you either need new columns specific to primary card holders or you have relationships that only make sense for primary card holders, you'll want to have separate entities for PrimaryCardHolder (1:1 with Account) and SecondaryCardHolder (1:M with Account). At least in your logical model; your access patterns might warrant denormalization in your physical, but probably not.
For now, this is true: the requirement is simply to know which cardholder is primary.
In the spirit of YAGNI, we will not build the more complex relationships at this point.
I would go with option 2, with minimal amount of design complications. I believe option 1 should be avoided, not from the pluses and minuses from the design point of view, but from the application use. For example, the cardholder could be the primary cardholder for one transaction but not another. By placing a bit field as part of the cardholder table, then you are forcing that cardholder to be the primary cardholder for all transactions of which that cardholder has taken part of. As for option 3, if a plus for option 1 was minimal storage and storage is a concern, then it becomes a negative for this option. Generally, associative (bridge) tables are looked upon as a many to many relationship and may confuse others in the future as they maintain the database/application.
This discussion is continuing in this thread. Look for changes in a future update to the series' code.