I'm confused about normilazation..

[Whack]

Good morning... I'll try to be as clear as I can...

I have a need to have an address table... where anything that exists having an address has its address in the table.. so you can think of this as

Customer addresses -> Address Table
School Districts -> Schools -> Address Table
Churches -> denominations -> Church Names -> Address Table

I'm having a difficult time with the term's of normalization...

If I understand it correctly, everything is a "container" that contains a "full item that describes something".

So with that I think everything has an address... and with that there should be an address table where everything that has an address should have it's address in the address table..



Similarly, everything / everyone that has a phone number should be in a phone table...

Am I thinking about this incorrectly?

Thanks in advance
---Whack

[CJ_London]

you are thinking correctly but you don’t have to have addresses from different entities (customers, churches,etc) in the same table, you can have separate address tables for each entity

however you could consider putting all the entities in one table with an entity type field. depends on how much difference there is between the entities. It is not a problem to have a few fields which are only used by one entity type or perhaps multi functional- the relevance of the stored value dependant on the entity type.

You can also have a separate table or tables with a 1-1 relationship to the entity table to house those different values

It all comes back to what you are wanting the app to do. The main thing about normalisation is to avoid the duplication of stored data

[Whack]

Thank you CJ!

I've been wrestling with this for a couple of weeks..

If I keep thinking about tables as control blocks.. where every "record of a [type]" is maintained in the control block and each [type] is a pointer to a table that relates to the record in the table..

So there'd be an address tbl.. where


The school tbl had ptrs to the individual records in the address tbl..

Sure enough, the school addresses in the address table would likely not be contigious.. but the school tbl would be responsible hfor knowing where it's individual records might be..

Maybe I complicated a fart!😊

[CJ_London]

So there'd be an address tbl.. where


The school tbl had ptrs to the individual records in the address tbl..

sounds like the wrong way round. Usually a school has one (or more) addresses so the school table has a PK, the address table has a FK that 'points' to the PK in the school table

The way you describe it an address table has one (or more) schools.

Sure enough, the school addresses in the address table would likely not be contigious..

irrelevant, databases have no sense of order unless you provide it by ordering it.

and just to pick up on this

Similarly, everything / everyone that has a phone number should be in a phone table...

usually you would have a table of contact methods - landline, mobile, email, whatsapp, etc. and another table containing the data - contactFK, methodFK, methodValue (the actual number/email/whatever), perhaps with other fields such as a status for 'preferred method', 'do not use this method' etc. (yes you could delete the do not use records, but then the next user comes along and puts it back). So another tip is to design so that you never delete data (unless it is completely wrong), just include a status such as the two above or 'closed', 'gone bust','lost contact', whatever.


Without knowing exactly what you are trying to achieve, difficult to provide specific advice.

[alansidman]

See if this white paper helps you to understand

[Whack]

See if this white paper helps you to understand

Thank you! I'm reading it...

[Whack]

Alan,

Thank you for the document... I found it quite enlightening... now I've a new question..

So I've a table... Lets use Students as an example..

I understand I'd have a studentID PK that is a FK in all other tables... let's discuss things like, address, phone and the like..

Which makes more sense..

for addresses a table for student_home_address_table; student_mailing_address_table..

For phones, Student_phone_landlne_table, Student_Phone_Cell_Table, Student_Phone_Satellite_Table

This way each entity as it relates to address is in it's own entity or domain and the same would be true for phones...

What it the phone tables don't do is that it allows the same phone number to exist in all the phone tables types (cell, satellite, landline) for the same student... which could become somewhat of a mess...

How does one handle a student having several phone numbers / addresses, (home, mailing, vacation) field identifiers?

Thanks in advance
Whack

[CJ_London]

In that case you have what is called a many to many relationship

one student can have many phone numbers and one phone number can have many students

To handle this you have a linking table which contains

studentFK
PhoneFK

and remove studentFK from the phone table

to complicate things further, a phone number might be a halls of residence number so can also be associated with an address.

[ArviLaanemets]

Generally, it is not a good idea to have same type of information in different tables.
E.g. for phones, the really useful info is the phone number. Is this number used in mobile phone, or in handheld one, or even is it a fax number when user of number is living halfway in stone age, is secondary info when all you need, is to get a contact. (At least in my country, people get the phone number, and then can register it either for his/her mobile, or for handheld one, and can change this registering at will simply visiting provider). You need to have the info about physical device only in case you a designing some inventory software. In case the type of device has some real value for you, you can simply have an additional DeviceType field for phone number in your table.

But when the contact info is cardinally different for different contact types (e.g. physical addresses compared with phone numbers, or with mail addresses), then having them in separate tables is reasonable.

When any student can have only single contact of same type (i.e. registered in same table), or at least some of your students can have several contacts of same type, but different students can never have the same contacts, then you can have student id value in table for those contacts. Like
tblContactsOfType: ContactOfTypeID, StudentID, AnyNumberOfContactInfoFields.

When a lot of different students can have same contacts of same type, then you need a table where you register contacts, and another one where you determine, who is using which ones. Like
tblContactsOfType: ContactOfTypeID, AnyNumberOfContactInfoFields;
tblStudentContactsOfType: StudenContactOfTypeID, StudentID, ContactOfTypeID.
When such cases are rare, you can use th 1st approach too, but be aware, that when you search for student having certain contact, you may get a list of students instead.

[madpiet]

I don't think about it that way at all. I approach it from the entity/"thing being modeled" perspective. What attributes about that entity am I interested in or need to store? Then if find I have a several entities that overlap (like people in different roles), I usually find that I'm missing a relationship.

But I never start from the attributes of an entity and work my way up.

[Whack]

Thank you... Everyone....

I'm still new enough I'm not quite sure what to think... so I'm still trying to sort this all out... I'm painfully stubbing my toes as I go... and believe me you can see everywhere I've been because of the bloody trail my toes have left...

BTW... I usually read the replies and then need to go process them and see how it fits..

Thanks in advance to everyone
---Whack

[Whack]

Hi everyone,

Ok, so let me take the arrows now with my design... here's how I see this DB working..

I've got some students... from my vantage everything revolves around the student. So I start with the tbl_Student.

The Student can live at more than one residence over time so the Student is associated in a 1 to many addrsses with the tbl_StudentAddress table.

Similarly, the Student can have one or more phones associated with them. So the Student is associated in a 1 to many phones relationship with the tbl_StudentRecord table.

So everytime something happens with the student, (meeting, review documents, phone call.... email) it gets recordedinto the tbl_StudentRecord. So the tbl_Student is in a 1 to many relationship with the tbl_StudentRecord table.

Now, onto the Guardians...

The Student can have one or more Guardians (parents, Step Parent, Paramour, GrandParents, Guardian ad Litem, Conservators, ect) so the Student has a 1 to many relationship with the tbl_Guardian.

But the Guardian can have one or more addresses and then they can move, so associating the tbl_Guardian to the tbl_GuardianAddress assures the relationship between each "Guardian" is maintained.

And similarly, each Guardian can have one or more phone numbers associated with them... so the tbl_Guardian is in a 1 to many relationship with the tbl_GuardianPhone table.

I think I've a fairly clear view of all the above... but here's where I'm getting lost..

The Student can go to 1 or more School Districts (likely over time and hopefully not at the same time but that might be possible too). So the tbl_Student is in a 1 to many relationship with the tbl_SchoolDistrict table.

And the tbl_SchoolDistrict is in a one to many relationship with schools within the School District so the tbl_SchoolDistrict is linked to the tbl_School table.

And each school can have one or more phones associated with that individual school.. so the tbl_School is in a 1 to many relationship with the tbl_School table.

I've included what I've created in the file attached so if I can get some advice, I'd certainly appreciate it.




I'm sure I'm missing something obvious... this seems to be entirely to straight forward... so I'm sure there's a large glaring hole in my design / thinking somewhere... inexperience will get me in trouble every time!

Now if you open the "learn" file, you'll see there's all these tables with _1 notations.. I know the real tables are there but I can't seem to sort out why I can't see them.

I had them in a file named "X.ACCDB" and renamed the file to "Learn.ACCDB" and well... then I couldn't see the relationships to the tables again.. I'd hate to have to rebuild this thing but if that's whats needed ... ok I will...

Again, thanks to everyone helping me get into this..
Thank you in advance
---Whack

[CJ_London]

the _1 is because you have included the table a second time and is just Access's way of differentiating them. Add the same table again and it will have a _2 suffix. For some reason you have added the tables twice to the relationships window.



I don't believe you have explained in detail what your db is supposed to do or what the rules are around how the data interacts. I suspect you have not grasped the fundamentals of how relationships work with the rules and the big wide world. For example it may be a student is associated with a district based on their address and they can only attend schools in that district - it's kinda what you relationships reflect - but if a district has several schools, your relationships do not show which school they are currently attending, or others they have attended in the past. It also does not allow for students changing districts by virtue of changing address - maybe that matters maybe it doesn't.

In summary - your relationships show 1 to many relationships whereas in fact many of the relationships are many to many (a student goes to many schools, a school has many students). Refer back to post #8 and probably post #5. I've not check the link, but pretty sure many to many relationships will be mentioned as they are a fundamental part of database design

[Whack]

Let's see if this describes the entire system.

Students (ages ~3 -> 26 ). Usually afflicted with one or more of several learning / educational / physical / emotional / behavioural issues.

Each student needs some kind of advocacy support where the guardians ( Parents, guardians, paramore's, guardian ad Litem's ) need the support guidance of educational advocate. The "educational advocate" isn't allowed to "represent students / guardians" in any legal form, but the advocate has extensive depth of knowledge about what the State Laws are as they relate to what services are to be afforded to the student. Generally speaking, the "Guardians" don't have that depth of knowledge and for the most part, the educational advocate can "guide" the "Guardian" either for the "Student" or in conjunction with the student.

The educational advocate is compensated on a .1 hour basis and at the express decision of the educational advocate the fee can be discounted or eliminated (which creates a business loss for tax purposes). The types of activities that are "billable" in 0.1 hour increments are;

- preparation for meeting (virtual or in person)

- reviewing documentation (email, documents from school districts, schools, state, some medical evaluation, or other evaluations)

- Travel (both mileage and time billable on 0.1 hrs increments)

- Generating documentation or email responses with / for Guardian / Student

- Phone Calls

- (There are some times where the Educational Advocate performs as a "paralegal") and there is a difference in re-numeration for acting as an "educational advocate" and a "paralegal" for an attorney.)

- School Districts have schools at various levels, Pre-School; Elementary; Junior High; High School. Then there may be some college level work as well and those would be state level colleges or university's. Additionally, the student may attend a "private School"; an in-home school; an in home hospital school,

- The student may be enrolled predominantly in one "School District" at a time however it is possible for the student to attend more than one school in multiple school districts.

- it is possible for the student (because of their condition) to attend one or more schools either within the same school district or across school district boundaries simultaneously.

- Every studentRecord (these are the billable records) needs to have a drop-down for the event so all the events are selections.

- The student and or guardians can have both physical and mailing addresses which may be in different towns.

- The Student and or guardian may have one or more phone numbers as well as email addresses.

- the student and or guardians may have preferred names

- the student and or guardian may identify as a gender other than what their birth certificate specify (y'gotta love this where someone can identify as a car!)

- the students parents may be only partners, not married, or divorced and married creating "step-parent" situations or throuple; polyamorous multi-parent scenarios.

- every event in relation to studentrecord must only have one (1) event. It's possible that an e-vent (say a phone call) may involve reading / writing / replying to an email or reviewing other documentation, but it's not reasonable to think while on the phone, that event would terminate... hence the notes section to describe what happened... as well as whom was at a meeting, phone call... action plans and any other multitude of things.

- As for billing, in theory, each event is payable upon completion, but in reality and all to often, the billing is done once a month.

- The system must be able to identify payments and apply them to the running total of the outstanding account receivable for that student (guardian if you will).

- Each StudentRecord needs to be identified as either being related to an educational advocacy engagement or a paralegal engagement. (I've been torn whether there should be a StudentRecordAdvocacy table and a StudentRecordParalegal table.

- Then there's the issue where the Student could be represented by an attorney and having that information available from the student table makes some sense so it's available at a seconds notice.

- Must be able to add new Students / Guardians / School Districts / Schools / Attorneys / assessor of various types ( behavior, cognitive, medical to name just a few.

- Ability to have a "hyperlink" to various external documentation. Financial agreement forms; documentation from school districts; schools; assessors; and others...

- each StudentRecord must have the date of the event and the start and end times comprising that event. Each event can only be billed to the lat .1 hrs... so if the bill would be at .25 hrs (15 minutes it's reduced to12 minutes or .2 hrs)

- Ability to see all the school districts / schools past and present as well as addresses and phone numbers past and present as well as emails past and present for students; guardians; school districts; schools; assessor; attorneys ; assessors...

I've got most of this working in Excel; but to say it bluntly; it's awkward.. and I suspect a RDMS will make this so much easier. I'm just having a hard time sorting out which entities should relate and link to what other tables so this whole thing works.

Does that describe what I'm after from a whole system what I'm trying to build.

BTW... I understand the _1 tables are second instantiations of the initial table... but I can't seem to get back to the original table and not have the secondary tables with the _1 .. any idea how to get show the original table?

I appreciate and am grateful for any and all help in getting going with this.

Thanks in advance
---Whack

[CJ_London]

I'll be brief because I don't have much time over the next few days so focusing on relationships

Edit: did in a rush - and made mistakes, text edited below

Each student needs some kind of advocacy support where the guardians ( Parents, guardians, paramore's, guardian ad Litem's ) need the support guidance of educational advocate. The "educational advocate" isn't allowed to "represent students / guardians" in any legal form, but the advocate has extensive depth of knowledge about what the State Laws are as they relate to what services are to be afforded to the student. Generally speaking, the "Guardians" don't have that depth of knowledge and for the most part, the educational advocate can "guide" the "Guardian" either for the "Student" or in conjunction with the student.

parents might have more than one child in the system (now or the the past/future), ditto the others so this is a m-m (many to many) relationship

The educational advocate is compensated on a .1 hour basis and at the express decision of the educational advocate the fee can be discounted or eliminated (which creates a business loss for tax purposes). The types of activities that are "billable" in 0.1 hour increments are;

are there many of these or just the one? are these based on district? the student address? something else?

- School Districts have schools at various levels, Pre-School; Elementary; Junior High; High School. Then there may be some college level work as well and those would be state level colleges or university's. Additionally, the student may attend a "private School"; an in-home school; an in home hospital school,

m -m relationship with the student

- The student may be enrolled predominantly in one "School District" at a time however it is possible for the student to attend more than one school in multiple school districts.

- it is possible for the student (because of their condition) to attend one or more schools either within the same school district or across school district boundaries simultaneously.

m-m relationship, with start/end dates in the joining table

- Every studentRecord (these are the billable records) needs to have a drop-down for the event so all the events are selections.

needs an events lookup table

- The student and or guardians can have both physical and mailing addresses which may be in different towns.

m-m relationship

- The Student and or guardian may have one or more phone numbers as well as email addresses.

per previous comments - if phone numbers can apply to more that one student/guardian, it is a m-m relationship

- the student and or guardian may identify as a gender other than what their birth certificate specify (y'gotta love this where someone can identify as a car!)

needs a gender lookup table

May have missed a few, but when putting together your specification, determine the relationships first, then in another section define the requirements since this will usually only require adding additional fields to the existing tables defined by relationships - perhaps with the occasional lookup table