Contacts and Invoicing

[John W. Vinson]

Actually now that I think of it, I may have messed this up....I have to
somehow accommodate students with 2 separate residences (divorced parents)

So maybe I need a guardian table that links to address, phone, and email and
then finally back to Student.


Soo confusing

It's worse than that <g>...

One student might have two residences, but one guardian/parent may well have
two students.

Other than that, your design looks good; yes, you will need a StudentID in
each of these "many" side tables.

 

[.:RoKsTaR:.]

That is very true, since I do have siblings and parent and child students.
Hmmmm, I'll have to figure that one out. That's a lot of relationships!

 

[.:RoKsTaR:.]

Ok so here's how my tables appear so far:

tblstudent - studentID, student first, studentlast, gender, dobmo, dobda,
dobyr, lessonday, status (current or retired)

tbladdress - ID???, address, city, province, postalcode

tblphone numbers - ID???, phonenum, extension, phlocation

tblemail address - ID???, email, emailLocation

tblgaurdian - ID???, gaurdianfirst, gaurdianlast, relationship

I'm not sure what to do with each of the ID fields yet, but that's what I
have right now. As I figure it, the student has to have a primary key to
relate to everything. The Gaurdian has to have a key to relate to address,
email, and phone on it's own. Not sure of anything else yet.

Any thoughts or suggestions?

 

[John W. Vinson]

Ok so here's how my tables appear so far:

tblstudent - studentID, student first, studentlast, gender, dobmo, dobda,

StudentFirst etc. (don't use blanks in fieldnames); DOB (Date/Time, it's easy
to pull out day/mo/year)

dobyr, lessonday, status (current or retired)

tbladdress - ID???, address, city, province, postalcode

tblphone numbers - ID???, phonenum, extension, phlocation

tblemail address - ID???, email, emailLocation
ditto

tblgaurdian - ID???, gaurdianfirst, gaurdianlast, relationship

I'd be inclined to put GuardianID in tblStudent, assuming that *most* students
have only one Guardian but a guardian may have more than one student. If you
do in fact need multiple guardians then you should have a GuardianID primary
key in Guardians and a new table

Guardianship
StudentID
GuardianID
<any info about the relationship between this student and this guardian,
e.g. CustodialParent, text field describing the schedule of custodianship,
etc.>

I'm not sure what to do with each of the ID fields yet, but that's what I
have right now. As I figure it, the student has to have a primary key to
relate to everything. The Gaurdian has to have a key to relate to address,
email, and phone on it's own. Not sure of anything else yet.

Any thoughts or suggestions?

You're doing great!

 

[.:RoKsTaR:.]

Thanks John, I'll try to make those changes and post back

Since I have 2 Guardians for most students, I may change that table to
Gaurdian1 (Full Name) and Gaurdian2 (Full Name) . I'll have to play with it
and see what works best. I don't want to make more work than necessary, but
also don't want to limit functionality


Cheers!

 

[.:RoKsTaR:.]

Hmmmm, what's the best way to handle the following:

1) let's say I have a student with their own email and two parents each
with 2 emails of their own. All emails in the email table with only one
relating to a student and the rest relating to a guardian?

2) Adult students don't have guardians, so do i leave that info blank for
them?

 

[John W. Vinson]

Hmmmm, what's the best way to handle the following:

1) let's say I have a student with their own email and two parents each
with 2 emails of their own. All emails in the email table with only one
relating to a student and the rest relating to a guardian?

2) Adult students don't have guardians, so do i leave that info blank for
them?

There's a discussion in another thread about this. One idea is to have
tblPeople: students are people, parents are people, legal guardians are
people. You could do "subclassing" with all the fields common to all people
*in* tblPeople, with one to one relationships to tables for information
pertinant to just Students or to just Guardians; but in this case it might be
better to denormalize a bit, and have all the fields needed just in tblPeople.
You can have a "Self Join" - put a GuardianTo field in tblPeople; it would be
NULL for students but point to the person to whom this is a guardian in
records for guardians.

 

[.:RoKsTaR:.]

Wow! You really know a lot about this Thanks!

I have to say I'm a bit confused, but I'm working on understanding it more.
I'm gonna go watch a few more tutorial videos from the lynda set and maybe
that'll help

 

[John W. Vinson]

Wow! You really know a lot about this Thanks!

I have to say I'm a bit confused, but I'm working on understanding it more.
I'm gonna go watch a few more tutorial videos from the lynda set and maybe
that'll help

Subclassing is generally considered an advanced topic so just be aware you're
already in the deep end of the pool... and doing fine.