A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Analyzing/Normalizing Database



 
 
Thread Tools Display Modes
  #1  
Old July 24th, 2008, 07:12 PM posted to microsoft.public.access.tablesdbdesign
JWeaver
external usenet poster
 
Posts: 90
Default Analyzing/Normalizing Database

I created a database based on one that the office already had. There is one
table that contains everything instead of several tables therefore some
information is repeated over and over again in each record. I want to make
this database better by getting it "Normalized" so that there is one table
for Employee Info, one for Client Info, one for Payroll Info, etc.

I used the Analyze Table function to separate the big table into smaller
tables. This seems to work OK if I only have a few tables but if I try to
separate them into a lot of different tables for those small pieces of the
table that are different, I get a MaxLocksPerFile error. Even if I increase
this number in the Registry I get the same error so I gave up.

When Access separates the tables, and I go to the subsequent steps, it seems
that there are a great deal of records that show up that it believes has
related data and I have to delete out its proposed correction. If I separate
the table, I don't have to do this as much.

This database is used to gather payroll information. Therefore, some fields
(like Start Date and End Date) are the same for every employee during a
billing period. How finely should I separate the table to make it better?
If I need to add a record, do I need to add info to all tables or would I be
able to add it to one table and the other tables would fill in automatically
based on the lookup fields?

Your help in getting this database normalized would be appreciated!!

--
JWeaver
  #2  
Old July 24th, 2008, 09:49 PM posted to microsoft.public.access.tablesdbdesign
Klatuu
external usenet poster
 
Posts: 7,074
Default Analyzing/Normalizing Database

I have never done it using the analyzer, so I can't give any specifics on how
to use it. I usually do it myself by looking at the data and the business
requirements, and creating my own model, then using append queries to move
the data into the tables I created.

As to what tables you need, it is hard to say. The first thing to look at
is those repeating values you mentioned. That usually indicates a child
table so the repeated value is carried only once in a table and a foreign key
field is used to reference the value.

For sure, you will want an Employee table that contains all the static
information about an employee. And a payroll transaction table that that
carries the value for each pay cycle. If you have hourly employees, then you
probably need an attendance table to show what hours the employee worked.

But, without seeing what you have now, it is not possible to give a complete
answer.
--
Dave Hargis, Microsoft Access MVP


"JWeaver" wrote:

I created a database based on one that the office already had. There is one
table that contains everything instead of several tables therefore some
information is repeated over and over again in each record. I want to make
this database better by getting it "Normalized" so that there is one table
for Employee Info, one for Client Info, one for Payroll Info, etc.

I used the Analyze Table function to separate the big table into smaller
tables. This seems to work OK if I only have a few tables but if I try to
separate them into a lot of different tables for those small pieces of the
table that are different, I get a MaxLocksPerFile error. Even if I increase
this number in the Registry I get the same error so I gave up.

When Access separates the tables, and I go to the subsequent steps, it seems
that there are a great deal of records that show up that it believes has
related data and I have to delete out its proposed correction. If I separate
the table, I don't have to do this as much.

This database is used to gather payroll information. Therefore, some fields
(like Start Date and End Date) are the same for every employee during a
billing period. How finely should I separate the table to make it better?
If I need to add a record, do I need to add info to all tables or would I be
able to add it to one table and the other tables would fill in automatically
based on the lookup fields?

Your help in getting this database normalized would be appreciated!!

--
JWeaver

  #3  
Old July 24th, 2008, 10:13 PM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default Analyzing/Normalizing Database

Hi JWeaver

The Table Analyzer Wizard tries its best to do a good job, but it does not
understand your data like you do, so there is really no substitute for
sitting down with a pencil and a large sheet of paper ;-)

You start by grouping all your data fields into logical entities. Each
entity becomes a table. Every field which contains data that is a a direct
attribute of an entity belongs in that table.

For example, an employee's name, address, date of birth, SSN, etc are all
direct attributes of an employee, so belong in the Employees table.
However, the name, address and phone number of the employee's company are
all *indirect* attributes, so they belong in another table - the Companies
table - with a one-to-many relationship to Employees.

It's often said that a good database is like Grandma's pantry - a place for
everything and everything in its proper place. Try to design a pantry for
your data and see how you get on. If you need more help, post back here
with a list of all your tables and fields and a brief description of each
field (unless the name makes it obvious, like "EmployeeLastName").
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"JWeaver" wrote in message
...
I created a database based on one that the office already had. There is
one
table that contains everything instead of several tables therefore some
information is repeated over and over again in each record. I want to
make
this database better by getting it "Normalized" so that there is one table
for Employee Info, one for Client Info, one for Payroll Info, etc.

I used the Analyze Table function to separate the big table into smaller
tables. This seems to work OK if I only have a few tables but if I try to
separate them into a lot of different tables for those small pieces of the
table that are different, I get a MaxLocksPerFile error. Even if I
increase
this number in the Registry I get the same error so I gave up.

When Access separates the tables, and I go to the subsequent steps, it
seems
that there are a great deal of records that show up that it believes has
related data and I have to delete out its proposed correction. If I
separate
the table, I don't have to do this as much.

This database is used to gather payroll information. Therefore, some
fields
(like Start Date and End Date) are the same for every employee during a
billing period. How finely should I separate the table to make it better?
If I need to add a record, do I need to add info to all tables or would I
be
able to add it to one table and the other tables would fill in
automatically
based on the lookup fields?

Your help in getting this database normalized would be appreciated!!

--
JWeaver



  #4  
Old July 25th, 2008, 02:26 PM posted to microsoft.public.access.tablesdbdesign
JWeaver
external usenet poster
 
Posts: 90
Default Analyzing/Normalizing Database

Thanks for the reply!

My table is used to gather payroll information for employees who work with
clients. We gather the information and enter it into the database on a
weekly basis. The dates used are Saturday to Friday, therefore, for whatever
date is submitted I look to see which dates that falls between and use them
for the starting and ending dates. Every employee could have the same dates
entered for the starting and ending dates so these dates repeat throughout
the database for a particular pay period ending date. Here is what my table
looks like now:

PPE Date – Pay period ending date (Every other Friday)
Employee Last
Employee First
Employee #
Employee Pay Rate
Client First
Client Last
Client Program – could be one of 4 programs
Contract Hours – each employee is on a contract that is for a specific
number of hours
Contract Date – the date that the contract ends
From – Starting date for week that employee worked with client
To – Ending date for week that employee worked with client
Hours – Number of hours that employee worked with client during the week
ID – Autonumber field
Dups OK – Sometimes an employee will submit hours for a partial week on one
payroll and more hours for the same week on a different payroll. This field
is used to ensure that it is not the same dates that are being submitted.
Notes – used to make notes about the payroll period for the client/employee

Thanks for your help!!!!!
--
JWeaver


"Graham Mandeno" wrote:

Hi JWeaver

The Table Analyzer Wizard tries its best to do a good job, but it does not
understand your data like you do, so there is really no substitute for
sitting down with a pencil and a large sheet of paper ;-)

You start by grouping all your data fields into logical entities. Each
entity becomes a table. Every field which contains data that is a a direct
attribute of an entity belongs in that table.

For example, an employee's name, address, date of birth, SSN, etc are all
direct attributes of an employee, so belong in the Employees table.
However, the name, address and phone number of the employee's company are
all *indirect* attributes, so they belong in another table - the Companies
table - with a one-to-many relationship to Employees.

It's often said that a good database is like Grandma's pantry - a place for
everything and everything in its proper place. Try to design a pantry for
your data and see how you get on. If you need more help, post back here
with a list of all your tables and fields and a brief description of each
field (unless the name makes it obvious, like "EmployeeLastName").
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"JWeaver" wrote in message
...
I created a database based on one that the office already had. There is
one
table that contains everything instead of several tables therefore some
information is repeated over and over again in each record. I want to
make
this database better by getting it "Normalized" so that there is one table
for Employee Info, one for Client Info, one for Payroll Info, etc.

I used the Analyze Table function to separate the big table into smaller
tables. This seems to work OK if I only have a few tables but if I try to
separate them into a lot of different tables for those small pieces of the
table that are different, I get a MaxLocksPerFile error. Even if I
increase
this number in the Registry I get the same error so I gave up.

When Access separates the tables, and I go to the subsequent steps, it
seems
that there are a great deal of records that show up that it believes has
related data and I have to delete out its proposed correction. If I
separate
the table, I don't have to do this as much.

This database is used to gather payroll information. Therefore, some
fields
(like Start Date and End Date) are the same for every employee during a
billing period. How finely should I separate the table to make it better?
If I need to add a record, do I need to add info to all tables or would I
be
able to add it to one table and the other tables would fill in
automatically
based on the lookup fields?

Your help in getting this database normalized would be appreciated!!

--
JWeaver




  #5  
Old July 25th, 2008, 07:25 PM posted to microsoft.public.access.tablesdbdesign
JWeaver
external usenet poster
 
Posts: 90
Default Analyzing/Normalizing Database

I forgot to mention that I have an Employees Table already that contains some
of the field information as the Payroll Table. I haven't been able to figure
out how to make it work with my Payroll table.

Employees Table:
Last Name
First Name
Home Dept
Employee #
Hire Date
Pay Rate 1 - employee could have a different pay rate depending on what
program he/she was working in
Pay Rate 2
Pay Rate 3

I also have another Table that I use for payroll to pay our drivers who do
transportation of our clients. I was thinking about combining this one with
the Payroll table since some of the fields overlap and the same information
is gathered in both.

Transportation Table:
ID
Last Name
First Name
Employee #
Pay Rate
Starting Date - weeks begin on Saturday and end on Friday - would look at
date of transportation and use the Saturday that was during that week
Ending Date - this would be the Friday of the week that begins on Saturday
for the transporation above
Hours - this is the number of hours to be paid for the transporations during
the week above
PPE Date - this is the pay period ending date - every other Friday

Any help on being able to use these tables more efficiently would be greatly
appreciated!!!
--
JWeaver


"JWeaver" wrote:

Thanks for the reply!

My table is used to gather payroll information for employees who work with
clients. We gather the information and enter it into the database on a
weekly basis. The dates used are Saturday to Friday, therefore, for whatever
date is submitted I look to see which dates that falls between and use them
for the starting and ending dates. Every employee could have the same dates
entered for the starting and ending dates so these dates repeat throughout
the database for a particular pay period ending date. Here is what my table
looks like now:

PPE Date – Pay period ending date (Every other Friday)
Employee Last
Employee First
Employee #
Employee Pay Rate
Client First
Client Last
Client Program – could be one of 4 programs
Contract Hours – each employee is on a contract that is for a specific
number of hours
Contract Date – the date that the contract ends
From – Starting date for week that employee worked with client
To – Ending date for week that employee worked with client
Hours – Number of hours that employee worked with client during the week
ID – Autonumber field
Dups OK – Sometimes an employee will submit hours for a partial week on one
payroll and more hours for the same week on a different payroll. This field
is used to ensure that it is not the same dates that are being submitted.
Notes – used to make notes about the payroll period for the client/employee

Thanks for your help!!!!!
--
JWeaver


"Graham Mandeno" wrote:

Hi JWeaver

The Table Analyzer Wizard tries its best to do a good job, but it does not
understand your data like you do, so there is really no substitute for
sitting down with a pencil and a large sheet of paper ;-)

You start by grouping all your data fields into logical entities. Each
entity becomes a table. Every field which contains data that is a a direct
attribute of an entity belongs in that table.

For example, an employee's name, address, date of birth, SSN, etc are all
direct attributes of an employee, so belong in the Employees table.
However, the name, address and phone number of the employee's company are
all *indirect* attributes, so they belong in another table - the Companies
table - with a one-to-many relationship to Employees.

It's often said that a good database is like Grandma's pantry - a place for
everything and everything in its proper place. Try to design a pantry for
your data and see how you get on. If you need more help, post back here
with a list of all your tables and fields and a brief description of each
field (unless the name makes it obvious, like "EmployeeLastName").
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"JWeaver" wrote in message
...
I created a database based on one that the office already had. There is
one
table that contains everything instead of several tables therefore some
information is repeated over and over again in each record. I want to
make
this database better by getting it "Normalized" so that there is one table
for Employee Info, one for Client Info, one for Payroll Info, etc.

I used the Analyze Table function to separate the big table into smaller
tables. This seems to work OK if I only have a few tables but if I try to
separate them into a lot of different tables for those small pieces of the
table that are different, I get a MaxLocksPerFile error. Even if I
increase
this number in the Registry I get the same error so I gave up.

When Access separates the tables, and I go to the subsequent steps, it
seems
that there are a great deal of records that show up that it believes has
related data and I have to delete out its proposed correction. If I
separate
the table, I don't have to do this as much.

This database is used to gather payroll information. Therefore, some
fields
(like Start Date and End Date) are the same for every employee during a
billing period. How finely should I separate the table to make it better?
If I need to add a record, do I need to add info to all tables or would I
be
able to add it to one table and the other tables would fill in
automatically
based on the lookup fields?

Your help in getting this database normalized would be appreciated!!

--
JWeaver




  #6  
Old July 27th, 2008, 11:22 PM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default Analyzing/Normalizing Database

Hi J

OK, so it's immediately clear that Clients and Employees are separate
entities and therefore belong in separate tables. Lets look at some
possible structures:

Employees
========
empID (primary key - could be an autonumber unless you have a "natural"
primary key)
empNumber (is this unique, unchanging and guaranteed to be there? If
so, it could be used as your "natural" PK instead of empID)
empLastName
empFirstName
.... and other attributes of the employee - for example:
empHomeAddress
empPhoneNumber
empStartDate (start of employment)
.... etc

Clients
=====
cliID (PK - autonumber - see above)
cliLastName
cliFirstName
.... and other attributes of the client- for example:
cliAddress
cliPhone
.... etc

Now, as I understand it, a contract is arranged with a client (may be
multiple contracts per client):

Contracts
=======
cntID (PK - autonumber - see above)
cntClient (this is a "foreign key" [FK] in a one-to-many relationship
between the Clients table and the Contracts table. It holds the primary key
value of the client who "owns" this contract)
cntName (short name by which this contract can be identified)
cntStartDate
cntCompleteDate (null unless contract is complete)
cntProgram (not certain from your description whether this belongs here
or in Clients)
.... and probably also
cntDescription
cntExpectedHours
cntExpectedCompleteDate
.... etc

Now, an employee works on a given contract for a number of hours on a
certain day:

ContractWork
==========
wrkContract (FK to contracts table)
wrkEmployee (FK to employees table)
wrkDate (date work was done)
wrkHours (hours worked)
wrkPayRate (rate for this work)

Now, you haven't made it clear if the transportation is associated with a
contract or whether it is is arranged for the client separately from the
contract. If it's associated with the contract, you can use the same table,
with an extra field, wrkType, having one value for normal work and another
for transportation. If it's not contract-related then you need another
table:

Transportation
===========
trsClient (FK to clients table)
trsEmployee (FK to employees table)
trsDate (date work was done)
trsHours (hours worked)
trsPayRate (rate for this work)

If each employee has multiple possible pay rates, then you should NOT store
them as multiple fields in the Employees table. Whenever you see fields
with names such as Rate1, Rate2, Rate3, etc, it is a clear indication of
lack of normalisation. Instead, you need a PayRates table:

PayRates
=======
prtEmployee (FK to Employees table)
prtName ("Normal", "Weekend", "Out-of-town", etc)
prtRate ($ per hour rate)

Your main data entry form would be bound to the ContractWork table. It
would have a combo box listing all active contracts (no completion date)
with client name and contract name. You simply select the contract and the
employee (from another combo box) and enter the date and the number of
hours. A simple way to select the correct pay rate would be to have a combo
box showing all the possible rates for the selected employee. On selection,
the prtRate value would be copied to wrkPayRate.

A similar form could be used, if necessary, for transportation data entry.

Now, you probably don't need a separate table for pay periods, because you
can ascertain the pay period week from wrkDate (or trsDate). The WeekDay
function returns a number representing the day of the week, with 1=Sunday,
2=Monday and so on to 7=Saturday. So this expression:
=[wrkDate] - (Weekday([wrkDate]) Mod 7)
will give you the date of the previous Saturday (unless wrkDate IS a
Saturday, in which case it will give you the same day).

On the other hand, you may not wish to make the assumption that any
particular work has been paid up just because it falls in a particular
period (perhaps an employee was unable to submit his work records for data
entry before the cutoff date for the pay period. So you may wish to add
another table:

PayPeriods
========
ppdID (autonumber)
ppdPayDate (usually a Friday)
ppdCutoffDate (usually the previous Friday)

You then add a field wrkPayPeriod (FK) to your ContractWork table (and
trsPayPeriod to Transportation). Each Friday you add a new pay period
record and run an update query to write the new ppdID into wrkPayPeriod for
any work that occurred on or before ppdCutoffDate AND where wrkPayPeriod is
not already filled in. You can then easily generate a detailed report for
that week's pay, including the employee, date worked, number of hours,
contract and client. You can also generate a summary report showing the
total to pay for each employee.

Oh, one other thing - note that my field names do not contain spaces or
other special characters. You will find that non alphanumeric characters in
field names, while they are legal, will cause a great deal of extra work as
you develop your application further. Also, note that the prefix of each
field name shows clearly which table it comes from. This is a convention I
find useful to aid the memory and avoid ambiguity.

This has probably given you a reasonable amount to chew on. I hope it
doesn't cause indigestion! ;-)

Post back if you have any further questions.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand



"JWeaver" wrote in message
...
I forgot to mention that I have an Employees Table already that contains
some
of the field information as the Payroll Table. I haven't been able to
figure
out how to make it work with my Payroll table.

Employees Table:
Last Name
First Name
Home Dept
Employee #
Hire Date
Pay Rate 1 - employee could have a different pay rate depending on what
program he/she was working in
Pay Rate 2
Pay Rate 3

I also have another Table that I use for payroll to pay our drivers who do
transportation of our clients. I was thinking about combining this one
with
the Payroll table since some of the fields overlap and the same
information
is gathered in both.

Transportation Table:
ID
Last Name
First Name
Employee #
Pay Rate
Starting Date - weeks begin on Saturday and end on Friday - would look at
date of transportation and use the Saturday that was during that week
Ending Date - this would be the Friday of the week that begins on Saturday
for the transporation above
Hours - this is the number of hours to be paid for the transporations
during
the week above
PPE Date - this is the pay period ending date - every other Friday

Any help on being able to use these tables more efficiently would be
greatly
appreciated!!!
--
JWeaver


"JWeaver" wrote:

Thanks for the reply!

My table is used to gather payroll information for employees who work
with
clients. We gather the information and enter it into the database on a
weekly basis. The dates used are Saturday to Friday, therefore, for
whatever
date is submitted I look to see which dates that falls between and use
them
for the starting and ending dates. Every employee could have the same
dates
entered for the starting and ending dates so these dates repeat
throughout
the database for a particular pay period ending date. Here is what my
table
looks like now:

PPE Date - Pay period ending date (Every other Friday)
Employee Last
Employee First
Employee #
Employee Pay Rate
Client First
Client Last
Client Program - could be one of 4 programs
Contract Hours - each employee is on a contract that is for a specific
number of hours
Contract Date - the date that the contract ends
From - Starting date for week that employee worked with client
To - Ending date for week that employee worked with client
Hours - Number of hours that employee worked with client during the week
ID - Autonumber field
Dups OK - Sometimes an employee will submit hours for a partial week on
one
payroll and more hours for the same week on a different payroll. This
field
is used to ensure that it is not the same dates that are being submitted.
Notes - used to make notes about the payroll period for the
client/employee

Thanks for your help!!!!!
--
JWeaver


"Graham Mandeno" wrote:

Hi JWeaver

The Table Analyzer Wizard tries its best to do a good job, but it does
not
understand your data like you do, so there is really no substitute for
sitting down with a pencil and a large sheet of paper ;-)

You start by grouping all your data fields into logical entities. Each
entity becomes a table. Every field which contains data that is a a
direct
attribute of an entity belongs in that table.

For example, an employee's name, address, date of birth, SSN, etc are
all
direct attributes of an employee, so belong in the Employees table.
However, the name, address and phone number of the employee's company
are
all *indirect* attributes, so they belong in another table - the
Companies
table - with a one-to-many relationship to Employees.

It's often said that a good database is like Grandma's pantry - a place
for
everything and everything in its proper place. Try to design a pantry
for
your data and see how you get on. If you need more help, post back
here
with a list of all your tables and fields and a brief description of
each
field (unless the name makes it obvious, like "EmployeeLastName").
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"JWeaver" wrote in message
...
I created a database based on one that the office already had. There
is
one
table that contains everything instead of several tables therefore
some
information is repeated over and over again in each record. I want
to
make
this database better by getting it "Normalized" so that there is one
table
for Employee Info, one for Client Info, one for Payroll Info, etc.

I used the Analyze Table function to separate the big table into
smaller
tables. This seems to work OK if I only have a few tables but if I
try to
separate them into a lot of different tables for those small pieces
of the
table that are different, I get a MaxLocksPerFile error. Even if I
increase
this number in the Registry I get the same error so I gave up.

When Access separates the tables, and I go to the subsequent steps,
it
seems
that there are a great deal of records that show up that it believes
has
related data and I have to delete out its proposed correction. If I
separate
the table, I don't have to do this as much.

This database is used to gather payroll information. Therefore, some
fields
(like Start Date and End Date) are the same for every employee during
a
billing period. How finely should I separate the table to make it
better?
If I need to add a record, do I need to add info to all tables or
would I
be
able to add it to one table and the other tables would fill in
automatically
based on the lookup fields?

Your help in getting this database normalized would be appreciated!!

--
JWeaver





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.