Table Design and Efficiency Help (3 Viewers)

[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.