Table Design and Efficiency Help

[The_Doc_Man]

Regarding the diagram shown in your post #13, a couple of comments.

Regarding tblShiftList - if there is a long name for each shift, then this table is merely a "code translator" table. The way to see this is that the prime-key/foreign-key linkages are commonly done using LONG integers, which are the same size as 4 bytes of text. If your shift name is no more than 4 characters long, there is no need to split it out. If there is more to the Shift List than a short name, then OK - translating a long name via code is correct because you don't want to have to repeat long text strings when a code number will do. In general, when string-size issues do not apply, don't split an attribute away from a table if your translation is itself a single unique value that could be stored in the same place. Note that this objection disappears the moment you have other fields in the Shift List table besides its long name, attributes such as specific times of day for start/end of shift, or some sort of note about shift bonuses for people on the graveyard shift - things that belong with the shift and that apply to the employee ONLY because s/he is on that shift.

Regarding your tblSpecializedAssignments - can someone ever participate in two assignments at once? Because if so, this diagram does not show how that might be supported.

Regarding your tblSpecializedTraining - this is SURELY a more direct example of the problem with Specialized Assignments, because it is almost a dead certainty that longer-term employees will be exposed to multiple training sessions - and this structure only supports one Specialized Training per person.

The term "junction table" comes to mind. The other comments above have discussed many aspects of what you are showing us. The common theme I see is that you don't know how to demonstrate many-to-many relationships, of which the Training issue is a prime candidate. You solve the MM problem with junction tables.

 

[LarryE]

I disagree on not using ID as the primary key. It's simple and directly identifies the primary key. Note all foreign keys begin with part of the table name followed by ID making it intuitive what the key represents.

You will regret that decision.

 

[RonPaii]

You will regret that decision.

I don't understand you issue. It's actually the default added by Access if you don't define a primary key. And Yes I know Access has some questionable defaults.

 

[Ken Sheridan]

I don't understand you issue. It's actually the default added by Access if you don't define a primary key. And Yes I know Access has some questionable defaults.

Well, it has to be called something. Like many object names given by Access it is meaningless and should be replaced with an informed choice of name. Leaving all tables with a key named ID obscures the semantics when you write complex SQL statements involving multiple tables. In my career as an Environmental Planning Officer, much of my time was spent modelling environmental data, and querying that data in complex ways. I have adopted the convention of naming tables with plural or collective nouns which reflect the entity type being modelled by the table, or, where the table is modelling a relationship type, a name which describes the relationship type. Columns are named wherever possible with singular nouns, reflecting the fact that a column represents an attribute of the entity type modelled by the table. This makes writing, and reading, SQL statements much closer to plain English. SQL is a declarative language remember, so it makes sense if what is being declared to be as self evident as possible.

Take the following simple query from one of my demo files as an example. It's pretty obvious to anyone reading the SQL statement what this query is doing:

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,
Country, Employer, LastName, FirstName, Contacts.ContactID
FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)
INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts ON Cities.CityID = Contacts.CityID)
INNER JOIN ContactEmployers ON Contacts.ContactID = ContactEmployers.ContactID)
  ON Employers.EmployerID = ContactEmployers.EmployerID)
    ON Regions.RegionID = Cities.RegionID
WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity
    OR Forms!frmReportDialogue!cboCity IS NULL)
AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer
    OR Forms!frmReportDialogue!cboEmployer IS NULL);

 

[RonPaii]

Well, it has to be called something. Like many object names given by Access it is meaningless and should be replaced with an informed choice of name. Leaving all tables with a key named ID obscures the semantics when you write complex SQL statements involving multiple tables. In my career as an Environmental Planning Officer, much of my time was spent modelling environmental data, and querying that data in complex ways. I have adopted the convention of naming tables with plural or collective nouns which reflect the entity type being modelled by the table, or, where the table is modelling a relationship type, a name which describes the relationship type. Columns are named wherever possible with singular nouns, reflecting the fact that a column represents an attribute of the entity type modelled by the table. This makes writing, and reading, SQL statements much closer to plain English. SQL is a declarative language remember, so it makes sense if what is being declared to be as self evident as possible.

Take the following simple query from one of my demo files as an example. It's pretty obvious to anyone reading the SQL statement what this query is doing:

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,
Country, Employer, LastName, FirstName, Contacts.ContactID
FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)
INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts ON Cities.CityID = Contacts.CityID)
INNER JOIN ContactEmployers ON Contacts.ContactID = ContactEmployers.ContactID)
  ON Employers.EmployerID = ContactEmployers.EmployerID)
    ON Regions.RegionID = Cities.RegionID
WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity
    OR Forms!frmReportDialogue!cboCity IS NULL)
AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer
    OR Forms!frmReportDialogue!cboEmployer IS NULL);

Thank you for the reply. I'm not trying to be contrary.

The only issue I have with your design rules is that from a quick reading of your query, it's not immediately obvious which ID is the primary key and which is the foreign key. Where if ID is always the primary key then your query becomes as follows.

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,
Country, Employer, LastName, FirstName, Contacts.ContactID
FROM (Countries INNER JOIN Regions ON Countries.ID = Regions.CountryID)
INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts ON Cities.ID = Contacts.CityID)
INNER JOIN ContactEmployers ON Contacts.ID = ContactEmployers.ContactID)
  ON Employers.ID = ContactEmployers.EmployerID)
    ON Regions.ID = Cities.RegionID
WHERE (Cities.ID = Forms!frmReportDialogue!cboCity
    OR Forms!frmReportDialogue!cboCity IS NULL)
AND (Employers.ID = Forms!frmReportDialogue!cboEmployer
    OR Forms!frmReportDialogue!cboEmployer IS NULL);

But as long as you follow your own rules you coding will be constant and maintainable.

 

[Mark_]

@mfk5331
I would make sure exactly how they want to track statistics for KPIs. Normally instead of saving a "Shift" in your values, you can either store each value with the date/time it was entered (say amount of a sale) or a date/time for each shift start so you can track statistics from one employee that covers multiple shifts.

For manufacturing, if you have a couple employees who often work overtime not breaking out when production occurs can cause oddities on a "Per-shift" breakout if their overtime productivity is attributed to their normal shift and not the shift they work into. Alternately if you make sure everyone KNOWS and signs off on this, you can keep to the same system.

I'd also have each stat saved on a per-day basis. Makes generating a daily average for each employee much easier, especially when they either work overtime or have an unexpectedly long absence.

 

[cheekybuddha]

I would strongly recommend that you do not use ID as a generic name for keys. Instead name each to reflect the entity type modelled by the table, e.g. EmployeeID.

I see this advice trotted out regularly, and I would suggest it's unnecessary.

If anything, it risks ascribing meaning to the PK of a table which it shouldn't have - the only job of the PK is to be a unique identifier for each row of a table.

Note how often folks get their knockers in a twist when there are missing InvoiceID's.

Whenever you use ID in a query join it will be qualified by the table name or an alias, so there is no confusion, and there is nothing more irritating than having to write: Employees.EmployeeID.

There is a risk of confusing the engine if you SELECT fields from more than one table without qualifying them, or using SELECT * ... , but both of those cases are poor practice anyway.

When SELECT'ing from a single table a field named 'ID' is non-problematic, and probably clearer as to what the field actually represents.

(Edit: Ah! Ninja'd by Ron!)

 

[DHookom]

Thank you for the reply. I'm not trying to be contrary.

The only issue I have with your design rules is that from a quick reading of your query, it's not immediately obvious which ID is the primary key and which is the foreign key. Where if ID is always the primary key then your query becomes as follows.

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,
Country, Employer, LastName, FirstName, Contacts.ContactID
FROM (Countries INNER JOIN Regions ON Countries.ID = Regions.CountryID)
INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts ON Cities.ID = Contacts.CityID)
INNER JOIN ContactEmployers ON Contacts.ID = ContactEmployers.ContactID)
  ON Employers.ID = ContactEmployers.EmployerID)
    ON Regions.ID = Cities.RegionID
WHERE (Cities.ID = Forms!frmReportDialogue!cboCity
    OR Forms!frmReportDialogue!cboCity IS NULL)
AND (Employers.ID = Forms!frmReportDialogue!cboEmployer
    OR Forms!frmReportDialogue!cboEmployer IS NULL);

But as long as you follow your own rules you coding will be constant and maintainable.

To each his/her own. If I have an employee table, ever field in the table begins with emp. The auto number primary key is empEmpId. The foreign key fields begin with three letters identifying the table followed by three letters from the related table and “Id”. I love not having to think about most field names. My decisions are only the first three letters of the table. So a junction table of projects with employees would have a primary key of PrjEmpID.

 

[mfk5331]

Whether this diagram represents an appropriate design depends on how you need to track the data.

1. Are you ONLY interested in an employee's Position, Shift, Assignment and Training TODAY?
2. Do you need to keep track of an employee's current AND prior Positions, Shifts, Assignments and Training
3. Do you need to keep track on ONE type of Specialized Training for employees?
4. Do you need to keep track of ONE OR MORE types of Specialized Training for employees?
5. Can an employee be assigned to ONE Position at a time?
6. Can an employee be assigned to ONE OR MORE Positions at a time (split time between them)?

If you need History, you need an additional table.
If you need to handle ONE OR MORE Positions, Shifts, Assignments or SpecializedTraining, you need additional tables.

Tell us about those things.

1. It would be nice to be able to track what employee's production was as they gain new assignments and trainings, it's not critical.
2. Again, it would be nice, but not critical. I wouldn't even know where to begin to include these two things.
3-4. As of right now, employees can only have 1 Specialized Assignment at a time, but can have multiple Specialized Trainings. Using the Lookup Wizard, I went back and made both of these allow multiple values to future proof them so multiple items can be selected.
5-6. Employees cannot be assigned to more than 1 Position or Shift at a time.

Would would be needed in a table to include history, to be able to see what position, shift, assignments, or training an employee had at the time the stats were from?

 

[GPGeorge]

1. It would be nice to be able to track what employee's production was as they gain new assignments and trainings, it's not critical.
2. Again, it would be nice, but not critical. I wouldn't even know where to begin to include these two things.
3-4. As of right now, employees can only have 1 Specialized Assignment at a time, but can have multiple Specialized Trainings. Using the Lookup Wizard, I went back and made both of these allow multiple values to future proof them so multiple items can be selected.
5-6. Employees cannot be assigned to more than 1 Position or Shift at a time.

Would would be needed in a table to include history, to be able to see what position, shift, assignments, or training an employee had at the time the stats were from?

Unfortunately, using Multi-Value Fields is not a great solution. I am not as adamant on this point as some, but I would strongly recommend against that, especially at this point, where you are just starting out and have more appropriate options easily available.
Post #21 mentions junction tables. That is the much more appropriate approach I mean. It may seem like more work, but in the long run, it will prove to be the better solution.

You need two additional tables. One for EmployeeAssignments and one for EmployeeTrainings. These tables have, at a minimum two fields each. These are the Primary Keys for the Employees and the Primary Keys for the Assignments and the Primary Keys for the Employee and the Primary Keys for the Trainings. When these values are used in this way, they are referred to as Foreign Keys to their respective tables. The combination of the two can be the Primary Key for the junction table. In that case, they're called a Composite Primary Key. Two Keys together make up the Primary Key. I prefer to add an autonumber Primary Key instead. And in that case there is a Unique Index on the Employee and Assignment Foreign Keys and the Employee and Training Foreign Keys.

With regard to Position and Shift. The question here isn't whether they can be assigned to more than 1 at a time, but whether you want to know the history of those assignments. If you only ever need to know that John Doe currently works as an Inspector and not that he was previously an Assembler, that's one table design. But if you do want to know what previous Positions he held, that's a different table design. You note that it would be nice to have that history, so I think you better choice is to include it now, not try to retrofit it at some point down the road.

 

[XPS35]

I want to be able to track productivity for employees for those stats, then run reports to see those stats by employee, shift, by position, by assignment, etc.

Just a reminder to post #1.
In order to achieve this it is not just "nice" to have the history but necessary. As I said before (#2), an option is to store the shift, position etc. of an employee in a month into the tblStat. Option two is having junction tables with dates to store the history.
Another problem is that an employee might work several shifts in a row in one month. You need a business role to tell you how to handle this. Applying that rule in Accees could be quite complicated.

 

[DaveMD]

I want to be able to track productivity for employees for those stats, then run reports to see those stats by employee, shift, by position, by assignment, etc.

Many on this thread have provided very good input. To help illustrate what we mean by junction tables and provide one way a portion of this can be done, you could follow a design pattern like the below diagram.

This overall would provide the following benefits:

• tblEmployeePositionHistory: This allows for tracking employee positions with a history of when they were in each position (StartDate to EndDate). For example, if an employee changes positions from Assembler to Inspector (using @GPGeorge example), that employee would have those two position spans defined in this table.
  
  
• tblEmployeeWorkTime: This is where each specific work time entry (for each day worked) for an employee can be tracked. Tracking the ShiftID in this table would inherently provide a history of what shift each employee actually worked regardless of what their default shift might be or if they later change their default shift. You can think of this table like your employee time tracking which in this design can allow for querying and reporting stats in many different ways – by shift, a ratio / sum of hours worked, etc.
  
  
• tblEmployeeWorkStats: Serves as a junction table between WorkTime and the related Stats. In other words, for each date an employee worked (as tracked in the WorkTime table), you can track the related stats. For example, if the employee assembled 20 widgets when they worked 8 hours on December 5th, this table could reference the StatID for WidgetsAssembled with a Value of 20 related back to that WorkTime entry for December 5th.
  
  
• tblStatList: The defined stats that you track -- WidgetsAssembled, WidgetsInspected, WidgetsSold.
  
  
• tblPositionList: Defined positions in your company – WidgetAssembler, WidgetInspector, WidgetSalesAssociate.
  
  
• Note: In tblEmployeeList, you can also store a DefaultShiftID to be able to define the current default shift the employee is in. But the actual shift for tracking and reporting statistics is through the ShiftID FK in tblEmployeeWorkTime.

With this design, you are then able to develop queries and reports to track statistics by employee, by shift, by time period (x date to y date), by position, etc.

 

[mfk5331]

I'm not sure if I'm doing this right or not. I added tables for EmployeePositionHistory, EmployeeShiftHistory, and SpecializedAssignmentHistory. While trying to add relationships from tblEmployeeList to the new tables, it is showing Relationship Type as Indeterminate. Here is what I added.

I was trying to create the relationships from the PositionID field in tblEmployeeList to PositionID in tblEmployeePositionHistory, ShiftID in tblEmployeeList to ShiftID in tblEmployeeShiftHistory, etc. Is that the best way to do it?

 

[XPS35]

That is not the way to do it. PositionID, ShiftID and others should not be in the employee table anymore. The positions of an employee, including the current, are now in the history table.
You need relationships from employee to the history table on employeeID and from position table to history on PositionID.

 

[GPGeorge]

I'm not sure if I'm doing this right or not. I added tables for EmployeePositionHistory, EmployeeShiftHistory, and SpecializedAssignmentHistory. While trying to add relationships from tblEmployeeList to the new tables, it is showing Relationship Type as Indeterminate. Here is what I added.

View attachment 122660

I was trying to create the relationships from the PositionID field in tblEmployeeList to PositionID in tblEmployeePositionHistory, ShiftID in tblEmployeeList to ShiftID in tblEmployeeShiftHistory, etc. Is that the best way to do it?

One of the basic ideas in table design is that each field in a table is an attribute of the entity which is the subject of that table.

Looking only at tblEmployeeList we need to clarify what is, and what isn't an attribute of an Employee, i.e. a person who works for your organization.

Several things do apply: LastName, FirstName, EmployeeNumber and "Active" (which I assume is a Yes/No field indicating the Employee's status with regard to employment at your organization).

You need those attributes when pointing to employees in order to distinguish between them. "I mean John Doe, Employee Number 123456, not Sally Jones, Employee Number 654321". Active is important to your recordkeeping and history of employees, so that's also an attribute about the employee.

However, Shifts, Training, Positions and Assignments don't tell you anything about the Employee as an employee differentiated from other employees, not like you can define a person by referring to their name or their Employee Number. Those things tell you about how your employees interact with other things of interest to you. An employee is not constrained to one position; you can't define an employee as "a shift one person". That's a role the person fills, not something intrinsic to the employee's identity.

Now, because you are working with a subset of people, i.e. people who are employees, you can add attributes other than the basics to a table if that table encompasses more than just "peopleness" attributes. Here's where it can get tricky.

If, and only if, Shift, Training, Positions and Assignments are immutable qualities of an Employee in your organization, a table called "EmployeeList" could include those fields. In other words, it is sometimes reasonable to include other attributes when we expect those attributes to be durable, like Employee Number. As an aside, I acknowledge that there are times when things like LastName can change, so immutability is not always ironclad. But the point is that once we assign an attribute to an entity, we don't expect it to change, or if it does change, it is sequentially, not concurrently, important and the previous value is no longer relevant to us. If a woman marries and takes her husband's last name, that is the new last name we use.

So, if a person only ever works on one shift, or only ever undergoes one training, or only ever holds one position, or only ever holds one assignment, then those could be attributes of the Employee in the EmployeeList table. It could also be true that you care only about their current roles and statuses. If your record-keeping is such that you do not care what shift someone worked on two years ago, but only what they are doing today, then that could be an attribute of the Employee. You might not want to know anything about the history of your employees with your company. That would be very rare, I believe, but it would be a case where you'd keep ShiftID, etc. in the EmployeeList.

However, that is not the case here, if I understand correctly. Employees can move from shift to shift, and you want to know that when you look at their records a year from now. "Who worked the night shift last year when the plant exploded?"

That's not an attribute of the Employee. That is a role they filled and the history of that role is important. ShiftID is not part of the EmployeeList.

The same is true of the other attributes mentioned above. If you need to know history as well as current status, those fields belong in history tables, and not in the EmployeeList table.

There's still another aspect of table design beyond the history tables. That's the situation where Employees can have multiple concurrent attributes. I'm looking at Trainings. I assume that there's an aspect of answering questions like, "Which employee has had training in both Risk Management and Customer Service?" in your application. That's another case where the TrainingID can't be in the EmployeeList table.

Take a long, hard look at each of your tables and decide which attributes pertain only to the subject of each table.

 

[Ken Sheridan]

I'm not sure if I'm doing this right or not. I added tables for EmployeePositionHistory, EmployeeShiftHistory, and SpecializedAssignmentHistory. While trying to add relationships from tblEmployeeList to the new tables, it is showing Relationship Type as Indeterminate.

These tables each model a many to many relationship type between tblEmployeeList and the other referenced table by resolving the relationship type into two one to many relationship types, so, as Peter has explained you do not need the foreign keys in tblEmployeeList. One important point abput such a table is the need to define the composite candidate key which is done by including the two foreign keys in a single unique index. In the case of the tblEmployeeHistory table for instance, if each employee can hold the same position only once, the the two foreign key columns constitute the candidate key. If, however, an employee might hold the same position for two or more different periods, then the StartDate column would need to be added to the candidate key by its inclusion in the same unique index. The same would apply to the other two relationships. The image below is of a basic many to many relationship type:

The fact that you have already included a 'surrogate' primary key in each of the three tables does not remove the need to define the candidate keys in the above way. In fact, you could drop the surrogate keys and make the candidate keys the composite primary keys of the tables, as in the above example. The only advantage in having a surrogate key would be if the table is referenced by one or more other tables in one to many relationship types.

PS: As regards training and the need for a many to many relationship type which George has described, the image below is the model for a simple training log database. As you'll see there is a one many to many relationship between employees and course qualifications, modelled by the EmployeeCourseLog table, and a further many to many relationship type between course titles and qualifications modelled by CourseQualifications. This illustrates the fact that, in the database relational model a relationship type is just a special kind of entity type, so can be a party to another relationship type in the same way as any entity type. For courses which have no qualifications, they would reference a row in Qualifications with a value of 'N/A' or similar at the Qualification column position.

 

[mike60smart]

1. It would be nice to be able to track what employee's production was as they gain new assignments and trainings, it's not critical.
2. Again, it would be nice, but not critical. I wouldn't even know where to begin to include these two things.
3-4. As of right now, employees can only have 1 Specialized Assignment at a time, but can have multiple Specialized Trainings. Using the Lookup Wizard, I went back and made both of these allow multiple values to future proof them so multiple items can be selected.
5-6. Employees cannot be assigned to more than 1 Position or Shift at a time.

Would would be needed in a table to include history, to be able to see what position, shift, assignments, or training an employee had at the time the stats were from?

Hi
If you want to be able to choose a Supervisor , then allocate a Shift and then allocate Employees to the Shift selected, then you need tables as shown in the attached screenshots.

The Data Input Form allows you to select a Supervisor, Select the Shift he is responsible for and then add Employees for that Shift.

 

[DaveMD]

To help illustrate some of the concepts that are being noted, the relationships for this portion of your diagram could look something like the below as far as the History tables. As many have already mentioned, there are many different factors in determining how the tables and relationships should be put together dependent on the specific way your organization operates and the intended uses of your database. As others have already mentioned, note the suggested removal of the fields with the red cross-out from your tblEmployeeList table.

As another note conceptually with History tables (using tblEmployeePositionHistory as the example), it will be up to your business rules whether you would allow an employee to have more than one concurrent Position. Or, you could implement your business rules to only allow an employee to hold one position at a time. These rules can be implemented in your entry form using VBA to check if the employee has any overlapping Start/End dates before allowing the entry to be committed to your database.

 

[mfk5331]

Thank you all for all of the help. I'm slowly getting there. I've taken all of your advice and made some updates, I think I'm finally getting it. Here is how I have it set up now. At my company, an employee can not hold more than 1 position or be on more than 1 shift at a time. As of right now, an employee can only have 1 specialized assignment at a time, but that could change in the future. An employee can hold multiple specialized trainings at a time.

How would you recommend tying the stats into this? The stats are measured per month, and all are measured in simple numbers.

Once the stats section is all set up, I will be starting to build the tables for our company goals. I'm not sure if these will be easier to set up or more complicated. Each fiscal year (September 1 - August 31) we have a standard set of goals for all employees. Some of these goals come from a few of the productivity stats, others are things like attending a training class or participating in company events. Some of the goals are measured quarterly, others are measured annually. Then to complicate things further, the goals will sometimes change slightly from year to year. My plan is to create a junction table tblGoalsHistory with fields GoalHistID, EmployeeID, GoalID, StartDate, and EndDate. Then I'll create tblGoals with fields GoalID, EmployeeID, GoalName, GoalNumber, DateCompleted, and Description.

 

[mike60smart]

Thank you all for all of the help. I'm slowly getting there. I've taken all of your advice and made some updates, I think I'm finally getting it. Here is how I have it set up now. At my company, an employee can not hold more than 1 position or be on more than 1 shift at a time. As of right now, an employee can only have 1 specialized assignment at a time, but that could change in the future. An employee can hold multiple specialized trainings at a time.

View attachment 122670

How would you recommend tying the stats into this? The stats are measured per month, and all are measured in simple numbers.

Once the stats section is all set up, I will be starting to build the tables for our company goals. I'm not sure if these will be easier to set up or more complicated. Each fiscal year (September 1 - August 31) we have a standard set of goals for all employees. Some of these goals come from a few of the productivity stats, others are things like attending a training class or participating in company events. Some of the goals are measured quarterly, others are measured annually. Then to complicate things further, the goals will sometimes change slightly from year to year. My plan is to create a junction table tblGoalsHistory with fields GoalHistID, EmployeeID, GoalID, StartDate, and EndDate. Then I'll create tblGoals with fields GoalID, EmployeeID, GoalName, GoalNumber, DateCompleted, and Description.

Hi
It would help us understand if you gave us examples of actual "Stats" rather than a list of Stat1 through to Stat30