MySQL multidimensional arrays...

Question

What is the best way to store data that is dynamic in nature using MySQL? Let's say I have a table in which one item is "dynamic". For some entries I need to store one value, but for others it could be one hundred values. For example let's say I have the following simple table:

CREATE TABLE manager
( 
name char(50),
worker_1_name(50),
worker_2_name(50),
...
worker_N_name(50)
);

Clearly, this is not an ideal way to set up a database. Because I have to accommodate the largest group that a manager could potentially have, I am wasting a lot of space in the database. What I would prefer is to have a table that I can use as a member of another table (like I would do in C++ through inheritance) that can be used by the "manager" table to handle the variable number of employees. It might look something like this.

CREATE TABLE manager
( 
name char(50),
underlings WORKERS 
);

CREATE TABLE WORKERS 
( 
name char(50),
);

I would like to be able to add a variable number of workers to each manager. Is this possible or am I constrained to enumerating all the possible number of employees even though I will use the full complement only rarely?

Answer 1

In general, you should be doing something like the following:

CREATE TABLE managers ( 
    manager_id int,
    name       char(50)
);

CREATE TABLE workers (
    name       char(50),
    maanger_id int
);

This is how you should represent "a variable number of workers" in the relation model.

Answer 2

You could use an ID column to keep track of the workers as records in the same table.

CREATE TABLE manager
(
name char(50),
id int
);

CREATE TABLE WORKERS
(
managerID int,
name char(50)
);

Allow duplicate managerIDs in worker...when you want to get specific workers to a manager, simply SELECT Name FROM WORKERS where managerID=ID and it will return all of the workers for that manager. If you need more complex lookups you could also use Joins.

Answer 3

You could assign an autoincrement id to manager, and link the workers to it using a foreign key:

manager
   id
   name

worker
   id
   manager_id
   name

Answer 4

You can create an intermediate mapping table. Something like the following:

manager(id, name)
managerWorkerMapping(managerId, workerId)
worker(id, name)

Now, to get all of the workers for a particular manager, you can run the following query:

select w.name from worker w, manager m, managerWorkerMapping mwm
where m.name = 'manager name' and
      m.id = mwm.managerid and
      mwm.workerId = w.id

It's important to note that with this schema, it's possible for workers to have multiple managers as well.