Is this MySQL query a better idea?
In a project I am working on, there are two types of accounts: " people
" and " companies
".
I keep one table " users
" with all the accounts and just the basic information needed to login (email, walkthrough, etc.) and two other tables " user_profiles
" (regular people) and " company_profiles
" (companies) that contain more specific columns for each type, both tables linked to the common table " users
" through the column " profile_user_id
".
But, when I want to list users, which can be both people and companies, I use:
" select user_id, user_type, concat_ws('', concat_ws(' ', user_profiles.profile_first_name, user_profiles.profile_last_name), company_profiles.profile_company_name) as user_fullname
".
When I list these users, I know if they are people or companies " user_type
".
Is my approach using the concat_ws
correct (optimal) one? I did this instead select
for each *_name
to avoid returning more columns than necessary.
thank
EDIT: The request above continues like this: from users left join user_profiles on ... left join company_profiles on ...
source to share
select
u.user_id, u.user_type, concat_ws(profile_first_name + profile_last_name) as full_name
from
users u, user_profiles up
where u.key = up.key
and u.user_type = 'user'
union
select
u.user_id, u.user_type, concat_ws(profile_first_name + profile_last_name) as full_name
from
users u, company_profiles cp
where u.key = cp.key
and u.user_type = 'company'
source to share
Do you already have a job? Are you already running into performance issues with this approach?
If using the above query is taking longer than you expect, or it is causing problems in the software calling this information, it might be pre-optimization.
One note: the first use of CONCAT_WS has no delimiter, so the company name will be concatenated with the person's name.
source to share