MySQL Database Design – Are Junction Tables Good Practice?

database-designdatabase-sizedatabase-theoryMySQL

Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id.

In order to retrieve the data I have to use Left Join query. Doesn't that make the whole process longer to retrieve rather than having it all in one table? Eg I could have the location as text on the same table.

EDIT: Here is an example.

CREATE TABLE  `user` (
`id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL
);

CREATE TABLE `user_location` (
  `user_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL
);

CREATE TABLE `location` (
`id` int(11) NOT NULL,
  `location` varchar(45),
  `parent_id` varchar(45) 
);

Note: Please assume that all related fields are properly indexed between them.

Edit: I currently have a large database with users that retrieve their location via a junction table as described above. I was asked to optimize the database because the search results are slow. I've added memcache and it improved significantly but now I am just wondering about Left Joins.

For example, The current query is something like that:

SELECT * FROM users 
LEFT JOIN user_location 
ON user_location.user_id = user.id 
LEFT JOIN location
ON location.id = user_location.location_id;

And that is just to get the location. They have several other fields that are retrieved through junctions and they are all needed to view a user's profile. We have phone numbers, addresses, passwords, D.O.B and many others all in different tables.

In order for me to create a page for the user profile I have to send the server a large query. Now after the first time it gets cached and it's fine. But I was just wondering why would someone build their database like that?

Best Answer

If you put everything in one table, you will have a bigger, redundant table.

If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read for each query.