The farther I think I'm ahead, the farther I fall behind

[Julie417]

Uggghhh - I am now turned in all directions regarding the database I am trying to design.

The company I work for can be compared to a staff agency (for pharmacists/pharmacies).



I don't know what information to include, as I don't want to bog this down, but I'll start at the simplist part (I use simplist loosly, because it doesn't seem to be very simple).

I have an Employees table, which includes the most basic information on the employee, such as contact phone numbers, email addresses, and what type of pharmacy he goes to.

I then have an EmployeeRecord table, which contains the records such as license number, tax forms, internal hr forms (actually, just the date they signed these forms, not the whole form itself), etc.

I linked the primary key of the Employee table, as a foreign key, to the EmployeeRecord table.

When I enter the employee names into the Employee table, shouldn't it fill in on the EmployeeRecord table? When I look in the Employee table, there is a plus by the name, and when clicking it brings up the EmployeeRecords fields, but you still have to fill all the employee's names. (I understand that I will still need to fill in the information that is not on the Employee table.)

What am I missing??? What I described is only one small aspect of this database, so I am quite frustrated!!

Can someone help please?

Julie

[orange]

My recommendation is to read the first few topics at this site
http://www.rogersaccesslibrary.com/forum/topic238.html

Normal forms and Normalization are key.
Entity Relationship Diagramming will lead you through the creation of the things of interest to you and how they relate to each other.

In my view, you are just too close to the problem. You know the area and how things work and can not treat the issue as objectively as someone with no knowledge of your environment would.

I would not name a table EmployeeRecordTable. Employee I can understand, Contracts etc, but EmployeeRecordTable is confusing to me.

Each time you identify an entity -- write a description for it. Same for attributes of the entities. This will help clarify and document many of the things that can be taken for granted. It will also assist with communications as you start to "vet" the model.

You may also want to gather some test data -- for good cases and bad cases.

Just a few thoughts . Good luck.

[Julie417]

Hi Orange,

Seems I got swamped with work, besides working on this database, here lately so I'm just now able to reply.

I think you're right - am too close to the information I need. I'm trying to step back and look more objectively.

The EmployeeRecordTable might do better with a different name, but I need information relating to when forms were signed, the license numbers......HR related stuff. Maybe it should be labeled HRTable??

I have followed the link you provided and there was a lot of good information. Thanks!

I will keep working at this and referring back to this forum - so much good information!!

Thanks again ----- Julie

[blazerboy6]

Hey Julie,

I've got a similar design in my own company tables, one is the general info, and the other is the privileged info (ie, HR). I split it like this to protect the PII so that the private table is password protected etc.

Make sure the two tables are linked via an Employee ID, at a 1 to 1 relationship. In the databases relationship window you can select cascade update for the relationship between the EmployeeID's from the two tables, so that it will produce a new employee ID in the private table when you enter in a new employee, but you don't want to duplicate information such as first and last names. If the information is not PII (ie protected information from other users of the database) I would add the fields into a single tblEmployees to make life simple.

If you need to split them for the above reasons, your forms can be set up to simultaneously pull information from both tables for you, but that can take some effort to set up in the background if one is password protected.

[Julie417]

Thanks Blazerboy - something I hadn't thought of. Definitely something I will want to work in.

[SteveF]

Uggghhh - I am now turned in all directions regarding the database I am trying to design.

The company I work for can be compared to a staff agency (for pharmacists/pharmacies).

I don't know what information to include, as I don't want to bog this down, but I'll start at the simplist part (I use simplist loosly, because it doesn't seem to be very simple).

I have an Employees table, which includes the most basic information on the employee, such as contact phone numbers, email addresses, and what type of pharmacy he goes to.

I then have an EmployeeRecord table, which contains the records such as license number, tax forms, internal hr forms (actually, just the date they signed these forms, not the whole form itself), etc.

I linked the primary key of the Employee table, as a foreign key, to the EmployeeRecord table.

When I enter the employee names into the Employee table, shouldn't it fill in on the EmployeeRecord table? When I look in the Employee table, there is a plus by the name, and when clicking it brings up the EmployeeRecords fields, but you still have to fill all the employee's names. (I understand that I will still need to fill in the information that is not on the Employee table.)

What am I missing??? What I described is only one small aspect of this database, so I am quite frustrated!!

Can someone help please?

Julie

A question regarding your basic design:

In your Employees table, you have a field that tracks "what type of pharmacy he goes to".

Is an employee only allowed to go to one type of pharmacy? What happens if a person is qualified to staff more than one pharmacy type?

If a person can potentially be assigned to more than one pharmacy type, then this needs to be put into a separate table. It just needs to be a simple table: two columns (EmployeeID and PharmacyTypeID), where there's one record for each pharmacy type an employee can staff. If employee #25 can staff three different types of pharmacies, then there would be three records for employee #25 in this table.

Make sense? If multiple qualifications are possible, this needs to be done at the beginning to avoid big hassles later on.

Also, in your relationship setup: "I linked the primary key of the Employee table, as a foreign key, to the EmployeeRecord table." I think this needs to be other way around: The EmployeeRecord table should have an EmployeeID field in it that's related as a foreign key to the Employee table.

Steve