[thelist] MySQL storage recommendation

[Matt McKeon]

Ken Moore wrote:
> Hi all,
>
> Matt McKeon has a data storage question. I'm certain that I understand 
> what you want but I seldom  let that stop me. ;-).
>
> The problem with databases is seldomly storing the data. It is as you 
> said, finding it later. In order to find, update and edit the data 
> later, I would suggest that each table have the following structure:
>
> item_id:          autoindex
> user_id:          foreign key to user table
> num_entered: text
> text_entered: text
>
> Now you can do a lookup based on the user and the number they entered.
>
> If you are using a later version of MySQL, you can use triggers 
> (before-insert-trigger) to limit the number records that can be added. 
> If not, you can use PHP to check how many records there are before entry.
>
> Ken
>
>>
>> I am working on a project currently in PHP & MySQL where there will be
>> content generated by users that needs to be stored, big surprise. But
>> the question I have is if the way I am starting it is efficient.
>>
>> <snip>
>>
>> Thanks in advance,
>>   matt

Thanks Ken, I kinda follow you. I was thinking of using a separate table 
for each list, but I am not really seeing it in my head I guess.
Are you purposing that table structure for each list table? I already 
have a table for the items and users.
Right now the users table looks like this:
user_id   auto increment
...
user_list1   text
user_list2   text
those two fields look like this [234]text text text[4]text text

and the item table:
item_id       auto increment
item_name   varchar
...
item_default_desc   text


So what I'm confused about is if using the structure you propose 
wouldn't there be many duplicates. If many users each had the same item 
then there would be multiple rows with that ID. I suppose that could 
work, but wouldn't the queries be slow because they have to search 
through multiple rows then search again through them to find which of 
those rows belonged to a certain user?

Maybe I've missed the point, or am over thinking this. If anyone could 
enlighten me that would be great!
(I'll look into triggers as I've never used them.)

 -matt