Re: Order details table reference live data

JOG wrote:
> 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?

 No, but I beleive that Johan post is important problem for todays DB. I would like that sombody else present solution for "Products" with key and few attributes. This solution should suport on line data entry (including updates), as Johan mantioned these are live data.

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

>

I am not sure that we should put the propositions on first place as you force it?
(I have travel today and will be back in 20 days, so then we can continue in more details)

should /not/ just be a {product_id} as it is in your current design,

K in my example is not product_id; product_id is one atttribute, for example A1.

> but rather a compound identifier such as {product_id, timestamp} that

try to present timestamp in conceptual model for this entity?

> is able to distinguish it (and so can also serve as a foreign key in
> the orders table).

>