Re: database design method

So let us reiterate what we have so far:

1. A table with customers customer(customer_id, customer_name,...)
2. A table with customer purchases bought(customer_id, product_id) (I suppose with both attributes in the key)

Now we want a view
  cust_class(class_id, customer_id) (supposedly with both attributes in the key)
in which all customers that have bought exactly the same products are assigned the same class_id.

Supposedly we want to further massage this data so the class_id helps us identify which customers belong to the same class.

The choice of what class_id to assign is completely arbitrary. Let's say that in the first invocation we get   cust_class = { (1,1),(1,2),(2,3) }

Clearly we can't "run out of integers" because there can't be more classes than there are customers.

Now two things can happen:

1. the database changes so that some customer(s) buy new products (say cust 1 and cust 3)
2. no customers buy new products

Let's look at 1):

now cust_class = {(1,1),(2,2),(1,3) } (e.g.)

Obviously class '1' now has a different meaning because cust 1 and 3 have bought new products so it (class 1) has nothing to do with the previous "class 1".

If they were abstract identifiers - well - even if we could not see them they would be the same or different or...?

Let's look at 2):

now since the database system is free to choose what ever identifier it wants for the classes (since the choice is arbitrary)
the result of the query could be  

   (a) cust_class = {(1,1),(2,2),(1,3)}

or (e.g.)

   (b) cust_class = {(2,1),(1,2),(2,3)}

If the class id's are the same (a) then obviously we can compare them with the earlier
invocation.

But what if they turn out to be different (b)? Then we can't combine the result with
any previous result.

And now let's just pretend those class_id's are abstract and unvisible (you can print this posting and take a pencil and black them out). My understanding is that we still have the same problem?

I must be missing something here but what is it that I'm missing?

>I'm a bit time pressed at the moment, so if you don't mind I will try to
>explain it a bit more fully another time.
>
>
OK, no problem!

regards,
Lauri Pietarinen Received on Tue Nov 19 2002 - 14:31:34 CET