Customer Database Question

[floridarandy]

I'm creating a database for my financial planning clients. The primary key
will be the client ID. I've begun with my Client as my first table which
includes all the contact information about the client and an alternate family
member. Related tables of information will work off the same client ID
primary key and will include information about:
1. Non-retirement investment accounts
2. Retirement accounts
3. Life Insurance
5. Disablity Insurance

Each of these tables, and other similiar tables, will be "children" of the
"parent" Client if I understand relationships correctly.

I'm having an issue with how best to deal with mutiples of things like
non-retirement accounts, retirement accounts, life policies, etc. Each
account/policy will have a unique contract number and multiples fields of
information about the account/policy. If I simply repeat the fields for each
different account/policy in the same table the table for, say non-retirement
accounts, could be quite large if the client had, say, 6 different
non-retirement accounts. Yet, it seems unrealistic to have a separate table
for each account/policy, even tho the number of fields per table would be
smaller.

I'd appreciate suggestions as this is my first attempt. Also, please
correct me if the parent/child relationship I envision isn't correct.

Thanks in advance.

 

[KARL DEWEY]

Yet, it seems unrealistic to have a separate table for each account/policy,
You have separate records for each. You set a one-to-many relationship and
select Referential Integerity and Cascade Updates.

Relational databases have a hiearchy like this --
Database
Table
Records
Fields

Also a single table for "Non-retirement investment accounts" and "Retirement
accounts" using an additional field to define the difference.
You probably could do the same for all insurance. Just have field to define
type.

If you are going to track payments, correspondance, etc. that is a little
different as you can client, policy, or billing/payment at any of these
levels. One way would be to have correspondance table with table to have
child records for each applicable level - ClientID, PolicyID, PayID, and or
BillID.

Use form/subform for parent/child with Master/Child links using the higher
xyzID.