Guidance on a new Access database

[oni1983]

I am a new user and I am currently working on an Access database to have all information on a mentoring project in one place.

So basically the below is the information we need to keep:

Placements - is unique to each client and employer...if the same client is matched to another employer because the first employment was not successful, then a new placement is created. The same goes for the employer.
Appointments - each placement can have a maximum number of 8 appointments. Information like time and date, location, and what was discussed, if mentors spent money (they will be given an allowance if they need to meet clients say at a coffeeshop) needs to be kept here.
Clients - information on clients like name, surname and contact details
Job - the details of the job i.e. hours of work, job title, job description
Mentors - the persons who will be mentoring clients during a placement. Mentors can have various placements to mentor.
Employer - employer contact details, location, etc

How should I go about it?

 

[GaP42]

You have some basic ideas of the objects and data about those objects that needs to be collected and managed. You would do well to perhaps detail in lists in Excel under headings for each object what data items you will need to store and maintain. A KEY item for each object will be an identifier that can be used to refer to any row of data about an object in a table. For each item think about/record the type of data (text, integer, date). Don't forget about any categorisations you may need.
As you compose this list expect it to undergo iterative changes. You also need to think closely about relationships between the objects, defining at least in part, some fundamental business rules:
A Placement is where a Client is employed (assigned a Job) with an Employer
A Mentor is assigned to a Client in a Job
A Client may have one or more Placements
A Mentor may have one or more Placements
A Placement may involve one or more Appointments

A Client has a Job with an Employer (for a period of time) for which they (are being / are to be) mentored?)
An Employer may have many Clients employed in Jobs. Each of the Clients has a Mentor.
Are Mentors employees of the Employer or can they be independent of the Employer? What data needs to be held about Mentors? Is this much the same as clients? Can Clients become Mentors?

Using the above you are then ready to define your table structure for the database. However you would do well to have this reviewed, perhaps by posting your design here. Use the Access Relationships tool to define and show how the tables relate to each other and post a picture of it here.

As new user - are you new to databases or just new to Access, or new to the forum? If a real beginner you have a learning curve to climb. Relational Database design is a skill that requires understanding of database normalisation techniques. Access is a database based on relationships. It is best to have the foundations of the database design "correct" before you build the application on top - the queries, forms and reports.

EDIT: BTW I assume this is a new business process you are defining - you do not have a paper-based operating procedure you can use to identify the inputs/outputs and data needed.

 

[LarryE]

Please confirm:

1. Each mentor may have multiple clients
2. Each client may have multiple appointments
3. Each appointment may have multiple placements
4. Each employer may have multiple jobs

 

[LarryE]

@Pat Hartman and @GaP42
I thought maybe a design something like this:

where the appointments and jobs join in a junction table Placement. The PlacementResult field could be a field to note if the placement was successful or not.

 

[LarryE]

Thanks for taking the time to create a schema @LarryE I see it a little differently.

Appointment should not be between placement and job. It should be a child of the Placement junction table. So the FK in the appointment table should be PlacementID. and AppointmentID should be removed from the placement Table.

Mentor is related to Employer and needs EmployerID as a FK. Also, Mentor should probably be the FK in Placement rather than JobID. Because each Placement connects the Client with a Mentor. Job is also probably part of Placement rather than a separate table. So it would be Employer-->Mentor-->Placement<--Client
Placement-->Appointment
View attachment 114360

Oh I thought that Employers were independent of the mentors and clients. I thought the mentor program contacted various outside employers. OK. That makes sense. I didn't realize that the mentors were inside the employers.

 

[GaP42]

Thanks for taking the time to create a schema @LarryE I see it a little differently.

Appointment should not be between placement and job. It should be a child of the Placement junction table. So the FK in the appointment table should be PlacementID. and AppointmentID should be removed from the placement Table.

Mentor is related to Employer and needs EmployerID as a FK. Also, Mentor should probably be the FK in Placement rather than JobID. Because each Placement connects the Client with a Mentor. Job is also probably part of Placement rather than a separate table. So it would be Employer-->Mentor-->Placement<--Client
Placement-->Appointment

@LarryE , @Pat Hartman - I hope the OP appreciates the fundamentals developed/presented here by yourselves - it looks good.
Depending upon answers to some of the questions raised, I do think the schema from Pat below covers the starting point. Some outstanding points (some minor) are:
- Mentor is only known by name - the OP may need additional attributes to record contact information. That may mean that some attributes for Employer may be transferred to the Mentor - depending upon the needs of the OP.
- As the full context is not provided, I had a question raised from some experience, whereby a within a (large) employer organisation, HR encouraged certain employees to identify and meet with an internal Mentor for self-development over a period of about a year. Perhaps, depending on the flexibility of the design, both Mentors and Clients may have details recorded in the one table (as Person) that then participates in the programme as a Mentor or a Client as needed.
- Job may or may not need to be its own entity within the schema, depending upon whether it is a formally recognised position within the employer's structure eg a position code, hours, .... and I assume is describing the Job the client is assigned in a placement. Job then would be a table relating Placement and Employer. A Job, over time, may be assigned to multiple Clients (as they rotate from one placement to another).
- and to extend some of the OPs thoughts: What technical requirements are needed to be supported: eg. will the db be multi-user, requiring user logins? If multi-user, does the date and user who last edited the record need to be noted? (Red-Tape fields - simple to add a CreatedDate, UpdateDate, UpdatedBy attributes to all records). I assume the OP has / will consider other matters concerning hosting, size.

As a beginner? KISS especially should apply.

View attachment 114360