transactional data db structure
I want to be able to view a history of the price and dimensions of a widget. One day it could cost $2 and then the next day it could cost $3; one day it could be 2 x 2, then next day it could be 3 x 3. Which of the two methods below would you use to store transactional type data?
WID date WTID (FK) value
1 2/2/08 2 2x2
2 2/4/08 2 3x3
3 3/1/08 1 4.25
4 3/8/08 1 8.50
PID value date WID (FK)
NID description date WID (FK)
It accurately tracks the change in widget dimension and change in widget price as time goes on. Method A doesn't do this, it only tracks part of a widget (for example: how do you define a widget in method a?)
Plus, you can't have more than one data type using A; prices and dimensions would have to be something like alphanumeric. I entirely advocate method B, but some people that I work with aren't crazy about the possibility of there being 50 tables for tracking 50 different qualities (e.g. price, dimension) of a widget. So instead of creating the separate tables for each quality, they suggest putting the qualities in a 'type' table, hence the need for only one table to track the history of modifications and additions. I simply disagree with this approach. What if you wanted to further describe the price as "USD", "EUR", etc.? I foresee all sorts of issues with A...
Originally Posted by chazzy
Thanks for the input, chaz.
Note: I'm not saying that B is the best solution, but out of A and B, I would pick B.
I think one of the questions you're trying to ask is about localization. My thought, and a lot of areas seem to agree, localization is best left at the database level. In java we use Resource bundles that have ISO language codes attached to them to load different Strings for display into the class.
The main reason I picked B over A is that you don't have any way of versioning the widget itself in either case, which makes A look even worse. Even so, making it date based isn't always the best solution. What happens if the price of widgets changes twice in one day? Or you get a bunch of mutant widgets that are 2x2 at noon, and 3x2 at 2pm and 5x1 at 4:30pm?
Obviously if there are going to be 50 different attributes of a widget, Method B won't work at all. It will be a management nightmare.
Thank you for the continued input, chaz.
Originally Posted by chazzy
a) I researched "database localization" and couldn't find information relative to the scenarios that I presented. It would be greatly appreciated if you could provide for me a link, or some more information on the topic.
b) We are concerned only with changes or additions made on a particular date.
c) Considering my aforementioned concerns about data type conflicts and the possibility of wanting to further describe a piece of data, I'm surprised that method A would even be considered as an option. Sure, I'd have to build those 50 tables and establish the relationships, and that obviously would take more initial time. Queries could include many joins. But if I want to designate a decimal data type for values to be stored in the "PRICES" table, and designate an alphanumeric data type for values to be stored in the "DIMENSIONS" table, then I can; method A would force me to store prices and dimensions as one data type. Furthermore, if I want to further describe prices as "USD" or "EUR", then method B would simply require adding a field to "PRICES" containing the primary key from a "PRICETYPES" table. Similarly, if I want to further describe dimensions as "inches" or "feet", then method B would simply require adding a field to "DIMENSIONS" that would store the primary key from a "DIMENSIONTYPES" table. I don't have this expandability using method A.
I hope I'm being somewhat clear as to what the boggle is. Thanks again for taking the time to help.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)