Results 1 to 12 of 12
  1. #1
    BaaBaaBristol is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    11

    Design query - setting up relational tables

    Good morning,



    Okay - I am relatively new to Access, but understand some theory behind relational tables and the principles of good design. However - I would really appreciate some guidance or comments on a project I have been asked to complete.

    We have a new project starting within a Local Authority that deals with helping Young People into employment. At the moment, we need to collect basic data on participants in the project to keep for reporting, but I also want to link it to tables that include geographical/demographic data, info on vulnerable groups, and links to other projects that they will be referred to. This will allow for expansion and links to other project data for more flexible reporting.

    So - I have attached pics of the tables I envisage being part of the database. I have a Participant table that includes basic data we need to keep, as well as fields that I want to link to other tables. In the District Code and Engagement columns, you will see a number. I though this would link as a secondary key (is that the right terminology?) to the Engagement and District tables (also attached as an example), so that they can be updated and always carry a numerical code that links to the Participant table to avoid duplicating data. This can also be done for other characteristics that would also have their own tables.


    One issue I have is that the details in the Pathways table show projects that participants can be referred on to. However, there could be multiple projects. So - is it better to have checkboxes (Yes/No) to give all the possible outcomes of combinations of projects and they all have a number that could then go in the fields in the Participant table?

    I hope this is vaguely clear - any comments will help me shape this in more detail and will be very gratefully received.

    Best wishes,

    Ben

    Click image for larger version. 

Name:	DB_Engagement1.jpg 
Views:	46 
Size:	113.5 KB 
ID:	44977Click image for larger version. 

Name:	DB_ParticipantTable1b.jpg 
Views:	44 
Size:	111.7 KB 
ID:	44978Click image for larger version. 

Name:	DB_ParticipantTable1.jpg 
Views:	44 
Size:	110.1 KB 
ID:	44979Click image for larger version. 

Name:	DB_Pathway1.jpg 
Views:	45 
Size:	109.0 KB 
ID:	44980

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    For consideration:

    -Identify and write a description for each of the "things"/entities involved. What exactly is an Engagement? Vulnerable group?Other Project?
    -What is it that relate these "things" and How do they relate? 1to1 1to many? Business rules?
    -Create scenarios
    ---- What does a Participant do (sequential steps) to be assisted?
    ---- What specific info should/must be recorded at what step(s)?
    ---- What conditions exist to move from one step to another? And what should happen if a condition isn't met?
    ---- What reports are envisioned? What data do you need for each report? Confirm the data is recorded/acquired.

    My guess is that you may be getting into physical tables before getting a look at some feasible approaches.No problem with testing a few options; mocking up situations--But be sure you do the analysis before getting too deeply involved in physical database.
    See my stump the model for more info.

    Good luck.

  3. #3
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,549
    Hi Ben

    Can you upload a zipped copy of the database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #4
    Join Date
    Apr 2017
    Posts
    1,667
    1. Engagement field in participants table contains 2 different bits of info: what are participiants readiness for work, and participiant having any benefits or not. You'll be beter of, when you split this info to 2 different fields - e.g. EngagementStatus and HasBenefits (the last one may be a int field limited to values 1 for participiant having benefits, and 0 for contrary);

    2. It is not essential, but my advice is don't use table or field names with spaces in it (when you do, you have to wrap those names in square brackets whenever you refer to table/field in code or queries - and belive me it can get tedious). So instead of [Participiants details] using e.g. ParticipiantDetails will be much more comfortable in long run;

    3. About having different projects for same participiant: You need a Projects table like tblProjects: ProjectID, ProjectName, ... (which replaces current Pathways table), and a table like tblParticipiantProjects/Pathways: PathwayID, PaticipiantID, ProjectID.

  5. #5
    BaaBaaBristol is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    11

    Design query - setting up relational tables (with attachments)

    Database.zip
    Please now find the database attached.

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,549
    Hi Ben

    Your relationship diagram would be along these lines.

    It needs a bit more work as we don't yet know your full business process.
    Attached Thumbnails Attached Thumbnails ER.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #7
    BaaBaaBristol is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    11

    Updated database files and design

    Thank you to everyone who has given advice on this. I have particularly enjoyed the Stump Model and will use it now I have set up a Working Group to help look at the process of data management for the project.

    I have updated the database again following some work today, which I have attached (Database_v2)W4Y Database Business Rules.zip, along with Business Rules; I typed up a background, process rules and the project monitoring criteria for reference.

    What I need to consider next is how we track showing how 75% have engaged with onward training opportunities separately from 35% into work, and also how we might show more progression beyond just the 'exit route' into the first stage of their development (which might be enough for now).

    If anyone could advise with a new relationship map, that would be really helpful.Mike60smart your previous model was really helpful, thank you.
    Attached Files Attached Files

  8. #8
    BaaBaaBristol is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    11
    W4Y Database Business Rules.zipSorry, Business Rules didn't attach.....

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,549
    Hi

    The database you have attached this time does not contain any of the suggested changes from my ER Diagram??
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  10. #10
    BaaBaaBristol is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    11
    Hi Mike,
    I thought I would complete the business process and put some more planning into what we would need to report (sorry, this has been thrown at me very quickly by the project team with a quick turn around). The business plan and some of the ideas for new fields are now what I need to refine the design from.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Again for consideration:
    -set up a few scenarios to show what happens and when;
    --a new participant enters your "set up", then ???
    --another participant -trace through the steps -- what gets collected/where is it recorded

    --you might try describing a day at the office to put the pieces into context.

    -how are you going to monitor/report outcomes
    --seems you need some follow up interviews
    --what exactly would follow on training involve? how much of that is in scope of your application
    --do you have processes to follow and record participant status once they have received your initial assistance?

    If you provide info for the above, readers will help you with a model. A brief /overview model can be fleshed-out.
    Good luck.

  12. #12
    BaaBaaBristol is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    11
    Thanks for that.
    Well, the information we need to collect is all in the tblParticipant table. And actually, some of the onward information requirements are vague, I have just included what I think our team would benefit from collecting moving forward. To answer your questions and provide more context:

    -- How are you going to monitor/report outcomes?

    A: Honestly, I don't know. There will be a return to funding bodies and government and I will need to design queries to generate reports once I know exactly what it is. I have basically created tables and fields to cover everything I think we need to query for central government bodies and for our own development. We need to be able to show at a basic level:
    * How many participants engaged
    * That participants have an action plan and have completed a diagnostic assessment
    * The proportion that have engaged in some form of onward progression: training, work search, education
    * The proportion that have engaged in employment (this has to be a separate criterion to the previous onward progression)

    We also need to hold the basic details in the attached DB in terms of contact details, DoB, locality etc.

    We also need to track and demonstrate engagement with a certain number of employers, which is why I have separate employer tables.

    Now then....in terms of onward progression, I foresee an issue with being able to record beyond initial progression out to a first project, because a participant could go on to two or three sets of training, education and job search activity with a Mentor before finding a job, which coujld be part of their action plan and ultimately we should know about. I need to work out how we get this information passed to the Database - does a Job Coach/Mentor do this at each stage of the plan being completed? Will a Form need to be created for this?
    These are questions for further down the line.

    In terms of processes to follow - no, they are being created, possibly with my steer using the Database to show need for the process to be created in a certain way. Participants can self-refer in person, by phone, through a web portal that is being built at the moment.....these aren't in place yet, the project is that new!!

    That's where we have got to so far........

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Relational Design
    By mainerain in forum Database Design
    Replies: 10
    Last Post: 03-16-2021, 02:32 PM
  2. New Relational Database design
    By JonathanT in forum Database Design
    Replies: 27
    Last Post: 02-05-2020, 07:41 AM
  3. Replies: 3
    Last Post: 10-18-2017, 04:01 AM
  4. Setting up relational database
    By aceoftrades in forum Access
    Replies: 1
    Last Post: 02-22-2012, 08:42 AM
  5. Relational Database Design Questions
    By mribnik in forum Database Design
    Replies: 40
    Last Post: 08-09-2011, 02:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums