Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Question Trying the impossible :)

    Hey guys, total noob here

    I have an ambitious project that I have been planning for quite some time. I am currently using an Excel table to manage data related to my mother-in-law's trucking business. Over time (1.5 years), the table has grown into a monster, and I am looking to convert this over to a database software with a front-end. I am somewhat skilled in VBA (the spreadsheet uses extensive VBA / 50 pages of code), and in Delphi. As I am in the design process, I have not chosen a front-end for the database, but I am leaning towards Delphi or Access (based on my experience). So here is what the Excel sheet is doing.

    1. It holds details about each load
    • Date
    • Load #
    • Broker
    • From Zip
    • To Zip
    • Distance
    • Weight
    • Rate

    2. Broker is pulled from a different table via Vlookup/drop down list in the excel. The separate list holds the following broker information.
    • BrokerID
    • Name
    • Address1
    • Address2
    • Address3
    • Factored (Yes/No)


    Likewise, the distance is processed from a excel sheet (generated externally) that lists the miles of the trip per state. Thus, the distance is divided by states, and I don't know how to incorporate that.

    These two pieces of information are directly related in a one-one relationship.

    Here is the tricky part; the overlaying structure of organization is a TRIP. A trip consist of several items
    1. It can have one or several loads in it
    2. It has several gas receipts
    3. It has several toll receipts
    4. It has some miscallaneous receipts


    On top of that, each gas receipt has the following items
    • Date
    • Amount
    • Gallons
    • State


    The other receipts have the attributes
    • Date
    • Description
    • Amount


    There are several other options that are included in the Excel sheet, such as zip--> city via vlookup, automated invoice generation, receipt management, etc.

    However, I am trying to get the relations first (Please note that I am using this opportunity to learn database design and I am reading a textbook about database design to help me).

    So far, I have figured out that I need somehow a connector table that will tie in all the information by Trip#.

    How I eventually envision the final product to be is that

    1. I start/open a new trip
    2. Enter each load of the trip with its according information
    3. Enter all the receipts of the trip
    4. Close the trip


    Lastly, I will have to implement queries that include:
    • Miles driven per state per quarter
    • Gallons fuel bought per state per quarter
    • Trip report with Invoices/Expenses/Profit elements, etc.


    So far, I am afraid that the project is over my head I figured out to separate all the information in separate tables and narrowed it down to Trip as central HUB. But from there, I seem to struggle.

    Any pointers would be greatly appreciated.

    Thanks

    Alex

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    All in all, you have a pretty good start.

    The Trip is the central entity (thing of interest) in the database, but it actually feeds from a network built from the other entities that you've described.

    If you can find a copy of Tom Rettig's Database Normalization Poster, that will probably help you clarify your thinking a lot. It is by far the clearest way that I have found for most English speaking people to envision the process of breaking down and "cleaning up" the entities in a data model. This data model and the associated database schema is where you ought to put the most time because it is very much like the foundation for a building... If you get it right, you can't go too far wrong building upon it. If you get it wrong, you might be able to create something you can tolerate for a while, but it will neither last nor function under heavy load.

    After you get the schema done, start to build a rough application around it as a simple test... Don't worry about putting a ton of data into it at first, maybe five of each "thing" that you want to track. Once you get those "things" entered into the system, try to build your reports and see if everything works the way you want it to when you're done... It won't but that's part of the learning experience.

    Because you've done a pretty good job up front (outstanding for a first attempt), I don't think that this will be too hard and it ought to be a lot of fun. Feel free to stop back often and ask questions... While we probably won't actually write it for you, we are usually pretty helpful and there is a pot load of experience here on DBForums so you can learn from our past mistakes instead of having to make them all by yourself!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Posts
    3

    Thanks

    Quote Originally Posted by Pat Phelan View Post
    All in all, you have a pretty good start

    ...

    ...

    -PatP

    Thanks Pat. I tried googling for the mentioned diagram with no luck. Any idea where I may find it? (Or in which book?)


    Thanks

    Alex

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Oops, my bad. Try Marc instead of Tom.

    Tom Rettig was a database guru in the days of the Clipper compiler for dBase... That was a while ago (I'm REALLY dating myself by knowing that).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2013
    Posts
    3

    Excellent

    That did the trick. I actually already looked at that poster, but not in detail since it was not Tom Rettig Anyways, I have made a design decision that may work...

    Instead of trying to have one central trip table that contains all elements, I should follow advise #4 and create separate Trip-Load, Trip-Gas, Trip-Repair, and Trip-Toll tables. Although it is not likely to reduce the number of lines entered, it will certainly speed up any queries related to individual categories (Loads, Gas, Receipts).

    Time to let this idea marinate and work on the implementation

    I will check back with progress reports...

    Thanks for the help thus far

    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •