----- Original Message -----
From: "Olivier Salzgeber" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, April 05, 2005 9:01 AM
Subject: problem with table design
> Hello everybody
> I'm designing a database for our new Application and have some
> problems with the following:
>
> We have a table Jobs in which we store all kind of Jobs.
> Looks like this:
>
> tbl_jobs
> -------------
> job_id, integer,
> name, varchar,
> description, varchar
>
> easy so far :)
> The problem is we also want to keep track which of the Jobs are
> related or almost the same.
>
> Example:
> If we have the following jobs:
> 1 painter
> 2 auxiliary worker painter
> 3 plasterer
> 4 auxiliary worker plasterer
> 5 electrician
> 6 auxiliary worker electrician
>
I'm not clear if you intend this to be a second table or if you are putting
the desciption in tbl_jobs; the latter would be a poor choice since there
could be many people whose job is painter and you don't want to store the
fact that job code 1 means 'painter' more than once. Therefore, I'm going to
assume that you have a second table called job_codes that is defined like
this:
drop table if exists job_codes;
create table if not exists job_codes
(job_id integer not null,
job_name varchar(50) not null,
primary key(job_id));
insert into job_codes values
(1, 'painter'),
(2, 'auxiliary worker painter'),
(3, 'plasterer'),
(4, 'auxiliary worker plasterer'),
(5, 'electrician'),
(6, 'auxiliary worker electrician');
I'm also going to assume that the description column in tbl_jobs is no
longer of any importance for this discussion.
> There will be 2 logical "groups":
> first:
> 1, 2, 3, 4
> second:
> 5, 6
>
> If I query for "plasterer" I should get the following result:
> - plasterer
> - painter
> - auxiliary worker painter
> - auxiliary worker plasterer
>
> If I query for "auxiliary worker electrician" I should get this:
> - electrician
> - auxiliary worker electrician
>
> What is the easiest way to design this?
> I thought about this the whole morning but couldn't get a solution.
>
> I hope somebody on this list can point me in the right direction.
>
I don't claim that this is the absolute best solution - someone else may
think of something better - but I would create an additional table something
like this:
create table related_jobs
job_category char(30) not null,
job_id integer not null,
primary key (job_category, job);
and populate it like this:
insert into related_jobs values
('plastering', 1),
('plastering', 2),
('plastering', 3),
('plastering', 4),
('electrical', 5),
('electrical', 6);
If you want to list everyone who has a specific job, like painter, this
query will do it:
select job_name, employee_name
from job_codes c inner join tbl_jobs j on c.job_id = j.job_id
where job_name = 'painter'
order by employee_name;
[Please note that I changed the column name in tbl_jobs from 'name' to
'employee_name' because it is much more descriptive.]
If you want to list the jobs which are related to the plastering group,
this query will do it:
select job_category, job_name
from related_jobs r inner join job_codes c on r.job_id = c.job_id
where job_category = 'plastering'
order by job_name;
If you want to list the names of everyone whose job is one of the jobs in
the plastering group, this query will do it:
select job_category, job_name, employee_name
from related_jobs r inner join job_codes c on r.job_id = c.job_id
inner join tbl_jobs j on j.job_id = c.job_id
where job_category = 'plastering'
order by job_name, employee_name;
These last two queries are not precisely what you wanted; I am going after
the data already knowing the name of the group to which the job belongs. You
wanted to start with the specific job, such as painter, and then have the
query determine the group and then determine the jobs and/or people
belonging to the group. To accomplish that, you normally use a subquery.
Unfortunately, subqueries are only supported in V4.1 and later of MySQL and
I am only running V4.0. That means I cannot test this query to be sure it
will work. Also, I don't know if you are on V4.1 or later of MySQL so it may
not be very useful to you if I showed you that query. Therefore, I will tell
you a technique that will work for any version of MySQL since it doesn't
involve a subquery: simply break the job up into two queries. The first
query needs to determine the name of the job_category that includes
'plasterer':
select job_category
from related_jobs r inner join job_codes c on r.job_id = c.job_id
where job_name = 'plasterer';
Then, simply plug the result, which is plastering, into this query to get
all of the jobs that belong to the group that includes the specific job
'plasterer':
select job_category, job_name
from related_jobs r inner join job_codes c on r.job_id = c.job_id
where job_category = 'plastering'
order by job_name;
Or, to get the people whose job is in the same group as 'plasterer', use
this query:
select job_category, job_name, employee_name
from related_jobs r inner join job_codes c on r.job_id = c.job_id
inner join tbl_jobs j on j.job_id = c.job_id
where job_category = 'plastering'
order by job_name, employee_name;
Rhino
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.2 - Release Date: 05/04/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
