[thelist] MySQL storage recommendation
Matt McKeon
matt at camadro.com
Wed Sep 6 20:00:55 CDT 2006
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
More information about the thelist
mailing list