Table/field design

[Dinger_80]

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.

 

[Ranman256]

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:

 

[plog]

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.

 

[jdraw]

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.

. 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

 

[Dinger_80]

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.

 

[jdraw]

Dinger_80,

I honestly have no idea what you mean

.

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.