Welcome Guest, Not a member yet? Register   Sign In
Help modeling assignments
#1

[eluser]pabloheim[/eluser]
Hi,
im developing a mini app where a boss assigns a task to some members of the staff. each member has an id and a name. The task has an id too.

In your opinion, what is the best way to store in a databse the link between a task and the part of the staff that have to solve it?

for example, my first idea was this:
the tasks table has a column called team that is a string like this: "1,4,8" .This means that members whose id are 1, 4 and 8 have to solve the task. This is easy to insert, but a little difficult to extract (lack of flexibility)

i have another idea : imagine we have a table called assignments. it has two columns : "task_id" and "member_id". If the boss asks members 1, 4,8 to solve the task "30", then this is stored like this:

Code:
---------------------
task_id | member_id |
---------------------
  30    |      1
---------------------
  30    |      4
---------------------
  30    |      8
---------------------

This solution is easy for extract : select member_id where task_id="30"
but a little bit difficult to insert.

What do you think??

Thanks in advance and sorry for my english.
#2

[eluser]jedd[/eluser]
Howdi,

You are correct - strings containing multiple integers, separated by commas .. is not a good way of recording this information.

Quote:i have another idea : imagine we have a table called assignments. it has two columns : "task_id" and "member_id". If the boss asks members 1, 4,8 to solve the task "30", then this is stored like this:

You are on the right track here. Typically you would have an id column, AUTO-INC, UNIQUE, etc - as your primary key.

And you'd call the table by the name of the two tables you're connecting - so member_task (usually alphabetical ordering). This is a bit traditional Active Record (as opposed to CI's AR) but consistency is good, and their standard is as good as any. You'd also make the two columns you've mentioned here as INDEX, of course.


Quote:This solution is easy for extract : select member_id where task_id="30"
but a little bit difficult to insert.

What cases do you see inserts to be difficult for?
#3

[eluser]Dam1an[/eluser]
Jedd is pretty much spot on
Also, seeing as this will be abstracted in a model, it doesn't matter is it is hard (which I don't think it is), as you could pass in an array of user IDs and loop through them doing each insert, or a comma seperated string, explode it and loop through etc

And if you're using MySQL, you can even do multiple inserts in a single query, so it's no extra queries, syntax is something like
Code:
INSERT INTO `assignment` (`task_id`, `member_id`) VALUE (30, 1), (30, 4), (30, 8)
#4

[eluser]pabloheim[/eluser]
[quote author="jedd" date="1250481604"]Howdi,

You are correct - strings containing multiple integers, separated by commas .. is not a good way of recording this information.

Quote:i have another idea : imagine we have a table called assignments. it has two columns : "task_id" and "member_id". If the boss asks members 1, 4,8 to solve the task "30", then this is stored like this:

You are on the right track here. Typically you would have an id column, AUTO-INC, UNIQUE, etc - as your primary key.

And you'd call the table by the name of the two tables you're connecting - so member_task (usually alphabetical ordering). This is a bit traditional Active Record (as opposed to CI's AR) but consistency is good, and their standard is as good as any. You'd also make the two columns you've mentioned here as INDEX, of course.


Quote:This solution is easy for extract : select member_id where task_id="30"
but a little bit difficult to insert.

What cases do you see inserts to be difficult for?[/quote]

hi, thanks for comment.
i think it could be a little bit difficult because the function that insert the data receives a string like this "1,4,8". Thus, i have to separate before insert. and i need to know how many ids are to pass this parameter to a loop function like for or foreach.
Its not super difficult, but it needs some attention.

i receive a string because in my project, when the boss selects the team to solve the task, he does it throught a drag and drop system made with jquery ui. then when he click "Assign" , a little js script takes an "ul" whose "li" are team members and catch the memebers id and then pass as a value to a hidden input to finally submit the form.

I think im going for the solution you suggest, its more flexible. thanks a lot!
#5

[eluser]pabloheim[/eluser]
[quote author="Dam1an" date="1250481868"]Jedd is pretty much spot on
Also, seeing as this will be abstracted in a model, it doesn't matter is it is hard (which I don't think it is), as you could pass in an array of user IDs and loop through them doing each insert, or a comma seperated string, explode it and loop through etc

And if you're using MySQL, you can even do multiple inserts in a single query, so it's no extra queries, syntax is something like
Code:
INSERT INTO `assignment` (`task_id`, `member_id`) VALUE (30, 1), (30, 4), (30, 8)
[/quote]

hi, thanks for comment.
i think what you suggest its going to be very useful. IM using mysql, so i could build a query iterating for each id i explode .
thanks a lot!!
#6

[eluser]pmoroom[/eluser]
Will your table have nested relationships? If so I suggest the following link for reading. I had to learn all about this in the last two weeks as I build my application. It was complex for me, but I finally got it. Let me know if you need some examples.

http://dev.mysql.com/tech-resources/arti...-data.html




Theme © iAndrew 2016 - Forum software by © MyBB