database layout

[Hooch]

Hey all. I want to make one of my jobs at work easier. I have to keep track ofrailcars we have on site. We can have up to 220 cars here at one time. With about 800 -> 900 cars in the fleet. I want to keep track of: 1. Date -- June 1 2006 (VARCHAR) 2. Car ID -- ie xxx2300 (VARCHAR) 3. Area -- Area Name (TEXT) 4. Commodity -- H2O2 (VARCHAR) 5. Inbound -- 0 or 1 (INT) 6 Outbound -- 0 or 1 (INT) 7. Full -- 0 or 1 (INT) 8. Empty -- 0 or 1 (INT)Are my values correct? What I want the database to do is keep track of what is onsiteat any given time. So when a car leaves it is taken out of the DB. (I think I can get that part done with PHP.) My question is would I just need 1 table here? I'm asking because I wonder if someone with better knowledge would know of a more efficient way to makethe DB. Also, I think adding the car_id (INT AUTO_INCREMENT) field would be smart,but my here's other question. Say a railcar leave the site -id 325- This leaves a blank for that id. Can the db somehow be restructered automatically so it fills that id with the old 326 id? Then ofcourse the rest above 326 would drop down 1. Thank you Hooch

[reportingsjr]

yea, it would only take one table. It depends on what the car ids are, if they are like a10s5 then you cant do it, because auto increments can only be INTs...No, you cant have all the ones above 326 drop down one, although it would be very easy to create a php code to do that for you. make it a function and a form on a page so you just enter a number and it automatically drops everything down one..

[Hooch]

I made the table a little confusing. car_id would be a normal numerical value. But in the top part of my original post, (#2) Car ID is the actual numbers on the car. It would just be a text entry. Thanks reportingsjr, I'll give it a go.

[Hooch]

No, you cant have all the ones above 326 drop down one, although it would be very easy to create a php code to do that for you. make it a function and a form on a page so you just enter a number and it automatically drops everything down one..

<{POST_SNAPBACK}>

I'm not completely understanding what you mean, but before I ask any more questions, would this need to be done after everycar has left the site? If so, it's too much, I need to make this very simple. Since 10 other people rotate through this job. And trust me..it would get missed.

[ThePsion5]

Well, I think I may have a solution to your idea that may be better than creating code to automatically reset all the AUTO_INCREMENT IDs.Why not have the database list all the train cars, and then be able to set which ones are on site? That way you can just update the status of each car to determine if it's on-site or not, so you don't have to be constantly creating entries for cars each time a new one arrives.

[Hooch]

I just came to this problem ThePsion5. I made a database and had the entry page made. Then I realized, how will I delete the car from the site lineup. So if I do it your way (ty very much) I can just edit the existing DB with a field for onsite or not. Then just display from the table where the value is 1 within "onsite".Thanks guys.

[ThePsion5]

Yes, using the schema I described you should be able to simply add a BIT field to your table to signify whether a train is onsite or not, and whenever someone wants to view the list of trains onsite you would simply use a select statement like this:

SELECT* FROM trains WHERE onsite=1

[pulpfiction]

Just a few other changes, Instead of having seperate fields, propably you can merge Original:5. Inbound -- 0 or 1 (INT)6 Outbound -- 0 or 1 (INT)7. Full -- 0 or 1 (INT)8. Empty -- 0 or 1 (INTTry having like this5. BoundStatus -- 0 or 1 (INT) [0 for outbound and 1 for Inbound]6. Full/EmptyStatus -- 0 or 1 (INT) [0 for Empty and 1 for Full]

[Hooch]

heheThanks pulp..I did that. It's comming along well. I have a php question regarding subtraction. I posted it in the php section. This is fun stuff. Almost done!!