I'm a music teacher who teaches an average of 50 students at a time. I'm trying to build a database track contact info, invoice students, and track attendance. I'm starting off with the contact portion of the database and once that is done I'll move on to the rest. I'm using Access 2007 and I'm a total noob to databases. I'm looking for some insight into my design thus far before I start creating relationships and entering data.

tblstudent - studentID, studentfirst, studentlast, gender, age (adult, teen, or child), lessonday, status (current or retired)

tbladdress - addressID, address, city, province, postalcode, studentid, guardianid

tblphone - phoneid, phonenum, extension, phlocation, studentid, guardianid

tblemail - emailid email, emailLocation, studentid, guardianid

tblgaurdian – gaurdianid, gaurdianfirst, gaurdianlast, relationship, studentid



Here's a few notes:

Adult students don't have guardians
Students with divorced parents may have 2 addresses and 4 Guardians.
Most parents have 2 emails and phone numbers
Some young students may provide their own phone and email

That's it, thanks in advance

What's the point in the gaurdian table? How does it help you in your business? Can a single gaurdian have more than one student enrolled at a time?

Which table "owns" an email? How do the relationships work in your system?

Which table "owns" an address? How do you intend to use it?

How do you envision using a phone? It isn't clear to me what purpose this table serves, but that might be because you need to explain more about how you intend to use it.

-PatP

I need to track the parents of my students in my contact list, so that's why I have a guardian table. A guardian can have more than one student.

Students and guardians have emails and phone numbers. I use guardian emails for invoicing and updates and students ones (if provided) for assistance with work and emailing files.

The address table is their to be included on invoices at a a later date.

One suggestion was that I just use a people table since guardians and students are people. I'd then use subclasses to join them, although I don't get this concept

Thanks for your consideration

Then right away, drop the type prefixes from your object names ("tbl", etc...), which are a useless holdover from old procedural programming methods and which can only cause problems when you try to modify your design in the future.

Thanks Can you elaborate on that please? Someone else said that they need to be there, but I like the look of no prefixes.

Ignore 90% of what that person tells you.
Best practices these days is NOT mixing the physical implementation of the database with the logical implementation of the data model.
What if you decide to change the table to a view at some later point? Are you going to end up with a view names tblsomethingorother? Or are you going to change the name and modify all your code?
I roll my eyes in disgust every time I have to deal with some asinine naming convention such as this. It is nothing but cargo-cult programming. A cheap simulation of true rigorous database design.
In short, it is the mark of noobs.

P.S. If I sound overly sensitive about this, it is because I am currently forced to work on such a database right now. It is annoying as hell.

Sorry to hear that, but totally understand. I appreciate your candor and am glad that someone finally clarified that for me.

Is there a concern with using spaces in table names?
Since you seem to be neck deep in this stuff, how does my model look so far?

Hadn't really looked at your schema, since Old Man Phelan is already assisting you and you should listen 110% of what he tells you.
But since you've asked me, I'll take a gander....

1) Nitpicky again, but:
Drop the "student" and "guardian" prefixes for your firstname and lastname columns. These fields really represent the same thing, so they should have the same label. To differentiate them in your code, include the table name when you reference them in a joined statement (which is good practice anyway) and alias them when necessary.
Well, that is the naming convention I would use, anyway. But I concede it is still a matter of taste.

If you are including both StudentID and GuardianID in your Address table to hold addresses (and your phone and email tables too) for both Students and Guardians, then I do suggest that you switch to a subclass table model.

Do NOT use spaces in object names. Use either underscores, or CamelCase (my preference).

OK, great input thanks

I don't get the whole subclass things, so I'll have to research that. One other person suggested that as well in addition to placing students and guardians in a people table instead, since they're all people. Again, I'll have to research it so I understand more.

Last night I was messing with having junction tables instead of foreign keys, so it looks like I have lots of options

Cheers!

I would combine your student and guardian tables into one person table. Then I would set up association tables between your person table and address, phone, and email tables to allow several people to share common contact information.

person(id, first_name, last_name, ...)

address(id, ....)
phone_number(id, ...)
email(id, ...)

person_address(person_id, address_id)
person_phone(person_id, phone_id)
person_email(person_id, email_id)

if you want to get fancy with subtyping, you could generalize address, phone numbers, and emails into a common "contact mechanism" table.

Your guardian table would then simply be a recursive relationship between two people:
guardian(guardian_id, dependent_id)

This would allow you to easily handle guardians that are also students (e.g., a whole family signs up for lessons).

Students, then, are just people taking lessons:
lesson(lesson_id, ...)
student(lesson_id, person_id)

thanks, great feedback

Where will the guardian_id and dependent_id come from?

They are both foreign keys referencing the id column of the person table.

Ok, so I basically create a table and within it I have a column label guardianid and one labeled dependentid. I then match 2 people id primary keys for each one

Is that right?