 Re: Is it better to have a table for each user in
a mysql dB?
|
Fione Housman |
|
10/30/2008 3:24:22 AM |
Eleganta, 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.
Post Comments |
|
Re: Thanks for the replies! |
Eleganta Coerulea |
|
10/30/2008 3:27:18 AM |
Fiona, 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.
Post Comments |
|
Re: Is it better to have a table for each user in
a mysql dB? |
Borris O'Reilly |
|
10/30/2008 3:25:18 AM |
Hi Eleganta, You might want to try PHPs serialize() and unserialize() functions to store arrays in mySQL databases... check out http://www.tiffanybbrown.com/article...article.php/59
and thanks for asking the question. I didn't know this till I search for it
Post Comments |
|
Re: I've read a few things on serialize |
Eleganta Coerulea |
|
10/30/2008 3:28:23 AM |
Borris, 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?
Post Comments |
|
Re: Is it better to have a table for each user in
a mysql dB? |
Bhairavi Hussain |
|
10/30/2008 3:30:13 AM |
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']}
'"
Post Comments |
|
Good point - I should've been more specific in my initial reply. I should've said &quo
t;use one user-related table". The actual schema will have multiple tables (most likely m
ore 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.
Post Comments |
|
