Table/field design (1 Viewer)

I have a question about table design. I will be using Access 2010 or 2013, that shouldnt effect my question.

My buddy and I are designing a database at his school that is going to collect information on students like how often they use their phones in class and other issues that come up. We are also setting it up to keep track of when a parent is contacted and by which meothod they are contacted.

Having created a cople databases i am familiar with some concepts. The issue for us is the same for a couple bits of data we are collecting. We have seperate tables for students and parents. To me it seems better to have new records for each parent. So 1 kid 2 parents is 1 record for the student table and 2 for the parent table. Since normalization is important. My question is if a parent has more then 1 kid, should the tables be related by a field that is a multi select. This way multiple kids are attached to one parent.

The same issue comes up with creating records for instances with issues with students. If on the students table there is a field for issues, should it be a multi select so as to tie issues to that specific student?

Of course the primary key would be the related field. There may have been a third place where I feel a simular concept is used. What I am trying to figure out is since parents can have an indetermined number of kids, how does one best organize an undetermined number of variables as this. I know setting a table up with fields of kid1, kid2.... kid9, kid10 is ridiculous.

I am have used multi selct fieds before and found them to be useful. Since this database may use a few of them I wanted to see if better ideas were possible. As I said we only have Access to work with, other platforms may be better suited but not a tool we have. Any help is appreciated, thank you.

youd have a tIncident table
IncID (auto)
StudentID (long)
IncName (text what happend)
IncDate (date)

this would be a child of tStudent table, so you can see a history of each student's incidents.
You can also have items to pick from. Dbl-click a list, to run a query, to add incidents to this table.

Similar to:

Structurewise, to accomodate all permutations of child/parent relationships, I think you need 3 tables:

tblStudents - will hold the students data
tblGuardians - will hold their guardians data
tblStudentGuardians - will be a junction table that manages which students go to which guardians.

Further to plog's post, I think you should try to describe " other issues that come up". It seems your primary interest is Student(s). So you'd have a tStudent. You seem to imply "issues", so I'd suggest a tIssue (probably tIssueType). One IssueType seems to be PhoneUsage. And a tParent or tGuardian.

tStudent info about the student
tIssueType info identifying issue types


Student has 0,1 or more Parent/Guardian
Student has 0, 1 or more Issue
Issue is 1 IssueType

I'm not exactly sure what you mean with

I am have used multi selct fieds before and found them to be useful.

Click to expand...

. If you mean lookup fields at the table field level, I would avoid that approach. Also if you mean multi-valued fields I would vote against that.
I suggested tIssueType as a lookup table.

Good luck with your database project

Ranman256 - thank you for that thought. I am not sure why I had that more complicated in my head. That concept actually helps across other areas. As well. For some reason, I hadnt considered simply having a field for students in the table issues. It makes perfect sense at this point.

plog - thank you. So what you are saying is forgo the thought of setting up a relationship between the student and parent/guardian table and instead creat a 3rd table that instead has the two tables related in individualized records. I have never considered using a third table as a way to store related information. That is definitely something I will keep in mind going forward.

Jdraw - Other issues that comes up would be maybe like disrupting a class,leaving class early. I do not have predefined set of parameters. Should we expand on issues that can be selected, I agree a table with issues is the best way to allow expansion without needing to modify a field set for a combo box based on hard set parameters.
You mention:
[QUOTEStudent has 0,1 or more Parent/Guardian
Student has 0, 1 or more Issue
Issue is 1 IssueType[/QUOTE]
I honestly have no idea what you mean. I could just be missing the obvious as its been a little while since I have designed anything.
To answer your question about multiselect fields, I used them before when relating 1 record to multiple records. So tRequest was the parent table and tEquipment was the child. One record in tRequest was related to multiple records in tEquipment. The reason is that tRequest was a work requested table. Each record was for new work requested. Each work request used several pieces of equipment. Now, plog had suggested a 3rd table which simply wasnt an idea I had come across in development before so I created a field that had the option to select multiple items. I never had issues with queries or anything. So, I found it to be useful to have the multi select field. That said, going forward I am more likely to use the third table idea going forward.

Thank you all for your help. A much needed outside view on the problems encountered that are much more cleared up. The help here has put things much more how I imagine they should look.

Dinger_80,

I honestly have no idea what you mean

Click to expand...

.

Hmmm. I was suggesting some business facts from my understanding of your post/requirements.
The business facts are critical to designing tables and relationships in your database to support a business.

I recommend you work through 1 or 2 of these tutorials to get experience with database and table design. Each will take about 30-45 minutes, but you will learn a repeatable process.

Class info system
Catering Business

Good luck.

Here is a link I often suggest to people looking for info re database design and planning concepts.

Students, Teachers, Administrators, and Guardians are all people and in some cases one person can be all of those things So I normaly use a single table for "people" and then use a junction table to assign roles so that one person might have multiple roles. If that makes your head spin, stick with the two tables you have currently.

The relationship between students and parents is many-to-many. One student may have many "parents" and one parent may have many students. To implement this relationship requires a third table which is usually referred to as a junction table. It would look something like:
tblStudentGuardians:
GuardianID (PK field 1, foreign key to tblParents)
StudentID (PK field 2, foreign key to tblStudents)
GuardianType (use if you want to know who is mother, father, grandmother,etc)

Keep in mind when you join these three tables, you will get multiple instances of the same student if they have multiple guardians. This isn't a problem. You just have to know it will happen and use subforms and subreports to keep it from affecting your main forms and main reports.

A similar situation exists for Incidents. There will be a many-to-many relationship between students and Incidents. But this time, you'll want to capture additional information about each incident.
tblStudentIncidents:
StudentIncidentID (autonumber PK)
StudentID (Fk to tblStudents)
IncidentID (FK to tblIncidents (define the names of incidents you want to track))
IncidentDT
Desc
ReportedBy

And you will also need at least one or more tables so you can record meetings, phone calls, etc with the guardians and students.

I've done a set of YouTube videos on the many-to-many relationship between students and hobbies, you can find them on my website here:-

Many to Many Relationship

The many to many term to describe this is a bit misleading, it does describe the junction table very well, in that you have many students and from my example, many hobbies. But this is an explanation of the layout of the table, it does not do Justice to what is actually happening. In essence, it is two "One to Many" relationships, essentially they are the reverse of each other. One of the "One to Many" relationships is the opposite of the other version of the "One to Many" relationship. I demonstrate this in the videos.