I am currently writing my truly first PHP Application and i would like to know how to project/design/implement MySQL Views properly;
In my particular case User data is spread across several tables (as a consequence of Database Normalization) and i was thinking to use a View to group 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`
);
This way i can use the View's data from the PHP app easily but i don't really know how much this can affect performance.
For example:
SELECT * from `Users_Merged` WHERE role = 'Admin';
Is the right way to filter view's data or should i filter BEFORE creating the view itself?
(I need this to have a list of users and the functionality to filter them by role).
