I'm wanting to make sure my design is solid, so I thought I'd ask for feedback here. It particularly pertains to making it fast to search.
Let's assume I have lots of tables in my MySQL database, they could be like this:
Code: Select all
car_cc
car_tyres
car_cylinders
car_stereo
car_speakersCode: Select all
user_table
-username
-user_age
-user_gender
-user_cc
-user_tyres
-user_cylinders
-user_stereo
-user_speakersSo far so good (I hope you're still with me
The actual question is:
(Q)-Should the 'user_table' contain the actual data from within the other tables, or should the 'user_table' contain links to the id's containing the data in the other tables?
The reason for this question is speed. Let's assume the site is VERY popular and I have say, 10,000 people all doing a search at the same time.
Method 1
If I have the actual DATA in the user_table, the search is as simple as "show me all users vehicles with 4 tyres, a big stereo and that have 6 cylinders'. The search should be fast as it only searches the one table, table 'user_data'.
Method 2
If I have links to the DATA in the OTHER tables, then it become more akin to: Search user_table, check user_table and compare user_tyres against car_tyres, compare user_stereo against car_stereo and compare user_cylinders against car_cylinders.
I'm guessing method 2 would be MUCH more intensive? Is this so? Is the hit on processing power etc dramatically different?
I think I'd rather run on method 2, but I'd like feedback, and I really appreciate anything anyone has to say as it's going to shape my whole site and I'm just about ready to move into this aspect of the site.
I look forward to some good discussion
Rob