Best practices for new project (1 Viewer)

swingline

Member
Local time
Today, 18:11
Joined
Feb 18, 2020
Messages
51
I have a project that I haven't stated yet and I'm looking for advice on some best practices.
essentially I will have a table with personnel information, standard stuff First Name, Last name, ID number, ect.

Then I will have a table with some on the job training information such as Academic hours, practical hours, Date of OJT, approximately 30 OJT task which I plan to capture how many times each task is completed per date, Some Comments, and an overall performance grade for the OJT day.

My question is should I break a table like this up in to smaller tables? If so whats the best way to link on that data back to say the "ID number" so I could filter all events by ID number and date for reports?
 

June7

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,425
First, don't use spaces or punctuation/special characters in naming convention, nor reserved as names.

Next, don't use multi-value fields.

Don't build lookup fields in tables.

Yes, should be multiple smaller tables (long and narrow instead of wide and short). At a minimum, I can see 3 entities - people, tasks, results. So, at least 3 tables. Use form/subform arrangements for data entry and/or comboboxes to select items to associate entity records. Build queries to do analysis of related data and output on reports. This is all quite basic concepts. Have you studied any tutorials on database design?
 

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,476
My question is, do you understand normalization? If not, study it well, draw out a plan on paper (table & field names, data types, related tables and joins) to see if you can make your thoughts fit a normalized schema that supports the process. I figure at least 5 tables from what I read here, but jargon doesn't always paint a clear picture. F'rinstance, OJT is not academic training so training hours probably should be in one table as 2 fields, but an OJT list would be its own table as would a table for recording training type given.

It is likely that you will be able to find many examples of templates and/or schemas. They will make more sense IF you understand normalization first, and how to apply it to your needs. The toughest part is usually figuring out which things are entities vs attributes. There are a couple of training examples here.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 19, 2002
Messages
42,986
Good thing you asked. Sounds like you were thinking about 30 columns, one for each task. That is spreadsheet thinking. June got you on the right track. There will be 30 rows, one for each task in the task table. If you have to add more tasks in the future, it is a matter of adding a new row and nothing else needs to be done. If tasks were columns as you were thinking, many design features would need to be changed. Post back if you need more direction.
 

swingline

Member
Local time
Today, 18:11
Joined
Feb 18, 2020
Messages
51
My question is, do you understand normalization? If not, study it well, draw out a plan on paper (table & field names, data types, related tables and joins) to see if you can make your thoughts fit a normalized schema that supports the process. I figure at least 5 tables from what I read here, but jargon doesn't always paint a clear picture. F'rinstance, OJT is not academic training so training hours probably should be in one table as 2 fields, but an OJT list would be its own table as would a table for recording training type given.

It is likely that you will be able to find many examples of templates and/or schemas. They will make more sense IF you understand normalization first, and how to apply it to your needs. The toughest part is usually figuring out which things are entities vs attributes. There are a couple of training examples here.

I didn't know the term for it, but I started writing tables on paper because of lessons learned from poor design on past projects.
 

swingline

Member
Local time
Today, 18:11
Joined
Feb 18, 2020
Messages
51
Good thing you asked. Sounds like you were thinking about 30 columns, one for each task. That is spreadsheet thinking. June got you on the right track. There will be 30 rows, one for each task in the task table. If you have to add more tasks in the future, it is a matter of adding a new row and nothing else needs to be done. If tasks were columns as you were thinking, many design features would need to be changed. Post back if you need more direction.
So like one column for "OJT TASK" and then a new entry for each task?
 

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,476
I didn't know the term for it, but I started writing tables on paper
Based on your last post, I think you interpreted my comment about designing on paper as being called 'normalization'. Far from it.
You could say normalization is the exact opposite of designing a table like a spreadsheet, which is where I think you were heading. The paper & pencil are just tools to facilitate the process. Really looks to me like you need to read up on it if you were going to have a field for every task. One litmus test is, "will I need to add another table field to store values for another of the same type of thing?" . If so, the design is usually wrong.
 

June7

AWF VIP
Local time
Today, 07:11
Joined
Mar 9, 2014
Messages
5,425
Yes, one column for task ID and a new record for each task/person and grading is on right track. This is a 'junction' table that associates tasks with people. Assuming each task can be done by multiple people.
 

swingline

Member
Local time
Today, 18:11
Joined
Feb 18, 2020
Messages
51
Yes, one column for task ID and a new record for each task/person and grading is on right track. This is a 'junction' table that associates tasks with people. Assuming each task can be done by multiple people.
Seems I have a fair bit of reading to do before I can tackle this next project efficiently. I understand the concept just not sure how to make a form for 30 task that all update the same column. But thanks for pointing me in the general direction. I have a long weekend coming up so I think I have now have reading plans.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:11
Joined
Feb 28, 2001
Messages
27,003
Word of warning on your search:

You can search this forum for "Normalization" with some reasonable expectations. However, if you search the web for it, use "Database Normalization" because there are other types of normalization that have nothing to do with computers or databases.

In particular, for your initial reading, I would look to sites in the .EDU domain first. Colleges and universities often have good on-line tutorials or essays or explanations of normalization. I'm warning you away from .COM domain only initially, because (a) they often have something to sell to you and (b) it is often a proprietary or uncommon variant. Therefore, do some non-commercial reading first. Once you've gotten some of the ideas figured out, then if you want to look at the .COM sites, go ahead. I'm just trying to help you avoid "clutter" initially.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:11
Joined
Feb 19, 2002
Messages
42,986
To expand on what June suggested. I think you need five tables. This assumes that tasks could apply to more than one job. That means you first need to create the relationship between tasks and jobs. Then to record the results, you need to create the relationship between person and JobTask.

tblTask
TaskID (autonumber, PK)
TaskName
TaskDesc

tblJob
JobID (autonumber, PK)
JobName
JobDesc
etc.

tblJobTasks
JobTaskID (autonumber, PK)
JobID (FK to tblJob)
TaskID (FK to tblTask)

tblPerson
PersonID (autonumber, PK)
FirstName
LastName
etc.

TblTaskResult
ResultID (autonumber, PK)
JobTaskID (FK to tblJobTask)
PersonID (FK to tblPerson)
ResultDT
Result
 

Users who are viewing this thread

Top Bottom