I'm confused about normilazation..

[Whack]

Thank you CJ.

I think I'm starting to see...

[Whack]

Ok, I think I've got my head around it... perhaps I can get another look...

This works for a single advocate only. Not an office of advocates

1. Everything is based off the "tbl_student" The Tables are:

tbl_Student <- Contains only Students
tbl_Guardian <- Contains only Guardians
tbl_Address <- Contains only addresses for Students, Guardians, Schools, SchoolDistricts, Attorneys

tbl_Phone <- Contains only phones for Students, Guardians, Schools, SchoolDistricts, Attorneys

tbl_Email <- Contains only Emails for Students, Guardians, Schools, SchoolDistricts, Attorneys

tbl_SSN <- Contains only SSN's for Students and Guardians
tbl_Schools <- Contains only School Names For Districts
tbl_SchoolDistrict <- Contains only School Districts

=========== tbl_Student =========================

The tbl_Student has many to many relationships with many other tables.

Each record in tbl_student may have 1 or more guardians; so theres a 1 to many relationship with the tbl_guardian (jct_Student_2_Guardian)

1) Each record in tbl_student may be attending 1 or more schools; so theres a 1 to many relationship with the tbl_School via a junction table (jct_Student_2_School)

2) Each record in tbl_student will have 1 or SSN; so theres a 1 to many relationship with the tbl_SSN (jct_Student_2_SSN)

3) Each record in tbl_student may be attending 1 or more school districts; so theres a 1 to many relationship with the tbl_SchoolDistrict via a junction table (jct_Student_2_SchoolDistrict)

4) Each record in tbl_student may have 1 or more Addresses; so theres a 1 to many relationship with the tbl_Address via a junction table (jct_Student_2_Address)

5) Each record in tbl_student may have 1 or more phone numbers; so theres a 1 to many relationship with the tbl_phone via a junction table (jct_Student_2_Phone)

6) Each record in tbl_student may have 1 or more Email Addresses; so theres a 1 to many relationship with the tbl_email via a junction table (jct_Student_2_Email)

7) Each record in tbl_student may have 1 or more Attorneys; so theres a 1 to many relationship with the tbl_Attorney via a junction table (jct_Student_2_Attorney)

8) Each record in tbl_student will have 1 or more entries in the tbl_StudentRecord ; so theres a direct 1 to many relationship with the tbl_Student and Tbl_StudentRecord

=========== tbl_Guardian =========================

1) Each record in tbl_Guardian may have 1 or more students; so theres a 1 to many relationsip with records in the tbl_student via a junction table (jct_Guardian_2_Student)

2) Each record in tbl_Guardian may have 1 or more Addresses; so theres a 1 to many relationship with the tbl_Address via a junction table (jct_Guardian_2_Address)

3) Each record in tbl_Guardian may have 1 or more Phones; so theres a 1 to many relationship with the tbl_Phones via a junction table (jct_Guardian_2_Phones)

4) Each record in tbl_Guardian may have 1 or more Email addresses; so theres a 1 to many relationship with the tbl_Email via a junction table (jct_Guardian_2_Email)

5) Each record in tbl_Guardian will have 1 or SSN; so theres a 1 to many relationship with the tbl_SSN (jct_Guardian_2_SSN)

=========== tbl_Attorney =========================

1) Each record in tbl_Attorney may have 1 or more students; so theres a 1 to many relationsip with records in the tbl_student via a junction table (jct_Student_2_Attorney

2) Each record in tbl_Attorney may have 1 or more Addresses; so theres a 1 to many relationship with the tbl_Address via a junction table (jct_Attorney_2_Address)

3) Each record in tbl_Guardian may have 1 or more Phones; so theres a 1 to many relationship with the tbl_Phones via a junction table (jct_Attorney_2_Phones)

=========== tbl_SSN =========================


1) Each record in tbl_SSN will have 1 SSN relating to a Guardian; so theres a 1 to many relationship with the tbl_Guardian (jct_Guardian_2_SSN)


2) Each record in tbl_SSN will have 1 SSN relating to a Student; so theres a 1 to many relationship with the tbl_Student (jct_Guardian_2_SSN)



And again,

Thanks in advance for everyone's help, assistance and patients
---Whack

[twgonder]

I'm late, but here's my 2 cents, since I'm doing a similar application. I'm using simple phrases to get the idea across, first the problem idea, then the fancy stuff. Your pretty close in what I would design, I would just get rid of a few duplicate tables and use more many-to-many tables.

First, create one "Entity" table that contains people and organizations. They all have similar attributes like a name and creation date (DOB).
Do not put addresses and phone numbers in the "Entity" table. A Person has an address, but they are not an address.
Now create a table for addresses. Only put address information in there.
Create a phone table. Only put phone data there, which can include a FK for what kind of phone it is.

Why?, multiple children and guardians may all share an address and phone number (think landline). You don't need to duplicate the same address or phone number 2 or more times in different tables for different people.

Now create a relationship table where you say what is the relationship (the many-to-many link) between the Entities (people/organization). Eg. Ted is the guardian to Sam (the student). This way Ted can be guardian to more than one student. And Sam can have more than one guardian. Sam can be a student at Happy Days School. Sam could be a student at more than one school and Happy Days School can have more than one Student. The District can be just another Entity. It has a one-to-many relationship with the schools under it.

I think you want the student to be linked to a school, not a district. The school is then linked to a district.

Without getting into a separate Zip code table (and the like with phone numbers), I think I could do your design with 8 tables if you wouldn't need multiple notes for any of the Entities (e.g. I do allow multiple notes in their own table for an Entity, but have only one note for each relationship).

Think students and guardians with this.


Think of Students and schools with this. Also, Schools with District. Office with attorneys or advocates. Etc.


A query showing the relationships:


And how we got there:


Once these screen shots make some sense for the Entity and Relationships, then you can repeat for the phone and address tables.

[twgonder]

Hmmm, looks like another of my posts that was there went into the ether.
Anyways, I don't know about identifying as a car,
Does anyone remember the TV show My Mother the Car?

Last time I checked, here are your new-world gender identities demonstrated by my fictional Latina, Zelma:



Now if Zelma insists on identifying as a car, and there is no talking her out of it, then in 30 seconds we got her covered:

[CJ_London]

You are getting there, but you might want to consider the 'entity' approach mentioned in post #2 might be more appropriate. Also see the example provided by twgonder.

students, guardians, advocates, etc are all people, so providing these are all distinct (i.e. a guardian cannot also be a student or advocate) you could have an entity table called tblPeople which includes their personal details plus a field to indicate their type or role. There may be some personal details not required for all roles (for example you probably need to know the date of birth for a student but not guardians or advocates), these can easily be hidden or disabled when you come to design your form.

Per twgonder's example, you then just need the one table for addresses and another for phone's and another for emails (tho, again, these can all be combined into one table if you wanted, with a 'type' field and ignoring fields not required for certain types) plus the joining table

so you have

tblPeopleTypes (lookup table)
tblContactTypes (lookup table)

tblPeople (data table)
tblContactInfo (data table)

tblPeoplePeople (junction table)
tblPeopleContactInfo (junction table)

If you can have a person with multiple roles you would need another table

tblRelationshipTypes

you would remove the role from the people table and and include the role FK the the tblPeoplePeople table instead (although you could leave it in tblPeople as a 'default' type).

So your table structure might look something like this

[twgonder]

Look at that (in post #20) CJ_London also has 8 tables! We think more alike than he would care to admit. I just would split the ContactInfo for phone and address. You can continue with an Internet table that contains emails. An email, phone and address are all different in their data structure. Also, everyone in the family may have the same address for home, and share the same landline phone. But each may have a different cell phone number and email address. It makes no sense to duplicate the home address into multiple contact records when there are different email and cell phone data for each person.

I use the Element table as sort of a Do-All-Helper table to avoid dozens of little lookup tables. E.g. look at my Entity Form in post #18. The fields Real or fictitious, Type/Ethnicity, Gender identity, Name title, Name suffix are all related to the one Element table. That's kinda advanced stuff that took me quite some time to get working for forms, combo boxes, etc., but you can think on it. Or have dozens of little lookup tables and their respective forms, reports, menus, etc. ... Yuck! (a lot of work too).

A final note on naming. The method of naming for primary keys shown by CJ makes good sense when reviewing the relationships (i.e. different names with pk & fk). But this becomes a nightmare if you use helper modules for forms or reports. I've been working on a shell that sits upon Access, so I don't have to keep writing (or copying and maintaining) the same VBA code over and over again for each form and report. Look at the second image in post #19. Do you see all the commonality in the titles, colors, command buttons, etc.? That's not by accident. One big helper module does 90% of the work. It wouldn't work if every table had a different name for the primary key (and this extends to lots of other fields, I can't use Nm1 in one table and Name1 in another table if I want a procedure to format the Name by just passing the table name).

Here's an example where another set of name fields can be used for an Entity, in table Entity Other Name (EntityOthNm):

[twgonder]

Another note based on post #17. Depending on your needs, you may or may not need a SSN_Table. I put a code (Display code) in the Entity table for quick lookup (I may have hundreds of John Smiths). I let the user decide what to use. In some cases it may be SSN (or cédula in Latin countries), or maybe drivers license as you can see for Zelma in post #19 (cadl = California drivers license). If I need to put a bunch of identification and credential information in for an Entity, then I use the Identification Form which has a one-to-many relationship with the Entity table. If you are always in the USA and only using SSN, then just enter the SSN with or without dashes (your choice {you can't anticipate when the SSA may one day change the format for new tax recruits to NNN-NNA-NNNN - a new Y2K kind of problem}). In Colombia, they've changed the format for cédulas a few times, I even have a duplicate number to someone else. Go figure. Oh, and another house down the street has the same address!