Re: database design method

From: Jan Hidders <>
Date: 18 Nov 2002 09:43:44 +0100
Message-ID: <3dd8a840$>

Lauri Pietarinen wrote:
>thanks for the trouble for trying to clarify.
>There must be some underlying assumptions that
>I juat don't understand, because I fail to follow your

Well, to be honest, it is a bit hard to explain it thoroughly in short usenet postings. But if you keep asking the right questions we will get there.

>The only question I know how to ask is the following:
>>>> Suppose I have a relation
>>>> Bought(customer, product)
>>>> and I want as the result a relation
>>>> Cust_class(class_id, customer)
>>>> that groups the customers into classes that have bought the same set of
>>>> prodcucts and gives them a new identifier.
>> > - I would suppose that the id's could be generated
>> >each time the query was executed - what's the problem?
>> If it is executed a lot you may run out of them.
>Surely if that same view (that generates - perhaps abstract -
>identifiers) is queried many times, it represents each time
>the values of the execution at that point of time. So if
>I query it twice it makes no difference whether the
>identifiers are the same or not, or what the result
>of the query is.
>So if I say
> select * from cust_class
> and get the answer {(1,1),(1,2),(2,3)}
>and re-execute it after 10 minutes
> and get the answer {(1,1),(2,2),(2,3)}
>that would be perfectly correct because
>it would just be the result of the query
>and that's it.

Yes, what could also happen is:

 first answer: {(1,1),(1,2),(2,3)}

 second answer: {(2,1),(2,2),(1,3)}

since a relation is a set and there is no fixed order in which the identifiers are generated. This is theoretically a bit annoying; nothing has changed in the database, you ask the same question, and yet you get a different answer. Another more practical problem is that there may be another view that (on the basis of this view) computes certain properties of these groups like Avg_income(class_id, avg_inc) that will have computed certain properties of these classes. If the identifiers change every time you query the view the connection between this tables is lost.

  • Jan Hidders
Received on Mon Nov 18 2002 - 09:43:44 CET

Original text of this message