MYSQL Database Structure - Arrays?

Question

I've read multiple places that you shouldn't store arrays in a mysql db. So I thought I'd ask how I should go about doing this:

I'm writing a basic task management app in php/mysql, and I want to have projects and tasks. A task is a single object, where a project is an object that holds multiple tasks. So I already have the tasks working, I have a task table with id, name, dueDate columns. My next step is the projects.

My plan was to have a project table with id, name, dueDate, and taskID columns. The taskID column would either hold an array of task IDs or possibly just a string with comma delimiters.

Any suggestions?

Why not have projectID as a foreign key in your task table? — andrewsi
– andrewsi, Commented Aug 17, 2012 at 15:47
multiple values in a single field defeats the purpose of having a relational database. Splitting those multiple values into their own dedicated child table is called normalization, and you should read up about it. — Marc B
– Marc B, Commented Aug 17, 2012 at 15:48

thatidiotguy · Accepted Answer · 2012-08-17 15:47:56Z

3

This is called a one-to-many relation in MySQL. You would have tasks that hold the id of the project they are assigned to. So to find the tasks associated with the project, you would simply query for all tasks that are holding the project id. Does that sound right?

answered Aug 17, 2012 at 15:47

thatidiotguy

9,01114 gold badges66 silver badges109 bronze badges

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

1 Comment

user1564018 Over a year ago

Ahhhhhh...DUH! Simple, graceful solution. Thanks guys!

Peter Kiss · Accepted Answer · 2012-08-17 15:49:18Z

0

Tasks(id, name, dueDate, ProjectID) Projects(id, name, dueDate)

The ProjectID column is an and of a foreign key and the Proejcts' table id column is the other one.

answered Aug 17, 2012 at 15:49

Peter Kiss

9,3292 gold badges25 silver badges38 bronze badges

Comments

Dan · Accepted Answer · 2012-08-17 15:51:48Z

0

If there can be a different number of task ID's per id/name record then two options. The first is to use XML instead of SQL. The second is to create two tables. On that holds the id, name and due date and then a second that just has id and taskID. id in this second column is a foreign key referencing id in table 1 and taskID is a single taskID. For example:

Table 1:

id | name | dueDate
-------------------
1  | bob  | '2012-08-16'
2  | fred | '2012-08-17'

Table 2:

TableID | TaskID
-----------------
1       | 1
1       | 2
1       | 3
2       | 1
2       | 2

answered Aug 17, 2012 at 15:51

Dan

45.8k20 gold badges98 silver badges169 bronze badges

Collectives™ on Stack Overflow

MYSQL Database Structure - Arrays?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related