Advice on Design...

[Access rookie]

Hello,
I am putting together a database for employees.
There are three types E1, E2,E3(at the moment) and they all have huge
amounts of information to collect that's unique to each type.
I am thinking of creating a main table that would contain information common
to all 3 (Name, address, gender, etc) and then have a tblE2requirements,
tblE3requirements that would hold the different requirements for E2 and E3
employees respectively.
Now, to successfully do this, I'll have to make sure I have the primary key
(eg employee Number) in the main table, and then, make sure the other tables
have a foreign key (Employee Number) in each table, right? Wouldn't that make
it one-many?
The information is really supposed to be one to one...or is it the other way
around? ie have the E2requirementsID (Primary key) as a foreign key in the
main table?

A little confused at the end of the day,

John.

 

[Duane Hookom]

It sounds like you are using multiple "requirements" as multiple fields in
separate tables. I'm not sure if this is the case but if it is, consider a
more normalized approach where requirements create records, not fields.

 

[Jack MacDonald]

Further to Duane's comments, if you were to post some typical fields
from your perceived E1, E2, and E3 tables, then someone could comment
whether you may indeed benefit from additional normalization.

However, assuming that your database design is correct and they indeed
belong as separate tables, then you are on the right track with the
foreign key concept. With these proviso's:

- the PK in the main table is an autonumber

- the keys in the other tables are Long Integer, and indexed as
NoDuplicates.

- the tables are related by these fields. Access will automatically
make them one-to-one relationships because the keys in both tables in
the relationship are indexed with NoDuplicates.

- use a form / subform design to populate the subsidiary tables.
Access will automatically populate the linking field in the subsidiary
tables.

Hello,
I am putting together a database for employees.
There are three types E1, E2,E3(at the moment) and they all have huge
amounts of information to collect that's unique to each type.
I am thinking of creating a main table that would contain information common
to all 3 (Name, address, gender, etc) and then have a tblE2requirements,
tblE3requirements that would hold the different requirements for E2 and E3
employees respectively.
Now, to successfully do this, I'll have to make sure I have the primary key
(eg employee Number) in the main table, and then, make sure the other tables
have a foreign key (Employee Number) in each table, right? Wouldn't that make
it one-many?
The information is really supposed to be one to one...or is it the other way
around? ie have the E2requirementsID (Primary key) as a foreign key in the
main table?

A little confused at the end of the day,

John.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Jeff Boyce]

If one person (common table) would only have one row in E2 (or E1 or E3),
you have a one-to-one relationship. To do that, your E's tables would have,
as a primary key, the same value as the corresponding tblPerson row's
primary key. If you use Autonumber in your tblPerson, you'd use the
corresponding LongInt data type for the E-tables.

I'm with Duane and Jack, though. Post back some of what you are putting in
columns as E-attributes. It may be that they can be handled as rows in a
more fully normalized design.

 

[Kevin]

Question: Can a person who is an E1 be promoted to an E2? If so you need to
normalize the data more than you have because you will have problems
resulting from having records in both tables. Consider two tables tblEmployee
and tblEmployeeGrade. Then tblEmployee would have all general info such as
Name, Birthdate, SSN (if your in the US) address, Employee Grade, etc.
tblEmployeeGrade would have all information related to each grade. These two
tables would be linked with the PK/FK concept you described in your original
post. In your input form have all the general information in the main form,
then create a subform which is tied to the tblEmployeeGrade and show or hide
the appropriate fields based on the Employee Grade selected in the main form.
The subform could initially be hidden for new records and displayed when the
employee grade is chosen. This makes the design of the form a little more
complicated, but it will make life simpler in many respectes when
implimenting reports and queries based on your data. Querying a database that
is not appropriately normalized can get very complicated!

Another approach which might be a little simpler would be to create subforms
for each grade. All tied to the same table but having only the appropriate
fields for that employee grade on the form. Then after selecting the employee
grade, display the appropriate subform.

Having had to deal with databases which were not properly normalized I can
tell you trying to extract information for either a standard or adhoc report
or query or report can be exceedingly difficult. Consider your design very
carefully. A proper design can make a world of difference down the road.

I hope that helps!

 

[Access rookie]

Hello Duane, Jack, and Jeff,

Thanks for your replies; they were really helpful.
I have 3 types of emloyees: DSE's, Hourly's, and Mentors.
This is a healthcare-type organization, thus each employee is dealing with
different clients.
For DSE's, it's mostly HR questions like non-harrassment policy received?,
Employee Cover sheet received?, etc. I have about 20 of those unique to DSE
employees.
For Hourly's, more info is required. Stuff like health insurance expires,
dental insurace expires, etc. I have about 12 of thesea that are unique to
Hourly employees.
For Mentors, I have about 15 unique fields required for mentors alone, eg.
Immunization dates, FBI clearance, etc.
I hope this information helps...I want to build a solid foundation with this
database so future development can be easy.

Eager as Smeagel looking for his precious,

John.

 

[John Vinson]

Thanks for your replies; they were really helpful.
I have 3 types of emloyees: DSE's, Hourly's, and Mentors.
This is a healthcare-type organization, thus each employee is dealing with
different clients.
For DSE's, it's mostly HR questions like non-harrassment policy received?,
Employee Cover sheet received?, etc. I have about 20 of those unique to DSE
employees.
For Hourly's, more info is required. Stuff like health insurance expires,
dental insurace expires, etc. I have about 12 of thesea that are unique to
Hourly employees.
For Mentors, I have about 15 unique fields required for mentors alone, eg.
Immunization dates, FBI clearance, etc.
I hope this information helps...I want to build a solid foundation with this
database so future development can be easy.

It sounds to me like you're thinking of storing data (questions, etc.)
in fieldnames. This may not be ideal. I suggest instead that you have
a many to many relationship from Employees to Issues; you may want a
setup like

Employees
EmployeeID
<bio information>

Issues
IssueID <autonumber PK>
EmployeeType <DSE, Hourly, Mentor>
IssueName <e.g. "Non-Harrassment policy received?", "Immunization
Date"

EmployeeIssues
EmployeeID <pk, fk to Employees>
IssueID <pk, fk to Issues>
IssueYesNo yes/no <for yes/no type issues>
IssueDate Date/Time <for date type issues>

Eager as Smeagel looking for his precious,

well, give me a Ring and we can discuss it...

John W. Vinson[MVP]

 

[Access rookie]

Thanks for your help everyone!

I will be putting all your advice to good use...and post when I have problems!

Eeeeexcellent (In a Montgomery Burns kind of way...)

John.