Click & Drag AJAX into Database

[webfoo]

I have the task of creating a MySQL/PHP/AJAX application which shows the current status and location of about 25 vehicles.

There are three different types of vehicles (We'll call them type A, B, and C). Each vehicle is at one of three locations (1, 2, or 3). In addition, type A vehicles are assigned a numerical position at their location. All vehicles are also assigned a "status" indicating the current usage capacity of that vehicle.

So I have Table A, B and C in my database, holding each respective type of vehicle.

A record from Table A might look like:

vehicleID, location, position, status;
52U, 1, 3, reserved;

Records from tables B and C looks similar, but without the "position" field:

vehicleID, location, status;
1R7, 3, broken;

I am envisioning a page with three columns (for the three locations). Then in each column, there are three groups of vehicles (Type A, B, and C). The user can click any vehicle and drag it to either of the other two locations. Group A vehicles are order-specific, that is, the user drags the vehicles into the desired order at each location. In addition, the user needs to be able to change the status for each vehicle (such as a right-click context menu with status options).

All of this needs to be live, such that the database is updated second the user has moved a vehicle from one spot to another, or changed a vehicles status.

Ideally, this system would be flexible to accommodate new locations that are opened, or remove locations that close.

This seems like an awful lot for one shot. Anybody done something like this before? Ideas on where to start? Or am I out of my mind?

[webfoo]

And one other feature I forgot to mention:

When a user (who is not a "fleet manager") moves a vehicle or changes it's status, the user must enter: why the change was made, which of ~3 fleet managers approved the change, and how that aproval was made (email, phone, in person...). All of this will be entered into a database record. However, if the user is a fleet manager or system administrator, they do not need to enter this info (but a database record is still created to document the change).

[caribguy]

I would look into normalizing the database:

vehicle_models
vehicle_models_id vehicle_models_name
1 Porsche 911
2 Ford Pinto
3 Audi A6

status
status_id status_name
1 operational
2 broken
3 transit
4 available

vehicles
vehicle_id vehicle_types_id vehicle_status_id
1 1 1
2 2 1
3 1 4

etc...

[webfoo]

Thanks caribguy. I'm not sure I follow how this accomplishes what I need?

[caribguy]

Your data structure becomes more flexible, so that when more than 25 cars are added or new locations are opened, or new rules that pertain to users are created you won't have to redo all of your previous work. From the way you described your tables it looks like you're treating them like spreadsheets. Not the right way to approach this...

Your ajax drag-n-drop is just an interface layer on top of your database. That's relatively simple. Scriptaculous has some very old (but working) examples.

Get the data structure right first, will save you tons of headaches later... Good luck, sounds like you'll need it!