List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 5 2005 1:15pm
Subject:Re: problem with table design
View as plain text  
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

Thread
problem with table designOlivier Salzgeber5 Apr
  • Re: problem with table designMartijn Tonies5 Apr
  • Re: problem with table designMartijn Tonies5 Apr
  • Re: problem with table designRhino5 Apr