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

yes

no

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

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

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

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

I prefer the following entities

people and roles

Allows a lovely 1:M and everything!

I take it "people" and "roles" are 2 different tables, right?!

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

Yes, the roles table will have rows for student, teacher, manager, and agent.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.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

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.