Database Design

[charlee]

Im thinking and thinking and still not sure whats better to do with my database. The thing is I have a user table and a model and video table, in the user table i want to add a field for favorite models and favorite videos so im not sure if it should be good to create a FAV_MODELS table with the USER_ID field and the MODEL_ID field

USER ----> FAV_MODELS -----> MODEL

but this table is gonna be so overloaded with all the users favorites models that im scared about performance here, cause everytime i need this info i will have to go through the whole table to detect an especific user and this is gonna be repeated also with the user Favorite videos :(. I was thinking of using a pattern instead, for instance putting all the models ids along in the same field in the USER table separated by colons and i can use regular expressions to handle it so you can delete a fav_model or add other one to the end of the expression.....well, just not sure about whats better to do, i need some help if possible here

thanks in advance

[LifeinAsia]

If I understand you correctly, a user can have multiple favorite models, correct? If so, then I would do a Fav_Models table method. With proper indexing, searching through the table should be no problem. It would certaily be more efficient than putting a list of models in a field. (Searching by integer data is much faster than searching by character data.)

[charlee]

yes you got it right, a user can have unlimited favorite models, and yes i know searching by character data is always slower but still not sure because its not the same when you search by character data in a field with just 20 models ids(maybe 70 characters, so you handle it with your server php code just one retrieve), than when you search in a Fav_Models table where you have 3000 users and 20 favorite models each thats 60 000 comparisons and 20 retrieves. I also rather the fav-models table method but i need at least some tips to gain in performance :)

thanks in advance

[LifeinAsia]

Not exactly... If you're using PHP to do the searching, that means you have to retrieve all the data, then parse through it with PHP. Let the DB handle most of the work.

Another issue- let's say you make the field 70 characters that will be enough to handle a list of 20 models. What happens when you need to allow more than 20 models in the list? Or if you add more models and have over 100 (so now the model_id can be 2 digits)? Now you have to increase the field size.

But let's step back and look at how you're going to retrieve the data. Will you mainly be-
A) Looking for users with a certain model in their favorites, or
B) Pulling a user and listing all his favorites?

If A, then the fav-model table is definitely the way to go. If B, then it may be more efficient to put the list in a field (just make it big enough from the begining to allow for growth).

[charlee]

yes i got your point, but this is what i was really meaning:

FIELD METHOD - (doenst matter the amount of users)
20 models == string(long) Regular expression looks for 20 instances;
40 models == string(long)Regular expression looks for 40 instances;
100 models == string(long)Regular expression looks for 100 instances;
1000 models == string(long)Regular expression looks for 1000 instances;

TABLE METHOD
3000 users 20 models each == 60 000 comp;
3000 users 40 models each == 120 000 comp;
3000 users 100 models each == 300 000 comp;
3000 users 1000 models each == 3 000 000 comp;

i think u get the idea :)

in fact im gonna do both things A & B, depedns on what the user wants to do though, you posted the options for A and for B what do you think i should of consider for A&B?

thanks in advance

[LifeinAsia]

It doesn't exactly work that way... With properly indexed tables, the DB engine deosn't need to search through the entire table of X users times Y models- it follows the pointer to the start of the desired section and grabs the relevant results, whether you're searching by user or by model (assuming you have both fields indexed). Then it's an easy join with the Users and Models tables.

If you are searching by Model, the field method requires reading every row of data and doing a text search within the Fav_models field for each row of data.

If you are searching by User, then it's a quick lookup by the userID, but then trying to join direcly on Models will be very convoluted.

Doing both is just asking for trouble. Every time you made a change, you'll have to do it in 2 places.

Another potential issue (at least with some DBs) as you add more models- you'll eventually get to the limit of what can be stored in VARCHAR fields and will have to go to TEXT data, which can have limitations for fast searching.