locked
Best way to design a table with start and end data RRS feed

  • Question

  • I am designing a database that stores trip data for mileage tracking purposes. The database contains a Trips table that stores date/time, location and odometer information for both the starting and the ending locations. I want to be able to store a partial trip in the database without compromising integrity.

    For example, a user is preparing to depart on a business trip. The user logs in to the web app using his or her mobile device, enters location and odometer information, and starts a new trip. This partial trip is stored on the database, so that once the user arrives, he or she can log in to a different mobile device and enter the destination location and odometer information so that the trip can be completed.

    The Trips table currently stores data for both the starting and ending locations. All columns are non-nullable. Obviously, this does not allow storing null values for the end location data. If I allow null values in the end columns, then my web app frontend has to check the entire table for null end values at startup so it can complete an unfinished trip. This undoubtedly has performance implications when dealing with many users and trips. Also, allowing nulls for all trips compromises data integrity in the Trips table, because only one trip at a time should be unfinished. I don't really want to allow incompleted trips, except for the special case listed above.

    Should I separate the start and end locations into separate tables? Maybe keep track somehow of which trip id is incomplete? If so, how? Is there a better solution? I am very inexperienced with database design, so I would appreciate some help with how to implement a design for the above requirements.

    Thursday, June 2, 2011 8:07 PM

Answers

  • >If I allow null values in the end columns, then my web app frontend has to check the entire table for null end values at startup so it can complete an unfinished trip.

    Only for that user. Assuming it is INDEXed by user, that should be a quick lookup.

    Friday, June 3, 2011 2:57 AM
    Answerer

All replies

  • you should rename the table with complete trip as tour and all trips(partial) included in that tour will be as a seperate table (child talbe) to the tour.

    Tour Table(master)

    Trip Table (child)

     


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)
    Thursday, June 2, 2011 10:30 PM
  • you should rename the table with complete trip as tour and all trips(partial) included in that tour will be as a seperate table (child talbe) to the tour.

    Tour Table(master)

    Trip Table (child)

     


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)


    Each trip stands on it's own and is not part of a tour. The data for a Trip is a cohesive whole, but the data is collected at different times. How do I store the start data on the server, and then later the user can complete the trip and add the end data?

    I hope that makes sense. Each trip has only two parts, start and end location, odometer, time, etc., but recording that data is a two-step process, with each step stored on the server at different times.

    Thursday, June 2, 2011 10:41 PM
  • >If I allow null values in the end columns, then my web app frontend has to check the entire table for null end values at startup so it can complete an unfinished trip.

    Only for that user. Assuming it is INDEXed by user, that should be a quick lookup.

    Friday, June 3, 2011 2:57 AM
    Answerer
  • You have a table whose contents are "completed trips". Instead of trying to use this for "trips in progress" as well, keep that table to that one subject and make a second table for "trips in progress".

    This second table records the trip starting information. When you get the trip completion information, pull the starting information, construct and insert the trip record in "completed trips", then delete the "trip in progress" record.

    This gives you a short table showing trips in progress so you can do housekeeping on trips that are not closed.

        ++PLS

    Monday, June 6, 2011 4:44 PM