[thelist] MySQL storage recommendation

[Ken Moore]

Matt,

First, about duplicate data. If two users entering the exact same data is a 
duplicate, do not store it. If it is considered different, then do store it.

Second, speed is no problem. Using PHP embedded SQL commands is very fast as 
long as the data is stored in third normal form. That means that there is no 
duplicated data fields in a record such as two numbers and two texts. Each 
one must be in its own record and related to a user.

Finally, there are many examples of embedded SQL in PHP that you could use. 
Google for them.

If you want to discuss this in length, we can take this off line.

Ken


>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
>--

_________________________________________________________________
Check the weather nationwide with MSN Search: Try it now!  
http://search.msn.com/results.aspx?q=weather&FORM=WLMTAG