Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2006
    Location
    NY
    Posts
    14

    design question, to group or not to group?

    I have a the following objects:

    teacher: fname, lname, ss, dob...
    student: fname, lname, ss, dob...
    agent: fname, lname, ss, dob...
    manager: fname, lname, ss, dob

    My question is this: since all objects have a common denominator - should I just group them all into one table (staff) and include a TYPE column that indicate what's his role (teacher, student, agent, manager)??? or should I have each table associated with the type?

    * if I have a meeting-table and the meeting has only 1 student, 1 agent and 1 manager wouldn't that raise the complexity if I use one table for all staff member?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xianwinwin
    My question is this: since all objects have a common denominator - should I just group them all into one table (staff) and include a TYPE column that indicate what's his role (teacher, student, agent, manager)???
    yes

    Quote Originally Posted by xianwinwin
    if I have a meeting-table and the meeting has only 1 student, 1 agent and 1 manager wouldn't that raise the complexity if I use one table for all staff member?
    no

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You haven't raised one very important question, how do you deal with people that fit more than one category (say both a student and an agent, or a manager and a teacher)?

    -PatP

  4. #4
    Join Date
    Jun 2006
    Location
    NY
    Posts
    14
    thank you guys for your reply.
    Pat - I guess I'll have multiple rows (when student and agent are the same).

    so, assuming ALL will have one table and I would like to conduct a "meeting".
    so meeting table will look like this:

    meeting_id.....date.......location....comment
    1.................1/1/07........HK...........be on time


    and another table: meeting_staff

    id...........meeting_id........staff
    5...............1.......................3
    6...............1.......................6
    7...............1.......................7

    this looks like a good design that can solve the problem, but there's one glitch:

    Sometimes the meeting is comprise with staff (student, manager...) AND an entity (IBM, Microsoft etc); obviously they send a human (ha!) but I have no info about him/her. What I have is simply a name of the entity, how can I incorporate the entity into this table?

    entity:
    entity_id
    name
    address

    thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xianwinwin
    What I have is simply a name of the entity, how can I incorporate the entity into this table?

    entity:
    entity_id
    name
    address
    Code:
    
    entity_id  name  address
       937     IBM   123 sesame street


    by the way, your meeting_staff table should not have its own id -- it should have only meeting_id and staff_id, and those two columns together would be the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by xianwinwin
    I guess I'll have multiple rows (when student and agent are the same)
    It's probably not good having multiple staff records for the same user , even if they have multiple roles, because you'll soon end up with different data for the same person ie 2 different addresses for the same person. If you do have staff with multiple roles then couldn't you just put the staff roles into a separate table.

    I like to group entities together (where it makes sense) simply because it means you end up with fewer tables and less SQL code in the end application. If there's less code then the final product comes out a lot quicker and with fewer bugs. It also means that if you add some fancy code for one type of staff member (say a sounds like search on names) then that functionality will apply to all the different staff types.

    Mike

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I prefer the following entities

    people and roles

    Allows a lovely 1:M and everything!
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2006
    Location
    NY
    Posts
    14
    I take it "people" and "roles" are 2 different tables, right?!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would really like to see the "roles" that a "meeting" table might require for the following types of people, george: teacher, student, agent, manager

    the only thing i can come up with is "meeting attendee" and i'm not sure a "role" table is really required

    let's not over-design this, shall we

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by xianwinwin
    I take it "people" and "roles" are 2 different tables, right?!
    Yes, the roles table will have rows for student, teacher, manager, and agent.
    Quote Originally Posted by mike_bike_kite
    It's probably not good having multiple staff records for the same user , even if they have multiple roles, because you'll soon end up with different data for the same person ie 2 different addresses for the same person.
    This actually might be a desired effect in some cases... You might want where they live for the student row, but where they work for the faculty row.
    Quote Originally Posted by xianwinwin
    What I have is simply a name of the entity, how can I incorporate the entity into this table?
    One option would be to create a pseudo-person that represented the entity, possibly allowing someone to later substitute the data for the representative when that becomes known.

    -PatP

  11. #11
    Join Date
    Dec 2007
    Location
    Appleton, Wisconsin
    Posts
    9
    O.K just my 2 cents worth again but here goes,

    A Junction table called say Position between Staff and Roles takes care of the M:M relationship for many staff members may have one role and one role may belong to many staff members. The junction table would consist of STAFF_ID and ROLE_ID as primary key.

    You need to decide if knowing a persons address as student and address as faculty is needed or if just the most current address is required. If you need to store more then one address for a person or want to store historical data about previous addresses then there is a better way to break that information out then to have duplicates in the staff table.

    I agree with Pat that the entities name would go into the staff table and you would add ENTITY as a role in the ROLES table.

    Meeting Attendees would be a junction table between the Staff table and the Meetings Table. Consisting or STAFF_ID and MEETING_ID as the primary Key.
    Know the only thing with this hole set up is if a person has more then one role you wouldn't know what role they were in for that meeting. If that doesn't matter then this works. If it does matter then you could I do believe us a AUTONUMBER fiel as a primary key in the Position table making STAFF_ID and ROLE_ID foriegn Keys and then use the AUTONUMBER field and MEETING_ID as the primary key in the Meeting Attendees table allowing you to know who attended and in what capacity.

    Well thats my thoughts, anyone feel free to tear this idea apart as I like criticism.

Posting Permissions

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