Database Design/Change Help Requested

[JWeaver]

I apologize up front for the length of this email but I wanted to give all
pertinent information to you at the beginning.

I have a database that I inherited that I am slowly trying to fix since it
started out with a whole bunch of information in one table and I have now
created other tables for some of the repeating data. However, now I want to
make them more normalized and take away the duplicated data and wanted to see
if someone could give me some guidance. My tables are as follows:

Employees:
Emp ID – PK
Advocate Last Name
Advocate First Name
Home Dept
Hire Date
Pay Rate
Active/Inactive

Clients:
ClientID – PK
Last
First

Contracts:
ContractID – PK
Client Last Name
Client First Name
Requisition Number
Service Provided
Hours Contracted
Frequency
Date Contract Begins
Date Contract Ends
Date Contract Cancelled
County/City/Town (combo box pulls from County/City/Town table)
Referring Agency
Advocate Last Name
Advocate First Name
Other Contracts
Comments/Notes
Region

County/City/Town:
Counties - PK

Payroll:
PPE Date
ID - PK
Advocate Last
Advocate First
Emp #
Pay Rate
Last
First
Program
Contract Hours
Contract Date
From
To
Hours
ClientID – Recently added FK to Clients Table - no data entered yet
ContractID – Recently added FK to Contracts Table - no data entered yet

PPE Dates: Table is populated with all of the pay period ending dates for
the year – (No PK but should field be made a PK since it does not duplicate?)
PPE Date

Pay Weeks: Table is populated with all of the pay week info for the year
WeekID - PK
WeekStart
WeekEnd

PROPOSED CHANGES TO TABLES:

Employees:
- Add PayrollID field as FK to Payroll table
- Add ContractsID field as FK to Contracts table

Clients:
- Add ContractID field as FK to Contracts table
- Add PayrollID field as FK to Payroll table

Contracts:
- Add ClientID field as FK to Clients table and delete the Client Last Name
and Client First Name fields
- Add PayrollID field as FK to Payroll table
- Add EmpID field as FK to Employees table and delete the Advocate Last Name
and Advocate First Name fields

PPE Dates:
- Change PPE Date to PK field

Payroll:
- Add PayrollID field
- Add EmpID field as FK to Employees table and delete the Pay Rate, Advocate
Last and Advocate First fields
- Add ContractID field and delete Program, Contract Hours and Contract Date
fields
- Already added ClientID field so delete First and Last fields
- Add PPEDateID field and delete PPE Date field
- Add WeekID field and delete the From and To fields

QUESTIONS:
(1) If I make the changes above will it make my tables better and more
“normalized�
(2) Would I need to set up relationships between all PKs and FKs and enforce
referential integrity between them?
(3) How would this affect my Form since they are now set to pull certain
fields from the tables that will be deleted and replaced by FKs?
(4) How will this affect my Reports that are now set to pull from the
Payroll table only?
(5) Should I make any other changes?

I thank you in advance for any help/guidance you can give me! You folks are
great at this and I appreciate it more than I can say!!!

 

[Allen Browne]

Quick answers to your 5 questions:

1. Perhaps. More below.

2. Yes. Definately.

3. You will have to change the forms as well. You will probably need to use
combos (to look things like the person's name where it stores the ID value),
and subforms (to show the related data.

4. You will have to change your reports as well. You will probably need to
create a query that combines data from multiple tables, and use that as the
source for your report.

5. Probably.

More details:
When you add foreign keys (e.g. ClientID in Contracts table), remove the
other fields that depend on that (e.g. remove [Client Last Name] and [Client
First Name] from Contracts table.)

We don't know what this data is, so we can't advise in detail. For example,
what is a contract? Is it an agreement between your company and a client?
How/why is a contract tied to an employee? Could you ever have a situation
where a contract needed more than one employee? Would it be better to break
this into 2 tables, so one contract can have many employees?

Similarly, are all payroll records actually tied to a contract? Of could
there be staff being paid for something outside of the contract (e.g.
administrative or reception duties)?

There are at least a dozen different ways to model this, and what's best
will depend on factors too numerous to post here. What you are proposing is
definately an improvement (assuming you also remove the duplicated data
fields), but whether it is optimal we cannot say.

HTH

 

[JWeaver]

Thanks for the guidance.

In answer to your questions: A contract relates to a client and specifies
what type of service we are to give and the number of hours each week to be
provided. An employee is assigned to the client/contract. A contract would
be assigned to only one employee at a time, however, the name of the employee
could change during the length of the contract.

Payroll is tied to contracts in that employees are paid for providing the
service to the client. This database is not used to pay administrative type
folks only those employees working with clients.

I hope this answers your questions.

Thanks!
--
JWeaver

Quick answers to your 5 questions:

1. Perhaps. More below.

2. Yes. Definately.

3. You will have to change the forms as well. You will probably need to use
combos (to look things like the person's name where it stores the ID value),
and subforms (to show the related data.

4. You will have to change your reports as well. You will probably need to
create a query that combines data from multiple tables, and use that as the
source for your report.

5. Probably.

More details:
When you add foreign keys (e.g. ClientID in Contracts table), remove the
other fields that depend on that (e.g. remove [Client Last Name] and [Client
First Name] from Contracts table.)

We don't know what this data is, so we can't advise in detail. For example,
what is a contract? Is it an agreement between your company and a client?
How/why is a contract tied to an employee? Could you ever have a situation
where a contract needed more than one employee? Would it be better to break
this into 2 tables, so one contract can have many employees?

Similarly, are all payroll records actually tied to a contract? Of could
there be staff being paid for something outside of the contract (e.g.
administrative or reception duties)?

There are at least a dozen different ways to model this, and what's best
will depend on factors too numerous to post here. What you are proposing is
definately an improvement (assuming you also remove the duplicated data
fields), but whether it is optimal we cannot say.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I apologize up front for the length of this email but I wanted to give all
pertinent information to you at the beginning.

I have a database that I inherited that I am slowly trying to fix since it
started out with a whole bunch of information in one table and I have now
created other tables for some of the repeating data. However, now I want
to
make them more normalized and take away the duplicated data and wanted to
see
if someone could give me some guidance. My tables are as follows:

Employees:
Emp ID – PK
Advocate Last Name
Advocate First Name
Home Dept
Hire Date
Pay Rate
Active/Inactive

Clients:
ClientID – PK
Last
First

Contracts:
ContractID – PK
Client Last Name
Client First Name
Requisition Number
Service Provided
Hours Contracted
Frequency
Date Contract Begins
Date Contract Ends
Date Contract Cancelled
County/City/Town (combo box pulls from County/City/Town table)
Referring Agency
Advocate Last Name
Advocate First Name
Other Contracts
Comments/Notes
Region

County/City/Town:
Counties - PK

Payroll:
PPE Date
ID - PK
Advocate Last
Advocate First
Emp #
Pay Rate
Last
First
Program
Contract Hours
Contract Date
From
To
Hours
ClientID – Recently added FK to Clients Table - no data entered yet
ContractID – Recently added FK to Contracts Table - no data entered yet

PPE Dates: Table is populated with all of the pay period ending dates for
the year – (No PK but should field be made a PK since it does not
duplicate?)
PPE Date

Pay Weeks: Table is populated with all of the pay week info for the year
WeekID - PK
WeekStart
WeekEnd

PROPOSED CHANGES TO TABLES:

Employees:
- Add PayrollID field as FK to Payroll table
- Add ContractsID field as FK to Contracts table

Clients:
- Add ContractID field as FK to Contracts table
- Add PayrollID field as FK to Payroll table

Contracts:
- Add ClientID field as FK to Clients table and delete the Client Last
Name
and Client First Name fields
- Add PayrollID field as FK to Payroll table
- Add EmpID field as FK to Employees table and delete the Advocate Last
Name
and Advocate First Name fields

PPE Dates:
- Change PPE Date to PK field

Payroll:
- Add PayrollID field
- Add EmpID field as FK to Employees table and delete the Pay Rate,
Advocate
Last and Advocate First fields
- Add ContractID field and delete Program, Contract Hours and Contract
Date
fields
- Already added ClientID field so delete First and Last fields
- Add PPEDateID field and delete PPE Date field
- Add WeekID field and delete the From and To fields

QUESTIONS:
(1) If I make the changes above will it make my tables better and more
“normalized�
(2) Would I need to set up relationships between all PKs and FKs and
enforce
referential integrity between them?
(3) How would this affect my Form since they are now set to pull certain
fields from the tables that will be deleted and replaced by FKs?
(4) How will this affect my Reports that are now set to pull from the
Payroll table only?
(5) Should I make any other changes?

I thank you in advance for any help/guidance you can give me! You folks
are
great at this and I appreciate it more than I can say!!!