Thread: DB Design
View Single Post
  #2 (permalink)  
Old 03-01-10, 19:43
MarkATrombley MarkATrombley is offline
Registered User
 
Join Date: Jul 2009
Location: Michigan
Posts: 125
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.).

Last edited by MarkATrombley; 03-01-10 at 19:46.
Reply With Quote