Re: database design method

Lauri Pietarinen wrote:
>Jan,
>
>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
>argumentation.

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