Database Structure

[moodhi]

Hi there,
I need some advice on database design/structure.
I have about 30 Employees (employees can be added or Deleted etc).
Each employee can have a number of Roles (job type) e.g. Manager, Deputy Manager, Senior day, Carer day etc up to 15
The employees are supposed to attend a combination of 30 courses for which a date will be entered in the Grid (below). Not all employees will attend all courses.

The data need on the screen as a Grid is as follows:
Course A Cousre B Course C Cousre D
EmpId EmpName Status 14/01/2019
EmpId EmpName Status 21/01/2019 15/04/2019
EmpId EmpName Status 22/04/2019

Obviously I have Employees and Roles tables with one-to-many relationship.
So for the course matrix (above) I need to create a table.
Will it be better to have OPTION 01 - a table with EmployeeID, CourseDate and 30 (courses) fields CousreA, CourseB, CourseC, CourseD .....etc (across)? or is it better to have OPTION 02 - 3 fields (1) EmployeeID (2) CourseDate (3) Course Name ?

Considering that I am attending to use Continuous Form (for the Grid), it will obviously be easier to fill the form's RowSource with OPTION 01 provided if OPTION 01 of creating the table is acceptable. Failing that if I create the table with OPTION 02 what is the best way of then populating the Form's RowSource?

 

[plog]

Each employee can have a number of Roles...
...Obviously I have Employees and Roles tables with one-to-many relationship.

I believe that's incorrect. Sounds like you are missing a table. The relationship between Employees and Roles isn't 1 to many, but many to many. For this you need a junction table (aka Associative Entity: https://en.wikipedia.org/wiki/Associative_entity), to sort all those relationships out.

tblJobRoles
JobRoleID, autonumber, primary key of table
EmployeeId, number, foreign key to tblEmplyees
RoleId, number, foreign key to tblRoles

There might be more fields (e.g. BeginDate, EndDate, Notes, etc.). That's how you handle many to many relationships.

The data need on the screen as a Grid is as follows:

To what end? You didn't give a purpose of this screen. Are you talking about a Report (read-only) or a Form (add/edit/delete)? In either case, why? Why must the data be presented as such?

 

[jdraw]

moodhi,
Further to plog's comments I suggest you write a description in plain English based on a "Day at the Office" to give readers (and you) a reference to all of the "things/entities" in context. This will help determine the groupings of data (tables) and the business rules/facts that will lead to the relationships between tables. Getting the tables and relationships designed and tested is a critical part of database.
It is much easier to work with a paper and pencil based model with sample data than to jump into physical database and attempt design by trial and error.

Good luck with your project.

 

[moodhi]

Apologies but an Employee can have a maximum of 4 roles e.g. 1 employee having up to 4 roles, hence 1 to many? So what I have so far is:
(1) tabEmployees (EmpID, EmpName, .........all other fields)
(2) tabRoles (EmpID, RoleID, DateStart, DateEnd, ......all other fields)

Any way my query is about Courses. An employee have to attend a number of courses (out of 30 total). Now my question is what structure should the CourseMatrix table be?

OPTION 01
tabCourseMatrix (EmpID, CourseDate, CourseA, CouserB, CourseC, ...up to 30 courses)
or
OPTION 02
tabCourseMatrix (EmpID, CourseDate, CourseID)

If OPTION 01 then form's RowSoucre can easily be used to populate the form. But if more courses are added in future then a new field will need to be added to the table and amend the coding etc....

If OPTION 02 then do I need to use a crosstab query to to populate the form via the RowSoucre ?

 

[plog]

No, Employees to Roles is definitely many to many:

1 Employee can have many roles (Steve can be a Manager, Inspector, Line Worker, etc.)
1 Role can have many employees (Managers include Sally, Dave, Tim, etc.)

You need the junction table I prescribed to solve that.

------------------------------

OPTION 02 is correct.

--------------------------------

I still don't understand what the purpose of this form is for. Reading data? Or editing data? Why must it be in the format you prescribed? This method is a double Catch-22; by the mere fact you are asking questions about doing it makes me think you don't have the technical abilites to do so--using a cross-tab for a form is not an easy task. There are a few people who frequent this site can pull of what you want with a cross-tab, however they wouldn't do so because it's not the right way to achieve what you want--whatever that may be.

 

[Uncle Gizmo]

Will it be better to have OPTION 01 or OPTION 02?

Will it be better to have OPTION 01 or OPTION 02?

You asked a good question!

Option one is by far the simplest, the most logical and possibly the most attractive. However it does have some major problems. The first major problems is if you add another field, then every subsequent query, form, report, has to be manually edited. The other problem with it is if you want to extract useful information from the data, then again separating out the "Courses" into separate Fields can be a mistake.

Let's say you wanted to find out how many courses a particular student took. You would have to devise complicated queries over several Fields. And really any other sort of reporting on the data, other than very simple information extraction is just a complete headache.

So most developers would probably suggest option one.

I've written a Blog here which goes into more detail about the problems and issues:- "Excel in Access"


Now regarding the problem of displaying the data back in the format you want,I don't think there is a simple way. I may be Wrong!

I don't have a specific example but this might give you an idea of one way to go about it.

Display Field Data Horizontally


The sample files are available for free if you sign up to my newsletter here:- Subscribe to Nifty News

 

[moodhi]

So for example if there are a total of 28 Employees and they have to attend some or a number of 30 Courses. So a Grid of 28 rows times 30 columns is required to show:

Row Heading: EmpID, EmpName
Column heading: CousreA, CousreB, CourseC, CourseD.....up 30 courses.
i.e. 28 x 30 Grid.

Each cell in the Grid (or field - CourseDate) to be entered to indicate when an employee completed that course. After entering the Course Dates the user will click SAVE button to save.

So every time the user opens the form the above Grid will show e.g. 28X30 dates when an employee completed the course. If one new employee joined then the Grid will be 29 x 30 and if more course is added then the grid will be 29 x 31 etc.

 

[plog]

What benefit does that provide over having a menu with every employee in a drop down, the user selecting an employee and clicking a button to open a form which lists all Courses in a continous form for that 1 employee?

Don't constrain yourself just because the prior iteration of this was developed in a tool with less capabilities.

 

[moodhi]

Yeh, I agree, I think you have got it. This was previously designed in Excel where the grid is full of colours with various conditional formatting. For example "The course is due in next days", "Over Due", "In Date" etc so the field when I mentioned Course Date is NOT Date/Time its simply Text field where they can can enter either course completion date or other text and based on various criteria/conditions the cells are coloured. They want to be able to see a chart of all employees in reference to their course status on one grid. I am afraid that's how they are used to. Not easy to convince to change their habits?

 

[jdraw]

moodhi,

I think you are confusing table structure, user interface and outputs (forms/reporting).
These are separate things and do need to be considered, but there is a sequence/cycle to design/development. More basic there appears to be a confusion of What and How.

Here is a draft data model for consideration based on my read of your requirement. There may be other entities and attributes (eg. AttendedYN, CourseResults....)