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

 

[Pat Hartman]

It is always helpful, even if you are working alone to work with a whiteboard if you have one or directly on the wall if you don't. Use postit notes because they're easy to move around.

Start with just the name of the entity as you have. Then add attributes as you think of them. As you work on relationships, that's where the whiteboard comes in handy since you can draw connecting lines. You can't do that on your wall because you can't erase them if something changes.

Once you are happy with your initial design, start building tables. Every table needs a primary key. Generally it will be an autonumber and the name will reflect the table where it is defined. So tblPersons would have an autonumber PK named PersonID. In your case, you have an Employer table and a Mentor table. Mentors are related to one and only one Employer. EmployerID and MentorID would be good names. After you have entered all the tables, to to the relationship window and create the relationships. Relationships are an integral part of the design and need to be defined as early as possible. Adding them later will cause problems, especially if you already have data in the tables.

Remember, relationships ALWAYS go from the child to the parent. So the Mentor table points to the Employer table and that means that tblMentor needs a foreign key field named EmployerID or EmployerID_FK as you prefer. Being consistent with naming helps greatly once you start connecting the kneebone to the thighbone

Here's where you get your first 2 lessons - Consistency is your friend. If you decide to use certain abbreviations, ALWAYS use the abbreviation. But be careful with abbreviations because they will make the application harder to understand for someone not familiar with the business process. So, it is a trade off - less typing for potential confusion. No one wants to see object names that are 40 characters long and filled with special characters. And the second lesson is to limit your names to letters and numbers and the underscore. Do not use any other characters. And, numbers are suspect because they tend to be indicators of repeating groups. Child1, Child2, Child3, ... A person might have any number of children and so making a bunch of columns is the wrong design pattern. You use a child table where each child is a row so you can have an unlimited number.

At this point, you should probably post the design for review.

 

[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.

 

[Pat Hartman]

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]

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.

 

[Pat Hartman]

For one of my employers, I managed the interns we took from local Computer schools/colleges. For me, it was more work than it was worth. How much value can you get from someone who is only with you for a month or two at the most and who knows nothing about your industry and very little about development. But, it was a community outreach activity rather than one which got us "free" labor. Based on comments from our interns, they learned a lot. I'm the one who had to figure out what we needed to teach them and what kinds of work we could allocate to them. I always gave them new development (usually reports) as well as maintenance work (changing logic in existing programs) so they could see the types of things they would do in the real world. Sometimes I was even able to allow them to participate in customer interviews so they could see the design part of the process but they were only onsite for less than 10 hours per week so that wasn't always feasible.

There were five other managers in our IT department. I always took at least one of the interns and I gave the other applicants to other managers who were willing to help. It was a volunteer assignment so the managers were not required to participate (although it did look good at review time). Most years we were able to accomodate all of the applicants we got but sometimes we had to pass on a couple of them.

 

[Pat Hartman]

I thought the mentor program contacted various outside employers.

It does but the mentors are employees of the companies who participate in the program. I'm assuming that this application would be run by the school or maybe state agency so they could see where their students/clients got assigned to the local 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

 

[Pat Hartman]

Job may or may not need to be its own entity within the schema,

Not from the perspective of someone who has managed a similar program. The interns are not employees. There are job descriptions though which describe in general the type of work they will be doing on a given assignment.