I can't figure out how to have two addresses connected with one or more students.

I have:

tblStudents
IDStudents - PK
Lname
Fname
etc.

tblAddress
IDAddress - PK
Parents
Street
city - - - - - FK to tblCity
phone
cellphone
email

Now, due to divorce - each student could have more than one address, with one being a primary address and one being a secondary. Each address can have more than one student, with siblings.

How do I set up the relationships and link the tables together. I thought of having a FK field under Students PrimaryAddress and another one SecondaryAddress, but you can not have two fields in one table linked to the same table.

Tim

You have a many-to-many relationship between addresses and students. So, you need a junction table between them like below:

tblstudentaddress
studentaddressID (autonumber PK)
studentID (FK to students table)
addressID (FK to address table)

Your tblAddress should not have Parents in it. Parents should be in a separate table. The address table should have only stuff pertinent to an address, such as street number, street name, city, etc.

Okay, I can easily create the junction table, but I have a question about why Parents should not be with Address.

If I create a table with Address and Parents there is no repeating data and the information is all realated. Why should they be seperated?

Tim

Parents should not be with address because parents are not an attribute of the address. Can't the student live alone or with somebody other than the parents?

This Database is for tracking data on kids ages 3 to 10 years old, so a child can not live alone and if they are living somewhere other than their parents, I enter another address for that child with the Guardian for that location.

Parent
Foster Parent

or

Father
Mother

so I could have two or more addresses for each child, but every address would have a parent figure attached to it.

I did seperate out parents, however, because it did occur to me that I could on very rare occasions have two different families living at the same address, but over the last 12 years I think that has only happened to me twice. So if I kept the two together it would never create repeated data for more than one or two records in the database at a time.

Tim

It's situations like the 'once or twice' that you mentioned that make it necessary to normalize your database. If you get the tables right from the start, the whole system is much more flexible and allows itself to scale as the requirements put on the system increase over time.

It's not the repeated data that cause the need to keep the parents separate, it's what balaji mentioned, in that the parents do not, in any way, describe the address.

So, rather than parents, what you are tracking is the name of the adult associated with that address. Maybe you should rename that field to reflect that.

Hi,
Here's my logic (sometimes does not serves me well tho) on this, but I present it anyway.
I don't see M:M between Student and Addresses.Student can be at, or be at many addresses, but can't never be at more than one address at the same time, like let's say,products in warehouse being in more than one location.
So based on my logic above I'd envision something like this.

tblStudents
StudentID PK
StudentFName
StudentMInitial
StudentLName
and all other requir. attr. about student

tblStudentAddresses
AddressID PK
StudentID FK
AddressTypeID FK (Primary, Secondary)
AdultID FK (Parents, Father, Mother, Legal Guardian, Foster Parent)
Address
City
State
Zip
Phone
CellPhone
Email

tblAddressTypes
AddressTypeID PK
AddressType

tblAdults
AdultID PK
Adult

Students may never be able to be at more than one address at once, but I MUST keep track of both parents (gardians) associated with a child, for I sometimes have to contact one or the other. Also, I have students that are at one house the beginning of the week and another address the end of the week. Some data also has to be mailed out to both of the Guardian addresses.

If I follow what you recommended, I think I would link the Parent Table to your StudentAddress table.

Tim

Quote:>>If I follow what you recommended, I think I would link the Parent Table to your StudentAddress table.<<
That's what I did. My table for parents or guardians is called tblAdults. You have to distinguish at what address is what parent and what address is Student primary residence.If the parents are divorced and mother has full custody let's say this address would be primary address,AdultID(Mother) and her address info. Than you'd have secondary address with AdultID(Father) and his address. If student has both parents and they all live in same address you'd just have just one address for that student with AdultID(Parents)
There are some kids raised full time by Grandparents, Aunts and other who could be in tblAdults and as primary address of student.
Hope I am describing this clear enough.

Your relationship of Student to Address is not a direct relationship.

A Student is related to an Address through their Parent/Guardian.
A Parent/Guardian lives at an Address. A Student lives with a
Parent/Guardian.

So no matter how you decide to model an Address you do not need
to have a direct relationship between Address and Student.

Many of these design issues depend on your "business rules". You need to make tough decisions on what can or cannot happen in the future - always tough calls because the rules seem rigid. But rules change and business rule changes after the app is done are more expensive to implement.

If students can NEVER live at two addresses at once and you need to track multiple guardians then I'd suggest a one-to-many relationship between student and addresses. BUT one (and only one) address can be designated as the "primary" or "main" address. "Guardians" and "Address" are probably the same entity; I couldn't quickly think of an exception.

I can't think of a way to enforce this at the database/table level (right now) but certainly your application logic can enforce this requirement through the form interface.

Use form events to call code that ensures that only one "Guardian"/"Address" can be designated as the "Primary" one.

Amen to that.
To bad they are missing from these threads more often than not.

Hi ace. Agreed. My thoughts:

Often business rules are moving targets which is one of the arguments folks suggest for the use of surrogate keys (autonumber).

The Social Security Administration claims that SSN numbers are never re-used although there are many (perhaps mostly un-substantiated) claims that this isn't true. SSN is just a bad choice anyway as a "person identifier".

Business rules would dictate that the Social Security Administration should use SSN numbers but retailers have no reason to require this confidential info from users just because they bought a TV on-line. Auto-number is probably an appropriate identifier using an Address as a foreign key. Of course, there are plenty of folks who disagree with this and/or have other opinions.

A smart person listens to every side of an issue no matter how unpalatable or offensive so they can make an informed decision. Otherwise they are just a horse with blinders. FWIW.

Hi
Talk about a moving target!
________________________________________________________________________________

"addresses connected with one or more students."

"each student could have more than one address"

"Each address can have more than one student"

"Can't the student live alone or with somebody other than the parents? "

"a child can not live alone and if they are living somewhere other than their parents, I enter another address for that child with the Guardian for that location."

"I could have two or more addresses for each child, but every address would have a parent figure attached to it"

"I could on very rare occasions have two different families living at the same address"


AND!!!!/// OR!!!!
"Students may never be able to be at more than one address at once"

However

"I have students that are at one house the beginning of the week and another address the end of the week"

________________________________________________________________________________
_________

And there are more combinations than that.

Firstly in my opinion it' a m to m situation.
On the address side, there are 6 to 10 common address descriptors (more if you need them) of Address Types.


These could be Adderss Type descriptors could be implemented in a junction table, or an address table, a lookup table.

A Booleen (y/n checkbox) would also help to indicate one, two or three (more is doubtfull) "active" addresses.
This would help to track to "Moving Target" as conditions & circumstances occur.

This approach is used in Crystals "Contacts" database example in the archives.

Charlie

I am one of those "old fogeys" that have for various reasons sought further clarification of the premise to always use an autonumber field as the PK to every table within the DB application and I have listened to the input from others from within the UA community relating to this subject and I must admit that I am beginning to "come around" to agreeing within them about the autonumer PK issue. Who says you can't teach an old dog new tricks? LOL
However I would like to inject a word of caution (especially to newbies that are just beginning to delve into MS Access and relational DB design .... It is imperative to understand the rationale behind the normalization of the DB and to not blindly make use of the autonumber functionality as a PK without understanding the need for unique surrogate indexes (when applicable). There are a lot of extremely knowledgeable people on this site that spend a lot of time and effort is offering help and suggestions to those seeking help in their Access endeavors and for that a lot of us are extremely grateful. The autonumber PK is an extremely useful method for normalizing the db and creating valid table relationships that facillitate the creation of useful queries, reports and forms/subforms that present the underlying data in a manner that is straighforward, comprehensive and capable of conforming to underlying business rules. Just don't blindly make use of the autonumber PK "rule" without understanding the rationale behind the logic. Failure to do this can lead to troublesome results in terms of db design and the application's usefulness as related to the amount of time and effort that you have put forth in developing your application

Yes Charlie, It is that complicated. Thanks for trying to see this complicated relationship.

I agree, a yes/no checkbox is the only way to indicate the active or primary address/contact.

The guardian is connected to the address. In a way, the address is a description of the guardian, not a seperate entity. Yes, it is the guardian connected with the student, not the address.

At the moment - below is what I have. It took a while to get the relationships corrected between everything, but I think it works. In my last remodel of this, the checkbox for Primary Guardian was taken out, but I am putting it back in.

tblStudents
IDStudents - - - PK
LName
FName
etc.

tblAddress
IDAddress - - - PK
StudentLink - - FK to IDStudents in tblStudents
Street
City - - - - - - - FK to IDCity in tblCity (has City, State, Zip info)
HPhone - - - - home phone
Phone2 - - - - 2nd phone number - like a cell number
email

tblGuardian
IDGuardian - - - PK
AddressLink - - - FK to IDAddress in tblAddress
Guardian - - - - - Adult first name or names (mother and father)
G_LName - - - - Last name of Guardian
relationLink- - FK to IDRelation in tblRelation (Parent, Mother, Foster Parent, etc.)

AT the moment I have the direct relationship between the Student and the Address, but I am beginning to think it should be between the Student and the Guardian. Then in the Guardian table I put the checkbox indicating Primary Contact, and link that table with the Address table. OR, I go back to an earlier version and have none of them directly connected to each other, just connected all with a junction table and that is where I put the Checkbox indicating Primary Contact.

Tim

Hi Tim
Well, to make it a bit more complicated, taking a step back. Last week there was varying views of seperating or combining students and teachers (both people), now we have parents and guardians (people also) 3 tables of people may be bending the rules a bit too far. I really am leary about getting caught in the crossfire on this issue. as it is coming apparent that we are drifting away from the forms of normalization. So with the bigger picture in mind I would like to see what others have to say about it.

Charlie

Yes, I have broken normalization by keeping Teachers and Students as seperate tables, and I do not have a problem with that.

Guardians are not treated the same in the database.

Teachers and STudents each have
- First Name (one name) - field size is limited to 15
- Last Name (one name) - field size is limited to 15

Guardians have
- Guardian (multiple names and/or discription) - field size is set to 50
- Guardian Last name - (one name) - currently the Guardian's last name is seperated but will most likely end up back with Guardian since I have no reason within the use of the data to have it seperated and have run into problems with it being seperated, dealing with 2 parents with different last names. I originally seperated out the last name when I created different tables for Guardian and address, but I can see no use for it, since the address information is always sorted by the student not the guardian.

Most of the time in Guardian I will put something like; Joe & Mary Smith, or, Tammy (Grandma: Cindy). The purpose of the Guardian field is to provide me with information of who I can legally talk to when calling the house, so it is not like the teachers and students tables.

That is my view. A discussion of Normalization reguarding Guardians being in with either Students or Teachers, or all three being combined into one, does not make sense considering how Guardian is used and how the data in it is different from a typical name field.

Tim

Hello Tim

It seems that the more you are encouraged to normalize , the further you get away from it. Storing people in 3 different tables is going in the wrong direction (in regard to the normal forms). Especially when you justify it by combining data into fields and then redefining the people to "people of a different kind", and denormalizing to 1nf (data is no longer atomic).

I agree with Ace in regard to Addresses.
_______________________________________________________________________________
Your relationship of Student to Address is not a direct relationship.

A Student is related to an Address through their Parent/Guardian.
A Parent/Guardian lives at an Address. A Student lives with a
Parent/Guardian.

So no matter how you decide to model an Address you do not need
to have a direct relationship between Address and Student.
________________________________________________________________________________
_______

So in an effort to show you some viable alternatives, I've made a little demo for you that illustrates there are other ways to approach this (and keep the Normalization Police away lol). Try to have an open mind and roadtest it before you reject it. I'm not an expert on recursive joins, but if you are open to learning you can develop the skill and understanding to build a righteous application.

Charlie

I will spend time exploring and trying to figure out your example.

I downloaded your example and took a look at it, but I can not figure out what and how you did some of the stuff, so I can't use it till I do.

I am keeping an open mind, but I will not use something I do not understand. When I have some time I will explore it some more. Thank you for providing me with something to learn from.

As for the relationship between student and the address, it currently goes through the Guardian table instead of directly to the address.

Tim

Hi Tim

I really was hoping that the little test model would "turn on the light" for you. The purpose was to go beyond telling you what was wrong and quoting rules and to demonstrate the practical application of their use with entities and attributes that you are familiar with.

You seemed to be caught in the blind alleys of what is sometimes called "Spread Sheet Thinking". You know what you want, and Access can seem to satisfy your needs when used like Excel on Steroids.

However that house of cards comes crashing down when the forms of normalization are applied.
Using Humpty Dumpty as an example, the process of reduction (slicing and dicing) being similar to all the pieces of poor Humpty. If you Clone the pieces, you end up with many duplicates in the end. Certainly with all the extra parts and pieces you would have more than you started with. (Repeating Groups, & Repeating Data types).

In a normalized structure, you start with a single table, identifying all individual attributes (of your overall structure) and reduce each item to its most basic level (atomic). This is where you are taking the wrong turns (Off the Yellow Brick Road). Because if done correctly, after slicing and dicing, you should be able to reassemble your tables and reconstruct your data, WITHOUT extra parts and pieces.

This is also the place where you run into the Normalization Police. In your proposed structure, you are convinced that Teachers, Students & Guardians are significantly different to merit separate tables, although they are people, due to their relationship (ooooohhhhh, THAT WORD!!!) to each other, are really separate entities, Even if their attributes are the same!!!!!. Immediately going down the wrong road you are faced with relating those separated entities with more common attributes such as, addresses, phones, emails pagers, post office boxes, etc. The problems multiply.

Using the structure I built, if you query all fields of all tables, you get a result that resembles the original table with no repeating groups or data types.

Some insight as to how the structure is built.
The People table is using a recursive or self join. In the relationship view, show the people table twice and join them by the desired field. In the example, I used GuardianID. (Under the premise that you still have teachers separated from students and guardians) Now I encourage you to take it one step further and change it to something like RoleID to allow for teachers.

In query view, start in table design and show 2 occurrences of the people table " People and People1". Highlight the People1 table and click on properties. Then change the Alias Property (Name) to Guardian or Role; this changes the displayed name of people1.

The Structure is Hierarchal, meaning that at the top of the Hierarchy is a person. So you must first enter the person to place them at the top. IE a guardian, parent or teacher. Once there they can be related to others in the table. This method is used to relate people through the self-join. If you look at the people table, one record has no GuardianID; this is because it's at the top of the Hierarchy.

Concatenation in query is used to display the "First Name + "Middle Initial" + "Last Name".

You could take the concept a little further and break out the Guardian Types and People Types into lookup tables instead of value lists.

HTH

Charlie

Hi Tim

While you were sleeping, I took the whole concept one more step.

1/ I went back to the last week and got the last structure posted by Noah. Which was a basic design proposal I made with tweaks by Noah.

2/ I straightened out the visual layout made by Noah (Because I need to see clear relationship lines to visualize and analize them). but did not change his design

3/ I left Teachers alone as you have it and others concede that bending the rules a bit won't hurt in this case.

4/ Reworked tblStudents to become a people table like my last example to deal with the student/guardian/parent
/address issue.

I've read your past and recent posts about normalization and your perceptions of it's relation to functionality of your previous designs. Mostly they deal with "It always worked before" and "I'm running out of time" .

If you really want to learn, I suggest this: Go ahead with your old design so you can function with your work for now. Continue working on the revised normalized structure without the pressure of a deadline. You will learn a lot more , develop skills, and have a better structure in the end to show for it.

My 2 cents

Charlie

Thank you for your time and effort. I will look over things. I need to do some research on recursive or self join tables and querries for that is the element that is confusing me at the moment.

Also understand. I am not working on the same database anymore, or better put, I am now working on two. When we originally discussed Teachers and Students, that was in a different database that does not have Guardians and never will. This database, yes, does have all three and that probably means I need to rethink how I will remodel this database on the path to Normalization. Where the DIBELS database that I have asked the most questions about on this forum was a start from scratch database, the one I am working on at the moment isn't. I am currently working on normalizating a database I have used for years and one I must maintain all the data in during the remodeling.

Honestly, I am trying to understand all of this. It is just that the logic behind it doesn't make sense at times.

Tim

Hi Tim

Well certainly that explains some things / different week/ different database. However I hope that the execise has helped to give you some insight on not just the how of normalization , but some of the why. Your data has complex relationships between entities so building a 3nf model around it can be daunting.

Here is another resource for you to study (Courtesy of ace)
School Management Model

Good Luck in your Journey
Keep Smiling

Charlie

Thank you for the link - - it is a very interesting model. I also noticed on it that Teachers, Students, and Parents names are all stored in different tables. hmmmm

Tim

Hi Tim
Yes I saw that. And to show that I can think outside of the box. The attached is seems to represent the model you describe. Students are always associated with an adult figure thus the outlined relationship structure.

Charlie

Outside the box doesn't always = best practice.

It is, of course, up to each person as to how they ultimately design their database. From my own hard won experience, I will not break out people into different tables. All it takes is the 'one time' to overcome the idea of 'that will never happen' and then you're forced into a workaround. Once you hit the first workaround, then it usually turns into a domino effect. It is because of things like this that it may seem that things can be 'over complicated', but, as I said, it's from experience and trying to plan ahead to take any situation that could come up into account.

Hi Noah

" Outside the box doesn't always = best practice."

I do agree.

Charlie

Charlie,

In this latest example you have sent me, I see a problem (I think). Although you have it so a child can have several guardians and guardians can have several children, only one guardian is allowed per address. What do I do if a mom and dad are living at the same address?

Your link between Address and Guardian needs to be reversed, with the Foriegn Key being in the Guardian table not the Address table. I think that would allow for several guardians to be at one address.

Tim

Hi Tim
________________________________________________________________________________
_______
Your link between Address and Guardian needs to be reversed, with the Foriegn Key being in the Guardian table not the Address table. I think that would allow for several guardians to be at one address
________________________________________________________________________________
____

Well I am still in agreement with Noah , it was a mistake to suggest more bending the rules of normalization.

Let’s take a look at your presumption. Aside from reducing tables , normalization includes building relationships that
prevents Insert, Update and Delete anomalies. Your data model is tracking people. With your proposal, if you were to delete an address , you would then also delete the related person, a delete anomaly. So that’s not gonna work. You need to apply the impact of the anomalies when modeling the relationships between tables.

Charlie

The fact that setting cascading deletes in the relationship
window for a one to many relationship causes related records
to be deleted is not an anomaly. That is a function the database
engine performs because it was told to do so. If you do not set
cascading deletes it can't happen.

OK,

I haven't checked out any attachments since I only have Xp here at work, but what about something like:

tblPeople
PeopleID
LName
FName
RoleID
Comments
etc

tblRoles
RoleID
Role (Teahcer, Student, Parent, Father, Aunt, whatever)

tblStudentGuardian
SGID
StudentID (FK to tblPeople.PeopleID)
GuardianID (FK to tblPeople.PeopleID)
Primary (yes/no)

tblAddresses
AddID
AddLine1
AddLine2
City
Sate
Zip
(City, State and Zip might be broke out depending on how far your taking your normalization)

tblPeopleAddresses
PAID
PeopleID
AddID
AddTypeID

tblAddTypes
AddTypeID
AddType (Primary, Secondary, Alternate, etc)

Maybe I'm way off here, but that seems like what you need from what I've read...

J