mysql setup question!

I am trying to develop a program that gives different metrics of a website given just the URL. You can setup an account and the dB will store all the information collected for the URL(s). There is a check all button, so you can check the metrics on all the URL's with one click and it updates in the dB. I am having a problem because I don't know if I should make a new table for each new user who signs up or if I should just add a row to a users table. Right now I just add a row, but this is limiting the number of URL's a person can store in the dB (well, atleast I don't know how to save arrays into a mysql dB). So my question is... Is it better to have a table for each user or one table with all users information?

For login purposes I would have one table (for all users) with user login information, then another table for each user that has specific site information.

Thanks,
Ryan

 

Use one table. Multiple tables cannot be easily queried (e.g. if you ever need to get info on more than one user; you would have to create select's dynamically and add tables on the fly).

As for arrays, you can store them in a varchar or a text column and parse the content to recreate the original arrays. If your arrays are fixed in size, or limited to a reasonable number of values, you can map each value to a column - this would make it easy to query individual values without using substring queries.

J.D.

 

You might want to try PHPs serialize() and unserialize() functions to store arrays in mySQL databases... check out http://www.tiffanybbrown.com/articles/viewarticle.php/59

and thanks for asking the question. I didn't know this till I search for it

 

Thanks for the replies!
I will have to look into parsing to get the original array out. I didn't know that was possible (of course there are a lot of things I don't know how to do )
The arrays are not going to be fixed in size so I can't match each value to a column. That's how I have it done right now, but I want the user to be able to input more URL's (maybe up to x URL's, but each user would have a different number of URL's). Also for each URL there are atleast 7 different metrics I want to store and on top of that I would like to keep historical data on the different metrics.

I've read a few things on serialize and unserialize that lead me to believe it's the wrong way to do this. Do you or does anyone have any experience with these functions to get the arrays to go in and come out properly?

 

A better approach to do what you are trying to do might be to split the data into two tables:
<user_table>
<user_id>
<user_name>
<full_name>
<as may attributes as you like>
<etc>
</user_table>
<url-table>
<url_id>
<user_id>
<url>
<url_status> (i.e. active/inactive)
<more_url_attributes>
<etc>
</url_table>

Then to get the data from two tables use:
"Select * from user_table, url_table where user_table.user_id = url_table.user_id and user_table.user_id = '{$_GET['id_i_care_about']}'"

If you need all rows from user_table regardless if there are urls:
"Select * from user_table LEFT OUTER JOIN url_table ON user_table.user_id = url_table.user_id where user_table.user_id = '{$_GET['id_i_care_about']}'"

 

Good point - I should've been more specific in my initial reply. I should've said "use one user-related table". The actual schema will have multiple tables (most likely more than two). The rule of thumb is that if you have any duplication of data in the database (e.g. same entry/exit URL value in more than one record), there's something wrong with the schema and you need to normalize it.

J.D.