Re: Order details table reference live data

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

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 Received on Tue Sep 26 2006 - 20:23:18 CEST