Linking multiple records from another table

[Dale]

I'm not sure I'm even thinking about this the right way, but here goes:

I have a table of users. Each one of these users may be associated
with none, one, or many records in another table I call a todo table.
table user = 'id', 'name', 'bla bla bla','todo_list '
table todo = 'id','title','o ther info'

Suppose user 'id=1253' has on his todo list items 756,241, and 672. I
create a string 756,241,672 and store that string in the user's
todo_list field. Then when I want to display the todo items I get the
string with a query, bust it up into an array, iterate the array and
query the todo table.

I have a gut feeling I'm making it way more complicated than need be.
But I can't think of any other way to do it

[d]

Re: Linking multiple records from another table

"Dale" <dale.drinkard@ gmail.com> wrote in message
news:1140703564 .436036.79490@e 56g2000cwe.goog legroups.com...[color=blue]
> I'm not sure I'm even thinking about this the right way, but here goes:
>
> I have a table of users. Each one of these users may be associated
> with none, one, or many records in another table I call a todo table.
> table user = 'id', 'name', 'bla bla bla','todo_list '
> table todo = 'id','title','o ther info'
>
> Suppose user 'id=1253' has on his todo list items 756,241, and 672. I
> create a string 756,241,672 and store that string in the user's
> todo_list field. Then when I want to display the todo items I get the
> string with a query, bust it up into an array, iterate the array and
> query the todo table.
>
> I have a gut feeling I'm making it way more complicated than need be.
> But I can't think of any other way to do it[/color]

You're nearly there. Instead of storing a comma-seperated list of IDs, use
a third table, say "chores" or something that fits in with your data. Give
that table 3 columns - an ID, a user ID, and a todo ID. To link a user with
a task, insert their ID and the task's ID into that table. To get the
user's tasks, simply join the chores table with the todo table, and search
for a particular user ID. That's a much more efficient way of storing such
relationships.

Is that cool?

dave

[Jerry Stuckle]

Re: Linking multiple records from another table

d wrote:[color=blue]
> "Dale" <dale.drinkard@ gmail.com> wrote in message
> news:1140703564 .436036.79490@e 56g2000cwe.goog legroups.com...
>[color=green]
>>I'm not sure I'm even thinking about this the right way, but here goes:
>>
>>I have a table of users. Each one of these users may be associated
>>with none, one, or many records in another table I call a todo table.
>>table user = 'id', 'name', 'bla bla bla','todo_list '
>>table todo = 'id','title','o ther info'
>>
>>Suppose user 'id=1253' has on his todo list items 756,241, and 672. I
>>create a string 756,241,672 and store that string in the user's
>>todo_list field. Then when I want to display the todo items I get the
>>string with a query, bust it up into an array, iterate the array and
>>query the todo table.
>>
>>I have a gut feeling I'm making it way more complicated than need be.
>>But I can't think of any other way to do it[/color]
>
>
> You're nearly there. Instead of storing a comma-seperated list of IDs, use
> a third table, say "chores" or something that fits in with your data. Give
> that table 3 columns - an ID, a user ID, and a todo ID. To link a user with
> a task, insert their ID and the task's ID into that table. To get the
> user's tasks, simply join the chores table with the todo table, and search
> for a particular user ID. That's a much more efficient way of storing such
> relationships.
>
> Is that cool?
>
> dave
>
>[/color]

You don't even need an id column in the third table. Just user id and
todo id are sufficient. Primary key would be both columns.

Link tables generally don't have an id associated with each entry.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Dale]

Re: Linking multiple records from another table

Didn't think of that. That makes much more sense than what I had
kludged together. Many thanks!

Dale.

[Justin Koivisto]

Re: Linking multiple records from another table

d wrote:[color=blue]
> "Dale" <dale.drinkard@ gmail.com> wrote in message
> news:1140703564 .436036.79490@e 56g2000cwe.goog legroups.com...[color=green]
>> I'm not sure I'm even thinking about this the right way, but here goes:
>>
>> I have a table of users. Each one of these users may be associated
>> with none, one, or many records in another table I call a todo table.
>> table user = 'id', 'name', 'bla bla bla','todo_list '
>> table todo = 'id','title','o ther info'
>>
>> Suppose user 'id=1253' has on his todo list items 756,241, and 672. I
>> create a string 756,241,672 and store that string in the user's
>> todo_list field. Then when I want to display the todo items I get the
>> string with a query, bust it up into an array, iterate the array and
>> query the todo table.
>>
>> I have a gut feeling I'm making it way more complicated than need be.
>> But I can't think of any other way to do it[/color]
>
> You're nearly there. Instead of storing a comma-seperated list of IDs, use
> a third table, say "chores" or something that fits in with your data. Give
> that table 3 columns - an ID, a user ID, and a todo ID. To link a user with
> a task, insert their ID and the task's ID into that table. To get the
> user's tasks, simply join the chores table with the todo table, and search
> for a particular user ID. That's a much more efficient way of storing such
> relationships.
>
> Is that cool?[/color]

I usually use a 2-col table and make a unique index with the two fields
to prevent dups. ;) It works quite well, but it makes things just
slightly more tedious when deleting tasks or users because you now need
to look into additional tables for cleanup.

--
Justin Koivisto, ZCE - justin@koivi.co m

http://koivi.com

[Justin Koivisto]

Re: Linking multiple records from another table

Jerry Stuckle wrote:[color=blue]
> d wrote:[color=green]
>> "Dale" <dale.drinkard@ gmail.com> wrote in message
>> news:1140703564 .436036.79490@e 56g2000cwe.goog legroups.com...
>>[color=darkred]
>>> I'm not sure I'm even thinking about this the right way, but here goes:
>>>
>>> I have a table of users. Each one of these users may be associated
>>> with none, one, or many records in another table I call a todo table.
>>> table user = 'id', 'name', 'bla bla bla','todo_list '
>>> table todo = 'id','title','o ther info'
>>>
>>> Suppose user 'id=1253' has on his todo list items 756,241, and 672. I
>>> create a string 756,241,672 and store that string in the user's
>>> todo_list field. Then when I want to display the todo items I get the
>>> string with a query, bust it up into an array, iterate the array and
>>> query the todo table.
>>>
>>> I have a gut feeling I'm making it way more complicated than need be.
>>> But I can't think of any other way to do it[/color]
>>
>>
>> You're nearly there. Instead of storing a comma-seperated list of
>> IDs, use a third table, say "chores" or something that fits in with
>> your data. Give that table 3 columns - an ID, a user ID, and a todo
>> ID. To link a user with a task, insert their ID and the task's ID
>> into that table. To get the user's tasks, simply join the chores
>> table with the todo table, and search for a particular user ID.
>> That's a much more efficient way of storing such relationships.
>>
>> Is that cool?[/color]
>
> You don't even need an id column in the third table. Just user id and
> todo id are sufficient. Primary key would be both columns.
>
> Link tables generally don't have an id associated with each entry.[/color]

hmm... maybe I should read the entire thread before I start posting... ;)

--
Justin Koivisto, ZCE - justin@koivi.co m

http://koivi.com

[Tony Marston]

Re: Linking multiple records from another table

What you are describing is a classic "many-to-many" relationship. Take a
look at http://www.tonymarston.net/php-mysql/many-to-many.html for details.

--
Tony Marston

http://www.tonymarston.net

"Dale" <dale.drinkard@ gmail.com> wrote in message
news:1140703564 .436036.79490@e 56g2000cwe.goog legroups.com...[color=blue]
> I'm not sure I'm even thinking about this the right way, but here goes:
>
> I have a table of users. Each one of these users may be associated
> with none, one, or many records in another table I call a todo table.
> table user = 'id', 'name', 'bla bla bla','todo_list '
> table todo = 'id','title','o ther info'
>
> Suppose user 'id=1253' has on his todo list items 756,241, and 672. I
> create a string 756,241,672 and store that string in the user's
> todo_list field. Then when I want to display the todo items I get the
> string with a query, bust it up into an array, iterate the array and
> query the todo table.
>
> I have a gut feeling I'm making it way more complicated than need be.
> But I can't think of any other way to do it
>[/color]