Naming Conventions
Avoid spaces in table names and field names - they complicate the VBA code and the SQL code. Most Access experts prefer using CamelCase - just capitalize the first letter of each word and run the words together. If you must have space between words, then use an underscore _.
It's helpful to put the prefix tbl in front of tables, frm in front of forms, qry in front of queries. This is one of a number of competing standards, and you can find lots of others out there.
When synonyms are available that are shorter and mean the same thing, the shorter word is preferred. tblStaff rather than tblEmployees might be an example, or tblRole or tblPosts rather than tblPositions. Your mileage may vary.
Reserved words
Name, Date, Time, Month, Year, Join, and a bunch of other words are reserved word in Access. Class, Frequency and Position, I'm not sure about. Best to avoid reserved words.
Key Fields and Relationships
The best practice is to use the primary key of a table as the foreign key on other tables. Using the text name of a Class as the foreign key would be problematic. At the very least, if you proceed in that way, you should change the fieldname Class to ClassName and establish an index on it.
You should also make sure you use the exact same name for the same foreign key field in different files. Using ClassID in one place and Class in another will lead to confusion.
Some programmers might use the name ClassPK (for example) on the tblClasses and ClassFK or ClassID as the foreign key, but the more common practice is to use ClassID in all places.
Junction Table Names
I prefer to avoid the practice of bumping the names of two related tables together for the name of the junction table. For instance, a table that shows the hierarchy of employees (who reports to whom) would be called employee_employee. Best practice is to name the junction table based on naming the relationship it define, such as tblReportsTo or tblWorksFor or tblManager.
For example, the table that indicates an Employee has taken a class is currently named EmployeeClasses. My preference would be for a name like tblClassesTaken or tblClassesCompleted.
The table that shows what classes are required for a position, instead of PositionsClasses, could be either tblClassesRequired or tblPositionRequirements.
Code:
tblEmployees
EmpID autokey
DeptID FK to tblDepts
PostID FK to tblPositions
tblPositions
PostID autokey
PostName Text
tblClasses
ClassID autokey
ClassName Text
Frequency (Type?)
tblClassesTaken
TakenID autokey
ClassID FK to tblClasses
EmpID FK to tblEmployees
takenDate Date
tblPositionRequirements
ReqID Autokey
PostID FK to tblPositions
ClassID FK to tblClasses
Frequency (Type?)
I suspect that the frequency field may belong on tblPositionRequirements rather than tblclasses, since refreshers may have different frequencies depending on usage.
Relationships
There's a "Cycle" that goes from tblEmployees through classes and position back to the employee. This means that, in certain circumstances, you can ask the database a question, and it could give you different answers if you ask the question in a different order. This is especially a problem when your query has to do with finding things that have NOT happened, which is a significant concern in this database.
I don't see a way to avoid this design, due to the purpose of the database. You just have to be aware that to pull records from both sides of that loop, you will need to make a separate query for each side of the loop, then join those two queries.
Windows 7 32bit 
Access 2007
Reply With Quote
Originally Posted by June7
