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?

joanolo · Accepted Answer · 2017-08-10 22:27:24Z

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),
    manager_id int
);

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

edited Aug 10, 2017 at 22:27

joanolo

6,3881 gold badge33 silver badges40 bronze badges

answered Mar 12, 2010 at 15:35

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

phazei Over a year ago

This is specifically database normalization. It's mentioned in that relation model link, but it's a pretty large page to find it.

NebuSoft · Accepted Answer · 2010-03-12 15:35:28Z

0

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.

answered Mar 12, 2010 at 15:35

NebuSoft

4,0102 gold badges24 silver badges24 bronze badges

Comments

C.Evenhuis · Accepted Answer · 2010-03-12 15:37:23Z

0

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

answered Mar 12, 2010 at 15:37

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Comments

Mike Cialowicz · Accepted Answer · 2010-03-12 15:37:27Z

0

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.

answered Mar 12, 2010 at 15:37

Mike Cialowicz

10k9 gold badges49 silver badges77 bronze badges

Comments

Cosmin · Accepted Answer · 2012-01-23 09:46:22Z

0

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

edited Jan 23, 2012 at 9:46

Cosmin

21.5k5 gold badges47 silver badges61 bronze badges

answered Feb 21, 2011 at 20:32

cliff

1

3 Comments

corsiKa Over a year ago

Could you provide some explanation to go with this? What you've done that's fundamentally different and why it's solution for the OP?

Jay Elston Over a year ago

@cliff -- while the query statement is helpful, consider adding this to the existing answer. By itself it does not answer the question posed by the OP.

Pang Over a year ago

Exact duplicate of the other answer.

Collectives™ on Stack Overflow

MySQL multidimensional arrays

5 Answers 5

1 Comment

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related