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 Table
Occupation Code
Occupation Description etc
etc

Employer Table
Employer ID
Employer Name
etc

Job Location Table
Location ID
Location Description

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:

Occupation: Carpenter

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:
e.g.
Company A
Plumber Location 1, 2 and 4
Bricklayer Location 4, 5, 6 and 8

3) Finally a list by location as follows:

Location 1
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.

Cheers

Jo

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

Your initial structure is fine, you just need a couple more tables to make the relationships between the Occupation - > Location table and the Employer -> occupation table.


I could be wrong, but it seams to me that an occupation should be allowed to relate to multiple locations; Harry the plumber may be willing to work in location 1, location 4 and location 5.


so create a linking table to create these relations:
occupationlocations:
occupationlocationID - primary key
occupationlocationOcupation ID - Foreign key to Occupation Table Occupation ID
occupationlocationLocationID - Foreign key to Job Location Table Location ID


Now for the link to locations and occupations tables from the Company table. I could be wrong again, but it seams that company a might be interested in plumbers from location 1, but not electricians, they may want electricians from location 2.

the linking table for Companies to Occupations and Locations would be:

employeroccupationlocations:
employeroccupationlocationsID - primary key
employeroccupationlocationsOLID - Foriegn key to occupationlocations.occupationlocationID
employeroccupationlocationsEmployerID - Foriegn key to Employer.EmployerID

Sorry for the delay in replying. Thank you so much for this Jason.

OK based on what you have said how do I go about entering this information as we receive the information as follows:

Each company supplies a list of what occupations they are willing to hire and in what locations:

e.g.

Company B list would look like this:

Carpenter Location 1, 2, 4
Plumber 2,3,4
Electrician 4, 5
Bricklayer 6

So I need to set up the insert and update form as follows:

Company Name: Drop Down Menu (alloiwing only one selection) at the top
Occupation : Now sure how to handle this as there are over possible 500 occupations so drop down box or check box isn't going to work. Ideally would like to type in C (say for Carpenter) and it bring up all the occupations beginning with C and I can choose from that list or put in an occupation code in (which many of the companies use which is formatted as follows: 1234-56) and that is verified in some way against the occupations table (there is an occupation code field in this table).
Locations: This can be a check box allow multiple locations.

Ideally this would bring up excisting occupations that are currently required by the chosen employer through the update multiple records behaviour and give the user the option to either update the excisting locations against the occupations listed or add new occupations or delete occupations (and obviously their related locations).

Hope this makes sense. I have been reading about MRT behaviours, but not sure if this would apply as the occupations cannot be put into a drop down list or check box because of the number of options. Plus not sure how to list this all together on one page.

Also this may seem like a really stupid question, but I notice in your tables you use different field names for the foreign key names from their original primary key names - can you use the same name or is there some reason why you should use different names in each table?

e.g.

occupationlocations:
occupationlocationID - primary key
occupationlocationOcupation ID - Foreign key to Occupation Table Occupation ID
occupationlocationLocationID - Foreign key to Job Location Table Location ID

could I use:

occupationlocations:
occupationlocationID - primary key
Ocupation ID - Foreign key to Occupation Table Occupation ID
LocationID - Foreign key to Job Location Table Location ID

Thank you again Jason. One day I will get my head around this and stop buggin you, but desperate to understand and learn, so hope you can help.

This really is not a small project, it is a very large one.

To be honest, due to some of the limitations you mention, it will require a fair bit of and coding to complete. It is not a project I wold recommend as a way of becoming familiar with DataAssist and relational data.

This is going to require created Lookup recordsets. Lookup recordsets are used to get one particular record to return the ID.

It is going to require createing recordsets nested in other recordset repeat regions.


Where you cannot use multiple select lists or checkboxes, you will need to add ajax functionality to create an input box to display information from the database based on what is typed into the box.

I would start with using the data assist wizard to create pages for the Companies table.

On the detail page, add a link to a new page to manage the occupations / locations.

Use a querystring to pass the company ID.

add a hidden form element that will get the value passed from the quertystring.

You will really only be able to add one ocupation / location at a time.

The ocupation list is where you will need to use ajax functionality, we do not have a tool for this, but you should be able to find something through google.

When they select an ocupation, and a location, create a recordset to lookup in the occupationlocations the selected ocupation / location and return the occupationlocationID


This will be store in the employeroccupationlocations.occupationlocationOcupation ID table.

The employeroccupationlocations.employeroccupationlocationsEmployerID will get the value from the hidden form element.

Thanks Jason - I think I may have bitten off more than I can chew, but going to continue. I have made one slight change as follows to prevent entering one occupation and one location at a time, which will take so long it will defeat the object of automating it.
My tables now are:

Employer Table
Employer ID
EmployerName
EmployerContact
etc etc

Occupation Table
Occupation ID
Occupation
Salary
etc etc

EmployerOccupationJoin Table
EmployerOccID
EmployerID
OccID
Location1
Location2
Location3
Location4
Location5
Location6

The location fields are designed to be either yes or no. Firstly do you think this would work?

Secondly how is it best to bind data within a form. For example I would like to make the form user friendly so the person inputting the data can choose from the occupation description (which I have set up using ajax as you suggested), but what is actually entered is the OccupationID. Is this possible and if so how would I do about doing this?

Hi Jason

Guess what it works!! We can input the location data through the relationship table with Employer as the default (hidden field), as you suggested and just adding occupations (occupations is a menu list: id as value and Description as label) and their locations - locations are check boxes (Check = Yes Unchecked = No) against the occupation.

Also used SQLyog for building the queries (linking Job Details, Employer Details and Locations) and loaded them into record set to display the data.

Just set up multiple inserts and now working on multiple updates.

Thank you for your help Jason - couldn't have got my head around it without you.

Jo

Hi there

I have been reading thru some of the threads on the forum to fimiliarise myself with DataAssist. However dataAssist has has some mean limitations.

1. It creates a resulst page based on the idea that it is an ecommerce page.
2. It only creates 5 fields from a record set.
3. It only sees one recordset not 2 or 3.
4. The wizard is not so intuitive.

If I had to create a results page this is how I would go about it.

1. Look at availble recordsets and then choose the ones to add to my page.
2. From each recordset choose the fields to add to my page.
3. The layout should be more flexible in that one should be able to drag the selected fields and bind it to a predesigned layout.

This will be more flexible and easier to work with than the way it works now.To much trouble for an out of the box solution.

Many thx
M.Ajam

You mentioned a couple of things here that you do not like about DataAssist so I just wanted to clear up a couple of these points.

1. There are multiple page styles that can be used for the results, you can use an ecommerce style or an administrative backend style depending on what you select in the wizard.

2. DataAssist can work with an existing recordset or you can have the recordset crafted by the wizard, there is no limitation by DataAssist on the number of fields that can be used in the recordsets.

3. When you are working with the DataAssist Search server behavior the idea is that you have a single recordset that will return all possible results and you craft the search criteria to filter the results based on your selections.

4. Please give us some feedback on how the interface could be made more intuitive for you, this will help us to improve our products going forward.

In general DataAssist is doing the behind the scenes stuff for you, when it comes to how the results page looks you can customize the layout in whatever way you would like. You can take bindings for the recordset and drag them onto your page wherever you would like them to be.