> > > 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]
