Is it OK to have parts of a combined PK deriveable from a non PK element?

Question

Is it OK to have parts of a combined PK deriveable from a non PK element?

brazem

Valued Member

Points: 67
More actions
August 24, 2004 at 10:29 am

#86501

I'm having a dilema in identifying a proper modeling strategy for investment accounts. A model that I'm proposing seems like the right solution, yet it seems to violate normal forms (parts of a combined PK can be derived from a non key element). Can anyone provide some thoughts? See description of problem below.
An account has a taxtype. Taxtypes are:
+-----------------------------------------------+
|Code   Description                             |
|------ ----------------------------------------|
|0      CMAIP                                   |
|1      CMARSP                                  |
|2      CMARIF                                  |
|3      Self Dir. RRSP - Other                  |
|4      DO NOT USE; Self Dir. RRSP - MDM        |
|5      Self Dir. RRIF - Other                  |
|7      Strategic Alliances                     |
|8      IPP                                     |
|9      CMAP                                    |
|A      National Life RRIF                      |
|B      SDIP                                    |
|C      SDRSP                                   |
|D      SDRIF                                   |
+-----------------------------------------------+
Taxtype is used in various places to determine various tax implications on how an account should be treated.
An account has an account type.
+-----------------------------------------------+
|Code   Description                             |
|------ ----------------------------------------|
|0      Individual                              |
|1      Spousal                                 |
|2      Joint                 - single signature|
|3      Joint               - multiple signature|
|4      MD Education Trust                      |
|5      Prof Corp                               |
|6      Locked-in                               |
|7      Association                             |
|8      Estate                                  |
|9      EBP                                     |
|A      Family Trust           - Non-recognized |
|B      Family Trust                    - Other |
|E      MD Registered Education Savings Plan    |
|I      Insurance                               |
+-----------------------------------------------+
AccountType is used in various places to determine rules for treating purchases / redemptions (allowing or disallowing, based on the accounttype).
An account has a statement description. This description is derived from the account's taxtype AND accounttype. This is the caveat: An account's taxtype is also deriveable from it's description, BUT an Account's AccountType IS NOT.
+------------------------------------------------------------+
|Tax Acct                                                   |
|Type Type vdesc                                             |
|---- ---- --------------------------------------------------|
|0    0    CMA Investment Plan                               |
|0    1    CMA Investment Plan                               |
|0    2    Joint CMA Investment Plan                         |
|0    3    Joint CMA Investment Plan                         |
|0    4    MD Family Trust                                   |
|0    5    Professional Corp. Investment Plan                |
|0    7    Association CMA Investment Plan                   |
|0    8    Estate CMA Investment Plan                        |
|0    9    Employee Benefit Investment Plan                  |
|0    A    MD Family Trust Investment Plan                   |
|0    B    MD Family Trust Investment Plan                   |
|0    E    MD Registered Education Savings Plan              |
|0    i    CMA Investment Plan                               |
|1    0    CMARSP                                            |
|1    1    Spousal CMARSP                                    |
|1    6    Locked-in CMARSP                                  |
+------------------------------------------------------------+
The model I end up with is the one:
After a long winded question, here is my dilema: In the model shown, a portion of my combined primary key can be derived from a non-key attribute (In table AccountStatementDescriptionUsage, AccountTaxTypeCode can be derived from StatementDescriptionId). This seems to me like something that violates normal forms.
Is this a good approach or not?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Henk Kok SSC Eights! Points: 849 More actions · Answer 1

Hi Brazem,

I want to have a look at your model and, hopefully, give you an answer to your question, but the image isn't visible. I noticed that the url for the image is file:///c:/temp/model1.jpg, which explains why I can't see it.

the King!

If you see a picture of Elvis, here's the way to add a picture:

press the "Insert picture" button:
select the picture
add an alternate text, if you like
press ok

If you're seeing two small white boxes with a red cross in it, there's something we're both missing . Maybe if there's to much time between inserting the picture and hitting the "Post Reply" button?

Cheers,

Henk

jfmccabe SSCrazy Points: 2547 More actions · Answer 2

Without seeing the model, this is only an initial stab.

An account has a statement type. You don't need either tax type or account type since these can be derived from the statement type.

You would then create a view to pick up both the tax type and the account type.

Technically, an account doesn't have a tax type OR an account type, it DOES have a statement type. A statement DOES have a tax type and an account description. The statement type describes both how you prepare the statement and the handling of any tax returns (which impacts on the statement format).

Hope this helps.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 3

Actually, jfmccaeb is correct. But, I would also add that you should have a structure something like this for you statement type table.

+------------------------------------------------------------------------+

|Statement Tax Acct |

|ID Type Type vdesc |

|--------- ---- ---- --------------------------------------------------|

|1 0 0 CMA Investment Plan |

|2 0 1 CMA Investment Plan |

|3 0 2 Joint CMA Investment Plan |

|4 0 3 Joint CMA Investment Plan |

|5 0 4 MD Family Trust |

|6 0 5 Professional Corp. Investment Plan |

|7 0 7 Association CMA Investment Plan |

|8 0 8 Estate CMA Investment Plan |

|9 0 9 Employee Benefit Investment Plan |

|10 0 A MD Family Trust Investment Plan |

|11 0 B MD Family Trust Investment Plan |

|12 0 E MD Registered Education Savings Plan |

|13 0 i CMA Investment Plan |

|14 1 0 CMARSP |

|15 1 1 Spousal CMARSP |

|16 1 6 Locked-in CMARSP |

+------------------------------------------------------------------------+

I do not recommend the use of a compound primary key for any reason. Use unique indexes to enforce the uniqueness of business rules that a compound key really is.

brazem Valued Member Points: 67 More actions · Answer 4

Ok, I've made the image available to everyone.

Also, I've added a small note to the initial problem for jfmccaeb and stewart: Although taxtype can be derived from a statement description, account type cannot.

So the alternative suggested by both does not meet all requirements.

Thoughts?

jfmccabe SSCrazy Points: 2547 More actions · Answer 5

Same basic principle

Middle table will have fields:

Table - AcccountStatementUsageDescription

AcccountStatementUsageDescriptionID
AccountTypeID
TaxTypeID
StatementDescriptionID

And the Account table will simply have

AccountNumber
AcccountStatementUsageDescriptionID

brazem Valued Member Points: 67 More actions · Answer 6

I see says the blind man. To keep the above solution normalized, I would change it slightly as follows (since TaxType can be derieved from Statementdescription):

Table - AcccountStatementUsageDescription
AcccountStatementUsageDescriptionID
AccountTypeID
StatementDescriptionID

Table - Account

AccountNumber

AcccountStatementUsageDescriptionID

Table - AccountDescription

StatementDescriptionId

TaxTypeID

This is one of 2 or 3 theoretical solutions, which I always like to get to first. Remains to be seen how this will be implemented on our current system, where AccountType and TaxType are FK attributes of our Account table, and where we have limited support for SQL (our system is barely SQL92 compliant). But I'm cool from here!

Thanks for your thoughts