Hi guys

I need some help with this one.

I have 4 drop down selects (HTML form elements) on a page that uses PHP, Javascript and AJAX, and they change based on a LOOSE parent-child relationship defined by a MySQL databse.

Let me explain, imagine it was for a Business Activity and Sales system:

four drop down fields:
1.BusinessDepartment
2.EventName
3.EventDetail
4.EventStatus

So on the form in the web browser you would:

1. select the Business Department (determines the Event Names displayed in the second Drop Down field)
2. choose an Event Name (again, determines the options for the third Drop Down field)
3. choose the Event Detail (..same again..)
4. and the onto the current Event Status, which is the last.

ALL programmatically (PHP and Javascript) inter-dependant on each other within a LOOSE parent-child hierarchical structure.

---
So, I need to determine the best way to store the different variables for each level, and the interaction between each level, in a MySQL database.

Here are ALL the options for each drop down menu, but without relationship detail, just all the variable/field names for them on the relevant levels:



For example, combinations that would be allowed:

Retail > Outright Purchase > Mobile Phone > Provisioning
IT > Programming Request > Bug Fix > Assigned
Corporate > Service Issue > Internet > Completed
Corporate > Meeting > Other > Confirmed

But you could never have:

Retail > Outright Purchase > Bug Fix > ..
IT > Outright Purchase > .. > ..
Corporate > Programming Fix > .. > ..

So some options can be used by more than one combination, and all available combinations need to be defined, somehow ensuring restrictions are in place.

I want to keep these restrictions and rules for combinations to ONLY the database so they can be updated and altered by another 'Administration' form.

It is obviously up to the combination of server-side and client-side scripting (PHP and Javascript) to produce the end result, but what is the best way to represent the relationships in a MySQL Database?

• Should I use just one massive flat-file table with all the possible combinations? Space isn't an issue.
• Should I use 4 tables to represent each of the items in the 4 groups, and link them using a 5th table of keys?
• ..any other ideas..? :confused:

I have searched and read many resources, but nothing has worked close to a solution I require.

Any help will be much appreciated. Sorry if it's in the wrong forum, but i think it's the correct one.

Would appreciate any comments as I have made no progress with this.

Cheers.