If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Design/Change Help Requested
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!!! -- JWeaver |
#2
|
|||
|
|||
Database Design/Change Help Requested
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWeaver" wrote in message ... 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!!! -- JWeaver |
#3
|
|||
|
|||
Database Design/Change Help Requested
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 "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JWeaver" wrote in message ... 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!!! -- JWeaver |
Thread Tools | |
Display Modes | |
|
|