Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010

    Unanswered: Preliminary DB Help

    Hi everyone,

    I'm working on my first Database and just had a few preliminary questions about how some people handle various fields.

    What I am trying to do is create a DB that tracks the lifecycle of an order. In other words, various people can login assign an order to the appropriate party and view where the item is. So far i've come up with Vendor, Employee, and Asset tables.

    With the Vendor, there are two fields that I am not sure how to handle: Province/State, and country. Should I be creating a seperate Country, and Province/State table and link them through a relationship? Or within my App is simply using a drop down list with all permitted values ok?

    The second question is how to handle to relationships of keeping a history of all transactions, and being able to display where the current asset is located? Would I create a new table named AssetLocation and use the primary keys for all 3 tables as my values? I think this would cause quite a bit of duplicate entries but would still allow for a history to be kept and displayed. In order to be able to display the most recent entry, would creating an isActive flag be the best bet for each Asset?

    Hope my questions make sense.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    separate tables for country and province/state would be gross overkill

    as for history, you did not describe what it is that you're keeping a history of, so i can answer only in generalities

    if you weren't keeping history, your table would have a certain PK

    with history, the PK expands to include a date column, such that the "active" row is either (1) the row with the latest date, which represents an "effective from" date, or (2) the row with a NULL date which represents an "effective to" date

    obviously method 2 requires a surrogate PK, since no part of the PK may be NULL

    you won't need both effective from and effective to, and the isActive bit is redundant (although some prefer it for its simplicity) | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Help for Vendor:

    What I've done in the past is kept the list for each in their own table, use a non-limited combo box list built from these tables, and then store the entry in the table with the order. After the order was "placed" then I would run a query on the orders table sorting, removing duplicates and blanks, and re-create the list tables. This way you have the ability to have the user enter a new place without going into the tables, and the combo box will automatically try to match the text you type so you can minimize misspelled words.

  4. #4
    Join Date
    Apr 2010
    Thanks to you both. Gives me quite a bit to think about. I agree with separate provinces/countries is overkill. Another approach I was thinking is create a Log table that logs each time an Update is performed on the Orders table. The Orders table would then contain the data for the most active order, and the log would contain the total data for historical purposes. I realize this might create a bit of redundancy, but is it a suitable approach for a small scale application setting?

Posting Permissions

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