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 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. Regards, Olivier Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with table design
Hi, 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 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. Well, you could add the concept of job_group. Eg, create a table: job_groups jg_id, integer description varchar Add a group: 1, electrician 2, plasterer Now, if any job can belong to a single group, add a column job_group to your jobs table. When finding results for a certain job, you can check it's job_group and select any jobs from that group as well. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: problem with table design
On Apr 5, 2005 3:15 PM, Martijn Tonies [EMAIL PROTECTED] wrote: Hi, 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 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. Well, you could add the concept of job_group. Eg, create a table: job_groups jg_id, integer description varchar Add a group: 1, electrician 2, plasterer Now, if any job can belong to a single group, add a column job_group to your jobs table. When finding results for a certain job, you can check it's job_group and select any jobs from that group as well. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Thanks for your reply. I see this could be a possible solution. But isn't it possible to solve this problem somehow without having to create an additional job_group table? Regards, Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with table design
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 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. Well, you could add the concept of job_group. Eg, create a table: job_groups jg_id, integer description varchar Add a group: 1, electrician 2, plasterer Now, if any job can belong to a single group, add a column job_group to your jobs table. When finding results for a certain job, you can check it's job_group and select any jobs from that group as well. Thanks for your reply. I see this could be a possible solution. But isn't it possible to solve this problem somehow without having to create an additional job_group table? Well, if you do this: If I query for auxiliary worker electrician I should get this: - electrician - auxiliary worker electrician How do you expect the database engine to return electrician when you're searching for auxiliary worker electrician. How would it know that the two are related? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with table design
- 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
