Which SQL table structure is best to create one table with different groups each having different members?

Question

What I'm trying to do is as follows:

I have a table filled with supervisors and another one filled with workers. Each worker can belong to one or more Groups and each supervisor can be "in charge" of one or more Groups. What structure would you suggest for a Group table in mysql? What if different supervisors could have different "levels" in the same group, how would you create the table then?

So far I've figured out that I could use some kind of string to store the info, such as: group_id1:level,group_id2:level,... and retrieve it with some sort of explode function. Any other ideas?

Thanks!

Answer 1

create a table for the groups.

since workers have many-to-many relationship to groups, create an xref table for worker_groups.  each row in that table should contain keys for one worker and one group, i.e. xref points to worker and to group.

since each group has one supervisor, the group table should point to the supervisor, i.e. the group row should contain the key to the supervisor.

Answer 2

So, you have:

- workers

- groups

- supervisors

and you need many-to-many relations between workers and groups, and between supervisors and groups. And relation between supervisors and groups has property "level".

Right?

Each relation can be expressed as table. And you will have 5 tables: 3 for entities, and 2 for relations. Each table for entity should have primary key (let's call it id). A table for worker-group relation has two fields, worker-id and group-id. A table for supervisor-group relation has 3 fields, supervisor-id, group-id and level.

Answer 3

You don't want to do "groupid1:level" that violates one of the basic principles of database normalization, never put more the one piece of data in a field. Based on the information you have provided you could do this:

Table: Group

  GroupID

  GroupName

Table: Supervisors

    SupervisorID

    SupervisorName

Table: Workers

   WorkerID

   WorkerName

Table: WorkerGroups

   WorkerID

   GroupID



Table: GroupSupervisor

  GroupID

  SupervisorID

  Level

If workers and supervisors have the same fields you could combine the worker and supervisor tables and add a boolean field IsSupervisor to differentiate them.