Table Relationships and Primary Keys - - - - Need Help

[Olddogg1]

I am not too familiar with access and the relationships / primary keys that I need to define. If I could get a hand with this that would be excellent.

-- What the Database is For --

I am creating an access database for office use. It has all of our clients and their respective information. It is for a financial firm for easy reference.

-- the Data for the Database --

Ok. Here is where I am lost. I have a list of companies that all have their own Contact & General information. But not all of them have the other information (RPP, GI, and RRSP). Some of the companies have the same plans under (GI, RRSP, and RPP) Some have no plans...

This is what I was thinking. I have 6 tables:

Company Index - (company ID *PK, RPPID, GIID, RRSPID, and company name)

Company Contact Information - (company ID *PK, and all of the contact information fields)

Company General Information - (Company ID *PK, and all of the general information fields)

Company RPP Information - (RPPID *PK, all of the RPP plan information)

Company GI Information - (GIID *PK, all of the GI information fields)

Company RRSP Information - (RRSPID *PK, and all of the other RRSP plan information fields)

-- What I can't figure out --

How do I create relationships and primary keys so that I can relate all of this information together?

-- A Few Example Companies --

Kerridge -- has its own general and contact info. but has the same RPP plan as Smith Construction. and has no RRSP information. has no GI information.

Smith Contruction - has its own general and contact info. but has the same RPP plan as Kerridge. has its own unique RRSP plan. and has the same GI information as Tucker Concrete.

Tucker Concrete - has its own general and contact info. has no RPP information. has its own unique RRSP plan. but has the same GI plan as Smith Construction.

--------------------------------------------------------

I appreciate all of your advice and comments. Thank you very much for your time.

JC

 

[TomCologne]

This might become a long discussion and if I had more time I'd try to go into more detail right now.

For the moment I strongly recommend looking into the article about db design that our valued TT member Jeremy Wallace (JeremyNYC) has linked here:

http://www.abcdataworks.com/fundamentals.htm

TomCologne

 

[rac2]

How do I create relationships and primary keys so that I can relate all of this information together?

It looks like you are headed in the right direction. Not really sure I understand what you are asking, but here are some thoughts.

Creating a primary key is done when you create the table. The primary key column should be an arbitrary value, most easily obtained by defining the column to be autonumber. Each of the tables will have a primary key as you show. In the GUI for creating a table, look for a key icon, select the column and click the icon. Presto, it is the primary key.

Probably you will be building a form to add companies to the CompanyIndex table. That form will have fields for entering the reference to Contact, General, RPP, etc. These fields can be filled with dropdown menus. The creation of the form and so forth is way beyond me, it uses the database but is really about the Access application building which may be another Tek-Tip forum than this one.

One tricky part will be the Contacts because new ones will be added for every company, and the primary key will not exist until they are added, so it becomes a little bit complex to build that application.

The fields RPPID, GIID, and RRSPID are primary keys for their respective tables, but when they are used in the CompanyIndex table they are foreign keys. Meaning they point to a row in another table. Whereas, primary key means this row in this table. Foreign keys can be empty(Null), they are not required to have a value.

So if there is no RPP info for some company the value of the column RPPID in the row in CompanyIndex for that company will be Null. This becomes an issue when you are building queries because a simple INNER JOIN requires a row in both tables being joined. In this case you will need to use a LEFT JOIN, otherwise the company without an RPPID will be omitted from the results.

The CompanyIndex structure will work and is sufficient unless a) you discover that there is a new kind of information which will be stored in a new table, maybe a table of Company YouTube URLs. This will lead to redefining the CompanyIndex table by adding a new column as well as creating the new table.

Or b) companies can have two or more entries in the RPP Info table. In which case you lose with the structure you have proposed. For this, referred to as a one-to-many relationship, you need an association table. An association table is a table made up of nothing but foreign keys. For the RPP association the columns would be RPPID and CompanyID. Then a company with two RPPs would have two rows in the association table. You would define an association table for each one-to-many relationship. I know it seems excessive, but that is the way this issue is handled in a relational database.

As to different companies sharing the same information, that is not a problem. It simply means that the foreign key columns in the CompanyIndex table will have the same values. This is OK. It is a many-to-one relationship, many companies may have one RPP info. Your proposed structure will handle this, no problem.