Linking tables - primary keys

[Guest]

I am setting up a client information database.

Currently, I have the information broken down into 36 logical tables. I
will list a few just to give a bigger picture:

Client information: address, city, state, zip &telephone #
Client first contact: 1st contact name, 1st contact telphone #, 1st contact
email
Client Location2: Address, city, state, zip & telphone #
Fund Company: fund company contact, contact phone, address, city, state, zip
Funds: fund company, fund name, share class, category name,


some clients will have same fund companys and same funds.

What would be the best way to assign primary keys and to link these tables?

 

[Roger Carlson]

There is no way to tell based on the information you've given. How you
create your relationships depends on your business rules. You haven't give
any.

In general, you create a one-to-many relationship by taking the primary key
of the table on the "one" side of the relationship and putting it in the
table on the "many" side. In the Many side table, this field IS NOT a
primary key (it is called a foreign key).

Let's take the following case
(this is just a guess at your business rules):

Each Client can buy one or more Funds
Each FundCompany offers one or more funds.

Which leaves the following tables:

Client Fund FundCompany
------- --------- ---------------
ClientID FundName FundCompanyID
ClientName etc... FCName
etc... etc...

In this case, you would put ClientID and FundCompanyID into Fund as foreign
keys and create your relationships.

Client Fund FundCompany
------- --------- ---------------
ClientID------| ClientID |---FundComID
ClientName |---<FundComID>--| FCName
etc... FundName etc...

Now, I have very strong doubts that your tables are designed properly.
ClientLocation2 and ClientFirstContact are too specific. If there are
multiple Contacts, there should be a Contacts table where each contact gets
a new record. This table would also get the ClientID as a foreign key.
Location would work the same.

I strongly recommend you get the book: "Database Design for Mere Mortals" by
Michael Hernandez. On my website, (www.rogersaccesslibrary.com) there are
some tutorials (http://www.rogersaccesslibrary.com/TutorialsDesign.html)
that illustrate how to use his method to design your database.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am setting up a client information database.

Currently, I have the information broken down into 36 logical tables. I
will list a few just to give a bigger picture:

Client information: address, city, state, zip &telephone #
Client first contact: 1st contact name, 1st contact telphone #, 1st contact
email
Client Location2: Address, city, state, zip & telphone #
Fund Company: fund company contact, contact phone, address, city, state, zip
Funds: fund company, fund name, share class, category name,


some clients will have same fund companys and same funds.

What would be the best way to assign primary keys and to link these

tables?