Database Setup Multiple Tables and Multiple Options
I am have been going around in a circle on this one for days and now could really do with some suggestions or a fresh pair of eyes as to how I handle the following:
I have 3 tables:
Occupation Description etc
Job Location Table
The situation is that for each occupation there are a number of possible Employers who are willing to offer employment for these occupations. Each Employer has say 8 potential work locations, but they only want certain occupations for certain locations.
The Occupations Table has over 500 entries. The Employer table has 10 entries and the Location has 8 entries.
An example is:
Company A - wants Carpenters in location 1, 2 and 5
Company B - wants Carpenters in locations 4 and 8
Company C - wants Carpenters in all locations
Company D - doesn't want any Carpenters.
I am really not sure how to set this up to make this work with DataAssist.
I want to be able to retrieve the data to sort/filter and to display as follows:
1) Retrieve an occupation and display the employers that want to employ that occupation and in what location.
2) A list of occupations that an employer is willing to employ and in what locations:
Plumber Location 1, 2 and 4
Bricklayer Location 4, 5, 6 and 8
3) Finally a list by location as follows:
Plumber, Company A and B
or Locations 1 & 4
Bricklayer, Company A, B and D
Plumber, Company A, C, E and F
Hope this makes sense. I am confident in joining tables in dreamweaver, but not sure how to set this up in the first place, even whether my table structure is right - do I need more tables? How to input the data, especially as there are multiple options that I also need to sort/filter by.
I am using PHP, MySQL (using PHPmyAdmin)and have WA Super Suite.
Really grateful for any help as I think I have been at it too long and can't see the wood for the trees.
Whoops sorry also should have mentioned that the employer and location requirements data changes on a weekly basis e.g. Employer A might want carpenters in location 1 and 4 one week, but locations 6 and 8 the next, then the following week not want any carpenters. So need to be able to update this information easily - at the moment they work on lists supplied by each employer so being to update by employer is really important also. I know a nightmare!!