Re: Order details table reference live data

vldm10 wrote:
> Maybe the following solution can be interesting for your DB. I will try
> to explain it on the example of the relation Products (K, A1, A2, A3);
> where K is a simple key and Ai are attributes.
> Instead of Products(K,A1,A2,A3) I will form the binary relations R1(K,
> A1), R2(K,A2) R3(K,A3). One of these relations should be E-Relation.
> (See my web site www.dbdesign10.com for E-relation). Now I will
> concentrate only on R2. Let A2 be a price of a product. I will define
> only 2 operations with R2-data entry:
> 1) Entering a new price in DB
> 2) Proclaiming that a price is not current from some point in the time.
> To do this I will add to R2 the following columns: D1,D2,D3,D4 and
> user_password. ( I am calling these columns "knowledge columns" -
> for the definitions see my web site).
> So I have now R2(K,A2,D1,D2,D3,D4, user_password), where D3 and D4 are
> "date from" and "date to" for the product's price in the real
> world.
> D1 and D2 are "date from" and "date to" for the product's
> price in the database.
> user_password is the password of the person who entered these data.
> Example for R2:
>
> 1) Entering a new price:
> K A2 D1 D2 D3 D4 user_password
> -----------------------------------------------------------
> 66 $50 4oct05 999 1oct05 999 John
>
> Here $50 is new price starting from 1oct05 and it was entered in the DB
> on 4oct05.
> "999" means that price is current. This data entry screen has only
> two "fields": for price and for D3. All other data are entered
> automatically.
>
> 2) Proclaiming that a price is not valid:
> K A2 D1 D2 D3 D4 user_password
> ---------------------------------------------------------
> 66 $50 4oct05 2dec05 1oct05 999 John
> 67 $50 2dec05 999 1oct05 1dec05 Mary
>
> Here first row is updated with "today date" in D2. After this
> update, the new row is formed. The new row says that price of $50 is
> not valid after 1dec05. This data entry screen has only one "field"
> (for D4)
>
> 3) Again entering a new price for the same product:
> K A2 D1 D2 D3 D4 user_password
> ---------------------------------------------------------
> 66 $50 4oct05 2dec05 1oct05 999 John
> 67 $50 2dec05 999 1oct05 1dec05 Mary
> 68 $60 5dec05 999 2dec05 999 Ann
>
> What we can get with this solution?
> We can build the web data entry screens, which enable us to enter data
> over internet. It is "on line" update, which results are
> immediately available. As you mentioned - they are live data.
> We have all important events "on line" available. User can't
> falsify data even if he wants - we can catch it. The database is
> capable to recognize who (or which procedure) created every its data.
> We can add to R2 (if we need) other "knowledge columns" - the
> station number, the time, maybe the multimedia columns, etc. We can
> extract above relation R2 as a file and use it for some programming
> language or for example use it for data interchange among disparate
> applications.
> (In my ex country at a company where I was working, similar solution
> was successfully developed by one project manager. However it was on
> "record" level (not on attribute level) with the composite keys and
> with specific conceptual model for Unisys's computers.)
>
> Now we can apply completely new and different sets of "knowledge
> columns" to R1 and R3, depending on our needs and purposes. In fact,
> this data model supports the different solutions for the different
> attributes of an entity. For example, if R1 is E-relation then we can
> apply one set of "knowledge columns" for R1, for R2 we can use for
> example above solution (i.e. another set of "knowledge columns")
> and for R3 it can be for example 0 "knowledge columns". If you are
> interested in this kind of solutions, you can find the detail
> explanation on my web site: www.dbdesign10.com
>
> Vladimir Odrljin

Does every response you give have to be an advert for your own agenda?

Anyhow, back at the ranch, you highlight interesting (but I would guess quite common) problem Johann, with the design you are working with not persisting an item in a specific state.

But it is just a design issue. You have to highlight at design time, whether an item needs to be distinguishable at a /certain point/ in its life. If so then the different states of the item are wholly different things as far as the database is concerned, and should be recorded as such.

Hence, the identifer used in recording propositions about that item should /not/ just be a {product_id} as it is in your current design, but rather a compound identifier such as {product_id, timestamp} that is able to distinguish it (and so can also serve as a foreign key in the orders table).

For a better discussion than I can offer, I would recommend having a look at:
"Temporal Data & the Relational Model" (Morgan Kaufmann Series in Data Management Systems). Received on Wed Sep 27 2006 - 15:48:24 CEST