> 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:
> 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.
> 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":
> 1, 2, 3, 4
> 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:
Add a group:
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.
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL