MySQL view performance for denormalization

Question

I am currently writing my truly first PHP application, and I would like to know how to properly design / design / implement MySQL views;

In my particular case, user data is spread over several tables (as a result of normalizing the database), and I thought of using View to group the data into one large table:

CREATE VIEW `Users_Merged` ( name, surname, email, phone, role ) AS ( SELECT name, surname, email, phone, 'Customer' FROM `Customer` ) UNION ( SELECT name, surname, email, tel, 'Admin' FROM `Administrator` ) UNION ( SELECT name, surname, email, tel, 'Manager' FROM `manager` ); 

That way, I can easily use View data from a PHP application, but I really don't know how much this can affect performance.

For instance:

 SELECT * from `Users_Merged` WHERE role = 'Admin'; 

Is there a proper way to filter view data, or should I filter before creating the view itself? (I need you to have a list of users and functions for filtering them by role).

EDIT

In particular, I am trying to get the Denormalization of three tables into one. Is my decision right? See Denormalization on wikipedia

Answer 1

In general, the database engine will perform the optimization for you. This means that the engine will find out that the user table must be filtered before joining other tables.

So go ahead and use your view and let the database worry about it.

If you find poor performance later, use MySQL EXPLAIN to get MySQL to tell you what it does.

PS: Your data design allows only one role for the user, is that what you wanted? If so, and if the example request you provided is the one you intend to run frequently, be sure to index the role column in users.

Answer 2

If you have 1000 users (which seems likely), it really doesn't matter how you do it. If the list of users is unlikely to change over long periods of time, the best thing you can probably do in terms of performance is to load the list of users into memory and not go to the database at all. Even if the user data had to change at the same time, you could update the structure in memory as well as the database and, again, not read the user information from the database.

Answer 3

It would probably be much better for you to normalize administrators, users, managers, and what you want into one single table with a column of the Role discriminator, which would save a lot of duplication, which is essentially the reason for normalization in the first place. You can then add role details to the different tables that you use with the User table in the join.

Your request may look as simple as:

 SELECT `Name`, `Surname`, `Email`, `Phone`, `Role` FROM `User` WHERE `User`.`Role` IN('Administrator','Manager','Customer', ...) 

Which is also easier to process the database than union s

If you take one more step, you can add a UserRoleCoupling table (instead of the Role column in User ) that contains all the roles that the user has for each user:

 CREATE TABLE `UserRoleCoupling` ( UserID INT NOT NULL, -- assuming your User table has and ID column of INT RoleID INT NOT NULL, PRIMARY KEY(UserID, RoleID) ); 

And put the actual role information in a separate table:

 CREATE TABLE `Role` ( ID INT NOT NULL UNIQUE AUTO_INCREMENT, Name VARCHAR(64) NOT NULL PRIMARY KEY (Name) ) 

Now you can have multiple roles per user and use queries such as

 SELECT `U`.`Name` ,`U`.`Surname` ,`U`.`Email` ,`U`.`Phone` ,GROUP_CONCAT(`R`.`Name`) `Roles` FROM `User` INNER JOIN `UserGroupCoupling` `UGC` ON `UGC`.`UserID` = `User`.`ID` INNER JOIN `Role` `R` ON `R`.`ID` = `UGC`.`RoleID` GROUP BY `U`.`Name`, `U`.`Surname`, `U`.`Email`, `U`.`Phone` 

Which gives you the basic User data and a comma-separated list of all Role names assigned.

In general, the best way to normalize the database structure is to make the tables as universal as possible without being redundant, so do not add data about administrators or clients to the user table, but use the connection between User and Administrator to find specific details of the administrator . The way you do it now is not really normalized.

I will see if I can find my favorite book on database normalization and publish ISBN when I have time later.