Re: Order details table reference live data

From: vldm10 <>
Date: 26 Sep 2006 11:23:18 -0700
Message-ID: <>

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 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:

Vladimir Odrljin Received on Tue Sep 26 2006 - 20:23:18 CEST

Original text of this message