February 11, 2015 at 10:32 am
I'm curious about the correct way to model a particular scenario. I don't like how the database I'm working with has modeled it, but I can't change it. Regardless, I started to wonder how I would model it correctly, but I'm not sure I see an ideal solution.
I really can't do anything useful with this DDL (no power), but if you're bored and enjoy a tricky data modeling scenario, I'd love to hear how you would solve this. I would provide DDL, but that's exactly what I'm not sure how to create (one potential example is below). So I'll describe the situation.
There are two entities involved: account and account person. The account is a financial account uniquely identified by an account number (fixed-length 8-character string). The account person is someone who is a party to the account. The account and account person both have many additional attributes and dependent entities not detailed here. The account person can have one of three relationships to the account: primary (every account must have exactly one primary party), joint (an account may have 0 or 1 joint parties), or authorized (zero to infinite). The account person is identified by the account number and whether that person is primary, joint or authorized (for authorized persons, a sequence number must also be used to guarantee uniqueness). While it may be true that TIN (tax id number) should uniquely identify a person, in practice this number may be missing or incorrect and, per business requirements, its absence should not stop account creation.
It's possible in the real world for a person to have multiple accounts, but in practice those records do not overlap. The registration (name, address, phone, etc) for each account is separate. Changing a person's address on all accounts is a front-end coordination effort, not a single database update. (There are legal/regulatory reasons for this separation.)
The DDL below is one attempt. Remember that account and account_person have many additional attributes not shown here. This schema does not enforce:
- Exactly one primary person (code I)
- No more than one joint person (code J)
This schema also requires the client code to generate an increasing sequence number, by account, for each account person. I used a check constraint rather than a reference table for brevity.
CREATE TABLE account (
account_nbr CHAR(8) NOT NULL PRIMARY KEY);
CREATE TABLE account_person (
account_nbr CHAR(8) NOT NULL REFERENCES account(account_nbr),
account_person_seq_nbr INT NOT NULL,
relationship_cd CHAR(1) NOT NULL CHECK (relationship_cd IN ('I', 'J', 'A')),
PRIMARY KEY (account_nbr, account_person_seq_nbr));
Other schemas that I tried ended up with chicken-and-egg scenarios. Account could have keys to account_person for primary and joint, but account_person depends on account. There certainly isn't any way in that scenario to make the primary account holder required. I also tried making a person table that was independent of account, but that sort of independent entity doesn't actually exist because all account_person attributes are dependent on the account.
February 11, 2015 at 3:42 pm
What you've got is pretty close to what I was thinking, and it resembles a Party Data model, with 4 key tables, and you should get a reasonable normalisation of the data.
So 4 tables would be Account, AccountParty, PartyRole and Party, samples below.
CREATE TABLE AccountPartyRole
(
AccountNumber --FK To account table
,PartyCode --FK to Party
,PartyRoleType --FK PartyRoleType
)
With a Unique Clustered index on the three fields.
Party Role would be
CREATE TABLE PartyRoleType
(
RoleType --PK
RoleDescription
)
With a PK Clustered index on PartyRole and FK to AccountParty
Then finally Party would be
CREATE TABLE Party
(
PartyCode --PK
,PartyName
,PartyAttributes
)
PK Clustered index on PartyCode and FK relationship to the AccountParty
Ultimately the business requirements would need to see this tweaked a little
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply