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?
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)
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.
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?