Building a Many to Many database from the design tabs of excel spreadsheets (1 Viewer)

[enizag]

Hi all,

I am hoping someone can save me from my complete lack of cracking this issue.

I am modelling a database from an excel spreadsheet, I have 25 apprentices all completeing their own spreadhseet slightly differently, so want to standarise, there are multiple tabs, it is espically difficult to report on everyone, currently 25 users. So, I am thinking a database is the answer?

What I have in English is:
Apprentices have to do Knowledge Skills and Behaviours (KSB - there is a list of them K1,K2...B1,B2...S1,S2... There are about 20 altogether) the apprentice changes placements every 6 months, so for each of these placements they may do multiple KSB's, next to each KSB there is evidence given with a date, the same KSB can be done multiple times over differenet placements.
I also have the other issue that there are now two different KSB lists, so apprentice that joined 2022 and before are on one list, apprentice 2023 are on another, so I need to make sure that, that is reflected in the evidence form.

So, in short hand, each apprentice does multiple pacements and multiple KSB are carried out on each placement.
That in my mind is a Apprentice to many placements, many placements to many KSB's and each KSB can have many evidence.

I have laid out what in my mind is how it should work, I look after apprentices.

The course tables work fine (right hand side), it is the left that is causing me issues.

What I am having issue with:
The right side of the database works fine, it is the left that is causing me headaches, I cannot seem to get all the different forms and tables to update as they should, I have only given the relationship diagram.

I am sure there is an easy and elegant fix, that someone who has far more experience than I, will simply say do this, and it will all work.

I think I have given all the information needed, but if there is more, then please let me know and I shall get it.

Thank you in advance.
Enizag

 

[GaP42]

Preliminary / fundamental problems:
1. The repeat structures involving KSB / Evidence / Evidence link indicate a database structure not well designed (un-normalised). Use KSB Type as a field to distinguish KSBs in the one table (a reference table for KSBs)
2. Placement/ Placement link - the data items for start and end should be in the link table as that is the instance of the association of the student to the placement - the placement table contains reference data (I think) for the available placement (locations)
3. A foreign Key to KSB appears in both the placement and placement link table: Do you have a restriction on what KSBs are allowable on particular placements?

I perhaps see a structure more akin to the following:

Think about what data belongs in the reference tables to describe the types of KSBs / Evidence/ Placements as appropriate and what needs to be placed in the "link" tables that capture the data needed for the instance of the association: eg the dates the apprentice was on the placement.
There may not be a need for EvidenceRef - as the evidence is simply a date and some notes - it appears.

 

[mike60smart]

Hi all,

I am hoping someone can save me from my complete lack of cracking this issue.

I am modelling a database from an excel spreadsheet, I have 25 apprentices all completeing their own spreadhseet slightly differently, so want to standarise, there are multiple tabs, it is espically difficult to report on everyone, currently 25 users. So, I am thinking a database is the answer?

What I have in English is:
Apprentices have to do Knowledge Skills and Behaviours (KSB - there is a list of them K1,K2...B1,B2...S1,S2... There are about 20 altogether) the apprentice changes placements every 6 months, so for each of these placements they may do multiple KSB's, next to each KSB there is evidence given with a date, the same KSB can be done multiple times over differenet placements.
I also have the other issue that there are now two different KSB lists, so apprentice that joined 2022 and before are on one list, apprentice 2023 are on another, so I need to make sure that, that is reflected in the evidence form.

So, in short hand, each apprentice does multiple pacements and multiple KSB are carried out on each placement.
That in my mind is a Apprentice to many placements, many placements to many KSB's and each KSB can have many evidence.

I have laid out what in my mind is how it should work, I look after apprentices.

The course tables work fine (right hand side), it is the left that is causing me issues.

What I am having issue with:
The right side of the database works fine, it is the left that is causing me headaches, I cannot seem to get all the different forms and tables to update as they should, I have only given the relationship diagram.

I am sure there is an easy and elegant fix, that someone who has far more experience than I, will simply say do this, and it will all work.

I think I have given all the information needed, but if there is more, then please let me know and I shall get it.

Thank you in advance.
Enizag
View attachment 109647

Hi, Are you able to upload a zipped copy of a Spreadsheet file without any confidential data?

 

[enizag]

Hi both,

Thank you for the very quick repsonse, @GaP42 , awesome thank you, I will get cracking on changing the structure accordingly.
I have not done databases in a very long time, so I am not surprised that there is a better way

@mike60smart - please find attached the current spreadsheet I am working from. Please let me know if it does not work.

 

[enizag]

Preliminary / fundamental problems:
1. The repeat structures involving KSB / Evidence / Evidence link indicate a database structure not well designed (un-normalised). Use KSB Type as a field to distinguish KSBs in the one table (a reference table for KSBs)
2. Placement/ Placement link - the data items for start and end should be in the link table as that is the instance of the association of the student to the placement - the placement table contains reference data (I think) for the available placement (locations)
3. A foreign Key to KSB appears in both the placement and placement link table: Do you have a restriction on what KSBs are allowable on particular placements?

I perhaps see a structure more akin to the following:

View attachment 109648
Think about what data belongs in the reference tables to describe the types of KSBs / Evidence/ Placements as appropriate and what needs to be placed in the "link" tables that capture the data needed for the instance of the association: eg the dates the apprentice was on the placement.
There may not be a need for EvidenceRef - as the evidence is simply a date and some notes - it appears.

Hi, thank you for the reply,
To answer question 3 - there are no limits to the amount of KSB for each placement, it could be that one placement covers all of them, but it will at least cover one.

You are also correct in saying that evidence is just a date with notes attached for that date, but it does link back to an appropriate KSB.

The above layout - there are two seperate KSB lists for the different years, which is also throwing me out.

 

[GaP42]

If there are two separate KSB lists for different years - you can set an attribute in the KSB Ref table to indicate which are applicable for a given year (or period) with a meaning like KSBs Enrolled apprentices prior to 2022, enrolled apprentices from 2023? KSB Set A / B?
If KSBs are not limited / constrained by placement then the field in your tblPlacementLink should be removed. AssociatedKSB in the Placement table - is that what was intended to use to distinguish the KSB set applicable to a placement? Not required. You can set a rule (later) which looks at enrolment date for a student and control which KSB set is available when making a Placement-KSB association record.
Just an additional thought - if some KSBs are the same between the two sets then perhaps, if you wish to avoid re-keying KSBs the file could indicate SET A Only, SET B only or Common. The rule is different to include "common" KSBs.

 

[enizag]

If there are two separate KSB lists for different years - you can set an attribute in the KSB Ref table to indicate which are applicable for a given year (or period) with a meaning like KSBs Enrolled apprentices prior to 2022, enrolled apprentices from 2023? KSB Set A / B?
If KSBs are not limited / constrained by placement then the field in your tblPlacementLink should be removed. AssociatedKSB in the Placement table - is that what was intended to use to distinguish the KSB set applicable to a placement? Not required. You can set a rule (later) which looks at enrolment date for a student and control which KSB set is available when making a Placement-KSB association record.
Just an additional thought - if some KSBs are the same between the two sets then perhaps, if you wish to avoid re-keying KSBs the file could indicate SET A Only, SET B only or Common. The rule is different to include "common" KSBs.

Hi,
The KSB lists are completely different now, so there will have to be that rule in place.
I shall have a play and see what I come up with.

 

[Pat Hartman]

Are the KSB's taken as a group so that if you have to satisfy the Knowledge KSB, you need to do all 5 assessments or could this change depending on the placement? So one placement might require only 1,2, and 3 and another might require all 5.

Do you have a sample list of placements?

I created a similar application about 10 years ago for a client which of course I can't post but I can offer ideas and help with the schema.

 

[mike60smart]

Hi,
The KSB lists are completely different now, so there will have to be that rule in place.
I shall have a play and see what I come up with.

Can you upload the current list of KSB's ?

 

[Pat Hartman]

Actually, updating the whole database would be helpful.

 

[mike60smart]

Hi,
The KSB lists are completely different now, so there will have to be that rule in place.
I shall have a play and see what I come up with.

This would be the Data Input Form you would need to cover the whole process