Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2010
    Posts
    49

    Table Layout Help

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2010
    Posts
    49
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by .:RoKsTaR:. View Post
    I'm using Access 2007 and I'm a total noob to databases.
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2010
    Posts
    49
    Quote Originally Posted by blindman View Post
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by blindman View Post
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2010
    Posts
    49
    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?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    Code:
    select	Student.LastName as StudentLastName,
    	Guardian.LastName as GuardianLastName
    from	Student
    	inner join Guardian on Student.StudentID = Guardian.StudentID
    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.

    Code:
    select	Student.LastName as StudentLastName,
    	StudentAddress.City as StudentCity,
    	Guardian.LastName as GuardianLastName,
    	GuardianAddress.City as GuardianCity
    from	Person as Student
    	inner join Person as Guardian on Student.StudentID = Guardian.StudentID
    	inner join Address as StudentAddress on Student.PersonID = Address.PersonID
    	inner join Address as GuardianAddress on Guardian.PersonID = Address.PersonID
    where	Student.PersonType = 'Student'
    	and Guardian.PersonType = 'Guardian
    Do NOT use spaces in object names. Use either underscores, or CamelCase (my preference).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2010
    Posts
    49
    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!

  11. #11
    Join Date
    May 2008
    Posts
    277
    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)

  12. #12
    Join Date
    Apr 2010
    Posts
    49
    thanks, great feedback

  13. #13
    Join Date
    Apr 2010
    Posts
    49
    Where will the guardian_id and dependent_id come from?

    Quote Originally Posted by futurity View Post
    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)

  14. #14
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by .:RoKsTaR:. View Post
    Where will the guardian_id and dependent_id come from?
    They are both foreign keys referencing the id column of the person table.

  15. #15
    Join Date
    Apr 2010
    Posts
    49
    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?

    Quote Originally Posted by futurity View Post
    They are both foreign keys referencing the id column of the person table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •