Help needed on question & answer EP Diagram

[MIS_noob]

Hi, guys! I'm new to this forum, and I am looking for help. To get straight to the point. For class, I have been assigned a project from a company to fix their current database system. At the moment the company is using Microsoft Excel to store data for a program that they run.

This program initially starts with a Google Form for initial interest to the program. The data is then extracted and put onto an Excel file where a worker can contact them about how serious they are interested in the program. After the Google Form, there is a paper form that they must fill out, and data is also stored within another Excel file. And after that, there is ANOTHER form with an assessment test where that data is filled out. After they are accepted into the program, their status code is then changed, and placement within the program is tracked. I am finding a rather hard time deciding on how the outline of an Access database would look. My team and I want to make it a relational database. Below is a screenshot of what we have now.

Our question is: Does the relational tables look good to you? How would we go about the answers and questions table?

 

[plog]

No it doesn't look good. 2 biggies I see:

Circular paths among tables. In a database your relationship should only allow 1 path between tables. You've created a circuit with your Placement, Person, Session Detail and Case Notes tables. This is incorrect. I don't know what the correct relationship is, but the one you have is incorrect because of this.

Field values stored as field names. Case Notes should not have a field for each month of the year. Instead you need a [NoteMonth] field which will tell you what month the record is for. That means instead of this 1 record:

personId, sessionID, ReYear, January, February, March, etc.
2, 13, 2017, "January Note", "February Note", "March Note", etc.

You would have 12 records in a table like so:

personId, sessionID, ReYear, NoteMonth, CaseNote
2, 13, 2017, 1, "January Note"
2, 13, 2017, 2, "February Note"
2, 13, 2017, 3, "March Note"
etc.

I believe your Questinnaire table has a similar issue--you shouldn't have all those Q fields, instead you should have more records.

Additionally, you should avoid non-alphanumeric characters in names. That means remove spaces and # signs. My

 

[MIS_noob]

Hey Plog,

Thank you for your quick response. I have changed it as how you suggested and more to fix the circular shape. My question is that if I link Person to Session and then Session to Case notes and Placement. Would this mean that each person belongs to only one session, and while in that session they may have many placement notes and case notes?

 

[plog]

You never simply explained what this database is to model, so I know what I know from looking at your tables. If you want to write a simple paragrah explaining what it is you are modeling (without database jargon), that might help.

As it is now, 1 person can have multiple sessions. 1 session can have multiple Case Notes and multiple Placements. And again, I have no idea what placements nor case note are.

 

[MIS_noob]

Sorry about that. I think explaining it without database jargon will be easy since I am fairly new to this database scene.

A person will apply to this program by asking a list of questionnaires which includes tons of questions such as; their past employment, barriers to employment, and demographic questions. There are give a take about 50-80 questions total or so. This person is then contacted and notes are given upon this person (I have yet to make a table for regular notes). One person is able to get into one Session. Through this session, they are placed in a workmanship. The placement table represents where they wanted to be placed and where they are actually placed with other information. The case note is monthly updates after a person finishes the program. The answer table represents the answers that are given correlated to the question by each person. I hope I did well to explain this in a simple manner. Let me know if there are anything else I can provide to give you a better understanding of what I am trying to do.

After reading what you have stated I am thinking of just having the sessions table, placement table, and case notes table just connect to the person table to look more like a star.


EDIT: Uploaded how I believe it should look now actually

 

[plog]

The Question/Answer portion looks good. CaseNotes looks good. But 1-1 relationships aren't usually a good idea. I mean, in those instances, you just put all the data in one table, instead of another one.

If a person can only have 1 session, then the session data just needs to go into the Person table. Same thing with placements.

 

[jdraw]

MIS noob,

Instead of guessing what the tables and relationships might be, spend 30-60 minutes working through 1 or 2 of the tutorials from RogersAccessLibrary recommended in this attached link.
The other materials on database design, models etc may also be helpful to getting started.
Good luck.