[thelist] MySQL storage recommendation

Ken Moore psm2713 at hotmail.com
Wed Sep 6 18:59:45 CDT 2006


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.
>
>There will be two seperate "lists" that the users can contain anything
>from an available pool, one of them is limited in number and the other
>is only limited in the number of available options, which currently is
>around 300 items.
>
>I have one table holding those items call it tbl_items, and another
>table holding user data, including those lists in Text fields called
>tbl_users.
>I devised my own storage method similar to serialize but is easier to
>read. It simple puts the id number of an item in brackets followed by a
>string of text that users can input for each item, limited to 145
>characters. It looks like this: [23] some text users put in[39] text
>about this new item[09] ...
>
>So my question is this a good way to store the data? I had to do some
>tweaking of my PHP code in some areas because certain operations over a
>large list, say all items, took forever. So it really got me thinking if
>this was a good storage paradigm. I need to be able to search through
>this data and sort it, update it, delete it, and so on. I'm sure some
>other guru has ran into similar issues, anyone have any suggestions of
>how to tackle this storage problem?
>
>Thanks in advance,
>   matt

_________________________________________________________________
Windows Live Spaces is here! It’s easy to create your own personal Web site. 
  http://spaces.live.com/signup.aspx




More information about the thelist mailing list