Another important consideration when modeling is the sharing of key attributes. When entities are involved in multiple relationships, the key attribute fields may be repeated in an entity. It is important to know whether the attribute keys are repeated or shared.
Consider the following Bank example.
|
- Bank is known by its Bank Code. - Bank has a name. - Etc... |
|
- Account is known by its parent's Bank Code. - Account is known by Account Number. - Account has an amount. - Account has a name. - Etc... |
Now imagine that you need to track transactions involving a specific Bank and Account. One transaction is called a Transfer which involves moving money from one Account to another.
So if keys are not shared, the model will be:
|
- Transfer is known by its Transfer ID Number. - Transfer has a join attribute Debit Bank Code. - Transfer has a join attribute Debit Account Number. - Transfer has a join attribute Credit Bank Code. - Transfer has a join attribute Credit Account Number. - Transfer has an amount. - Transfer has a date. - etc. |
In this situation, transfers can be made between two different Banks (or within the same Bank). The Debit Bank Code and Credit Bank Code can be different. The parent key of Bank Code is NOT shared. If the business rules specify that transfers cannot be made between banks, then the Bank Code must be the same for each account. The parent key of Bank Code should be shared. If the parent key is shared, then the model will be:
|
- Transfer is known by its Transfer ID Number. - Transfer has a join attribute Bank Code. - Transfer has a join attribute Debit Account Number. - Transfer has a join attribute Credit Account Number. - Transfer has an amount. - Transfer has a date. - Etc. |
Note that two relationships with the same entity can never share all their keys. In our example, this means that Transfer could not share both Bank Code and Account Number in both its relationships. This would have no meaning.
