Hi,

I have been trying to build a database where I have Companies, Employees, Dialogs, and Addresses. There are other tables, but they are not key to this question I believe. In working with data, I realize that people may not stay with the same company, and they move to others. That said, I would like to be able to preserve the history of employees at a company. Also important is the dialog. While I have it with a person, if that person leaves, I want to be able to related it to both the person and the company in the future. I have made some progress with linking tables, but it looks messy, and I am not sure if I am doing it correctly / efficiently.

Your help is greatly appreciated as this type of complicated design has been a sticking point for me. Please let me know if I have been confusing or you need more info.

Thanks,
Dave

I would set it up like this:

Company: CompanyID, etc.
Employee: EmployeeID, etc.
Address: AddressID, etc.
Dialog: CompanyID (FK to Company), EmployeeID (FK to Employee), DialogDate, etc.

CompanyEmployee: CompanyID (FK to Company), EmployeeID (FK to Employee), StartDate, EndDate

CompanyAddress: CompanyID (FK to Company), AddressID (FK to Address), StartDate, EndDate

EmployeeAddress: EmployeeID (FK to Employee), AddressID (FK to Address), StartDate, EndDate

The StartDate and EndDate columns represent the time period when the information was valid - Bob Smith worked at Acme Inc. from 1/1/2007 to 2/1/2009. An EndDate of 12/31/9999 means he still works there. When he is terminated the EndDate will be changed to be the actual termination date.

The CompanyEmployee, CompanyAddress, and EmployeeAddress are all many to many tables.

The ID columns are all unique numbers per table (surrogate keys). You will probably want to put a unique index on other columns to prevent duplicates (so you don't end up with two Acme Inc.).

Mark, thanks for the reply. I follow parts of what you suggest, but not others. First, should the Dialog table have a Dialog_ID field as the PK - this may be assumed, but I want to check? Also why a start and end date for each of the linking tables - it seems like it would require much effort to maintain these tables. I will probably have more questions, but will leave it at that for now.

Dave

I would create a DialogID column as a PK just for consistency. It is absolutely needed but I usually do it anyway.

The reason for the start and end date columns on the linking tables is so you know during what period the relationship was valid. If you followed the career of Bob Smith (EmployeeID number 417 you might see something like this in the CompanyEmployee table:

CompanyID 12, EmployeeID 417, StartDate 3/1/1980, EndDate 5/15/1988
CompanyID 33, EmployeeID 417, StartDate 5/16/1988, EndDate 7/5/1995
CompanyID 81, EmployeeID 417, StartDate 7/6/1995, EndDate 12/31/9999

This would show Bob Smith working for 3 different companies from 1980 to current. The purpose is so you can "the history of employees at a company". This included an assumption that you cared about following the career of a person from company to company. Even if you don't you will probably want to know their hire and termination dates.

Similarly by having start and end dates in the address you can link Bob Smith to different addresses if he moves. You would know that Bob Smith lived at one address from 1980 to 1988, then moved to another address.

You didn't include much about what your ultimate goals for the database were, so I had to make some guesses. Perhaps I guessed wrong.

Mark I am with you on the dates. It seems a logical approach to me. You are right, I should have given a bit more background.

The idea behind this data base is to track professional interactions between myself (and other members of my firm) an other companies we interact with. For example, I may speak with a person at another company, and wish to document the dialog. Many of these companies, I have been dealing with for over 10 years. People have come and gone, owners have passed away, and sons / external people have taken over. In addition, there are times when a person leaves one company and moves onto an other.

Key here is that a history has been created that relates to the Company and Employee. Its easy to track when the two are together (Employee & Company) but it becomes harder to track (at least for me) when the two separate. For example, if I call a company and the person has left for a new position elsewhere, I would like to be able to view past dialog with the person at that company and be able to say that in the past I have spoke with Mr. X about X, Y, and Z.

The Address parameter I wish to track is based on the idea that some companies have multiple locations - sometime multiple offices, other times offices and manufacturing facilities, or overseas locations. Tracking the history of the address is not critical, but is probably easy enough to keep if its tracked at all I guess. I will draw up (and post) some of the tables / methodology I had come up with and compare to your suggestions.

I hope this gives a better idea of where I am coming from. Much appreciated, Dave.