I need to create a simple database to keep track of what cars are in what tracks and in what sequence. I'm having trouble with the tables and relationships to accomplish this task. An example end result will be a track list which can be marked up to perform switching tasks: TRACK 1602 - INDUSTRIAL LEAD EAST TO WEST SEQ CAR_NUMBER CAR_KIND L/E 1 ARMN 715251 REEFER L 2 BNSF 185321 REEFER L 3 ARMN 755285 REEFER L 4 ARMN 755219 REEFER L Each car has unique information and each track has unique information, so far I have two tables CARS and TRACKS. I have a one to many relationship between CARS and TRACKS where each car has one track and each track can have many cars. I can query out all cars for each individual track. Now where I'm stuck is how do I properly design a sequence for the cars in the tracks. I added a SEQUENCE field in the CARS table to manually enter the sequence, but I can't enforce any data integrety rules with this method. Maybe I need a table for each track, or track sequence table, just having a hard time figuring out how to make this work. At the simplest form right now my tables are basically: Table: TRACKS Field: TRACK_NUMBER (primary key) (relationship - 1->n CARS:TRACK NUMBER) Field: TRACK_NAME Field: TRACK_LENGTH Table: CARS Field: ROAD_NAME (primary key) Field: ROAD_NUMBER (primary key) Field: CAR_KIND Field: CAR_OWNER Field: TRACK_NUMBER (relationship - n->1 TRACKS:TRACK NUMBER) Taking a stab somebody out there is designing a database and might be able to help. It's hard to believe I can't remember this database stuff because it was a huge part of my college major Thanks for any help,
Switchlist Switch List Application I’ve been working on a new switch list operations application. The application only needs to provide basic information. I just need to know what cars are where, and where they are going next. And maybe car type and length. I built the application with a WordPress plug-in called DB-Tookit. It interfaces with the mySQL database back end and helps build the front in forms and UI. It’s a fairly good program, but I’ve run in to a few limitations. For instance, I haven’t been able to select out multiple fields from a joined table. I know how to do it in SQL, but DB-Toolkit doesn’t have an interface to write your own queries. My main goal with DB-Toolkit is to have an effective database interface for the back end. I’d like to have a nice web-based front end interface that will present the user with sortable track lists; with the capability to sort cars by drag and drop from track to track. I found a couple of JQuery drag & drop scripts to try this out. The most promising is Touch Punch, because it also supports touch gestures like on iPad/iPhone. http://furf.com/exp/touch-punch/ I Especially like the Sortable lists, or a combination of them. Another example: http://furf.com/exp/touch-punch/examples/sortable/handle-empty-lists.html. Of course when a user updates the sortable track lists, I’d like to enter that data back to the database. Before I get deeper into this project, I need to decide if I really want to take the time to learn relearn PHP and Javascript to develop a decent app. I don’t want to get to far off course. Other Programs Before I started developing the switch list application I took a hard look at what existed. I spent a few days working with Easy Model Railroad Inventory but I don’t think it will suit my needs. I really like a program called SwitchList, but it is only available for Mac OS X 10.5, I have a PC. I’ve also tried building a program with OpenOffice Database & Spreadsheet, but I’d really like this to have a web interface. Originally Posted on The Industrial Lead
