Many-to-Many database help

[ejstefl]

Hello all,

I am created a database to track class actions. Basically, we file for an account on a cetain class action. Then, our claim is either paid (we don't know the amoutn until we recieve it, and may receive multiple payments for one claim) or rejected.

I want to keep track of which accounts we filed for, which claims were rejected, and which claims were paid.

I think my structure is pretty sound. The only area I question is my tblPayments. I'm not too familiar with many-to-many relationships, so can someone please either validate what I have, or tell me what I should change? As always, thanks in advance for the help!!

Currently, I have the following tables:

tblAccounts
AccountNumber - pk
AccountName
TIN

tblCUSIP (information about the security involved in the class action)
CUSIP - pk
SecurityName

tblClass
ActionID - pk
CUSIP
FilingDeadline
ProcessorID
ClassPeriodBegin
ClassPeriodEnd

tblProcessor
ProcessorID - pk
ProcessorFirstName
ProcessorLastName

tblAccountsClass
AccountNumber
ActionID
Rejected?
RejectedID

tblRejection
RejectionID - pk
RejectionName

tblPayments
PaymentID - pk
DollarAmt
AccountNumber
ActionID
DatePosted
BatchNumber
Comments

 

[GordonRS]

The one to many relationship is perhaps best understood in a real world example. Take a purchasing system - you would have one table for the header row which typically holds details of the customer number, order date etc. Another table would hold the individual items being ordered. The link between the two tables would be the order number. The relationship on that key field would therefore be described as one order header row to many order line rows.
So, in your example there is a relationship between tblAccounts and tblPayments on AccountNumber. As an account may have more than one payment against it the relationship is therefore one account row to many payment rows. In the case of the only other common key, ActionID, this makes a relationship between the tblClass and tblPayment. It depends if a class can have more than one payment made against it - if it can then this is again a one class row to many payment rows. If a class can only have one payment then the relationship is a one-to-one.

Hope this helps.

Gordon

 

[ejstefl]

Gordon,

Thanks for your quick reply. An account can be involved in more than one action, and each account/action combo can have more than one payment... so with that info, does my structure look sound?

 

[GordonRS]

If I understand the database structure correctly, then a row in tblClass relates to only one row in tblAccounts i.e. one class is attached to only one account although an account may have many classes. I am also assuming that a row in tblClass can only have one row in tblAccountsClass.
If so, perhaps you might want to consider adding these to tblClass:
AccountNumber, RejectionID and RejectionName
which then makes tblAccountsClass spurious and it can be removed.
Also, you would then no longer need AccountNumber in tblPayments as there is a link back to tblAccounts through tblClass.

Hope this helps,

Gordon

 

[ejstefl]

Well, one class will have many accounts... So I don't think that would work...

 

[Pat Hartman]

I don't understand tblClass and its relationship to tblCUSIP.
I don't understand tblAccountsClass. It looks like a junction table that connects tblAccounts and tblClass in a many-to-many relationship. This table has no primary key defined. What is the pk?
Is tblPayments supposed to relate to tblAccountsClass?
You have a ? in one of your field names. Remove it.

I wouldn't use CUSIP as a primary or foreign key. This number is not under your control and since it is external, I would relegate it to the status of attribute rather than pk. You can add a unique index on it to prevent duplicates since it must be unique.

Add fk info to the structure so we can see which fields are fk's and which table they relate to.

 

[ejstefl]

I don't understand tblClass and its relationship to tblCUSIP.

tblClass stores information about a class action lawsuit. tblCUSIP stores information about a security (like a stock, bond, etc.). Each class action lawsuit will involve one security.

I don't understand tblAccountsClass. It looks like a junction table that connects tblAccounts and tblClass in a many-to-many relationship. This table has no primary key defined. What is the pk?

tblAccountsClass is supposed to show which accounts are involved in which classes. I will add a pk.

Is tblPayments supposed to relate to tblAccountsClass?

I'm not exactly sure - I'd like your guidance on that. Each class action lawsuite will involve one security and multiple accounts. Each account will then either receive one or more payments, or the claim will be declined. My plan was if the claim was denied, to store that in tblAccountsClass. If I recieved one (or more) payments, I was going to store that in tblPayments.

I wouldn't use CUSIP as a primary or foreign key. This number is not under your control and since it is external, I would relegate it to the status of attribute rather than pk. You can add a unique index on it to prevent duplicates since it must be unique.

I will do this - but I am just wondering what the advantage is. CUSIP is an industry standard number that uniquely identifies a security... each security will have one, and each one will be unique.

Add fk info to the structure so we can see which fields are fk's and which table they relate to.

OK, second attempt:

tblAccounts
AccountNumber - pk
AccountName
TIN

tblSecurity (information about the security involved in the class action)
SecurityID - pk
CUSIP
SecurityName

tblClass
ActionID - pk
SecurityID - fk
ProcessorID - fk
FilingDeadline
ClassPeriodBegin
ClassPeriodEnd

tblProcessor
ProcessorID - pk
ProcessorFirstName
ProcessorLastName

tblAccountsClass
AccountsClass - pk
AccountNumber - fk
ActionID - fk
Rejected
RejectedID - fk

tblRejection
RejectionID - pk
RejectionName

tblPayments
PaymentID - pk
AccountNumber - fk
ActionID - fk
DollarAmt
DatePosted
BatchNumber
Comments

Does this make sense? Should I do something differently than what I am doing with tblPayments?

 

[Pat Hartman]

I know what a CUSIP is since I worked for a company that provided market analysis and portfolio management. My point is that it is not good practice to use as a primary key, a value that you don't control. CUSIP is your application's link to another application. I say this because I have a vague memory (over 20 years ago) of the format of CUSIP being changed causing use to convert our files. This was more trouble in the past with flat file types such as VSAM but it convinced me not to use someone else's key as my own. It is far easier to change a single CUSIP attribute than to change several primary keys.

When you make autonumber primary keys, they are normally named after the table name and suffixed with "ID". So, I would change AccountsClass to AccountsClassID. Use AccountsClassID in tblPayments as the fk rather than AccountNumber and ActionID.

Rejected seems to be redundant it tblAccountsClass since any non-null value for RejectedID would provide the same information - the suit was rejected.

Looks good

 

[ejstefl]

I know what a CUSIP is since I worked for a company that provided market analysis and portfolio management.

Sorry - wasn't trying to offend! Most people have no clue what it is, so I just wanted to make sure it was clear.

Thanks for the explanation - that makes a lot of sense to me. I have created other DB's with CUSIP as the pk - I'll definately have to avoid that in the future.

Thanks for your other comments as well! As always, it is much appreciated.