Re: Updatable views

On Mon, 12 Dec 2005 11:24:25 +0200, "x" <x_at_not-exists.org> wrote:

Dear x,

I am just a beginner in database theory. I consider myself not much of a beginner in objects or responsibilities. The problem seems real simple, from your answers I gather for most database experts this problem is not simple.

Thanks for your answers,

Rick

>
>"Rick Elbers" <rick.elbers_at_chello.nl> wrote in message
>news:qbiep11f3eg8h1mmkqhqskc77ai1r7boq7_at_4ax.com...
>> Probably in vain but I will give you answers.
>>
>> On Wed, 7 Dec 2005 11:08:57 +0200, "x" <x_at_not-exists.org> wrote:
>>
>> >
>> >"Rick Elbers" <rick.elbers_at_chello.nl> wrote in message
>> >news:3189p1dlv9qpofqai5nq8v32keao7amiph_at_4ax.com...
>> >> Dear database experts,
>> >
>> >> Can somebody enumerate the problems with an updatable view for me ?
>> >> With an updatebleview:= joined set of related tables and relations
>> >> from which rows can be updated, inserted and deleted like you
>> >> can in a simple table or master-detail relation.
>> >
>> >What is an updatable view in your view ?
>
>> Defined that above. See above
>
>If it was defined, I wouldn't have asked.
>
>> >What do you mean by joined set of related tables and relations ?
>>
>> A list of tables which are joined( flattened if you want) on their
>> related identifiers( keys if you want). In fact your regular join
>>
>> >Maybe you mean just a set of tables ?
>
>> Yes, I think so
>If you are not sure, who is then ?
>It is your definition. You should be sure about it before you do anything
>else.
>
>> >What do you mean by related ?
>
>> With a foreign key-primary key relation
>Ok.
>
>> >There is any constraint definition ?
>
>> Could be. Couldnt be. Should both be possible
>There are other kind of constraints besides the referential FK-PK ones ?
>
>> >Who is enforcing the constraints ? The DBMS or the application ?
>> the DBMS.
>
>Ok. Then the DBMS is the only one who knows if the view is updatable or not.
>
>> >Who is doing the translation of the update requests ? The application or
>the
>> >DBMS ?
>
>> Thats the point. As much as possible the class UpdatableView.
>How could this class know if the update is possible if the constraints are
>complex and are handled by the DBMS ?
>
>> >How you deal with the errors ?
>>
>> That too is responsibility of UpdatableView
>
>Ok.
>
>> >Are we talking about SQL DBMS or something else ?
>>
>> Yes we are talking about sql server to be precise( although I doubt it
>> matters)
>>
>> >etc. etc. etc.
>>
>> Etc etc trivial answers
>>
>> >
>> >> My first try is to find hierarchy in de database by finding the
>> >> absolute parents assigning them level 0. Parents of my table are
>> >> considered those relations in which my foreign key doesnt allow
>> >> DBNull.
>> >
>> >What if do not find any parent this way ?
>
>> Then you are safe to insert/update anything in that table
>Really ?
>
>> >And what is the purpose of building the hierarchy ?
>>
>> See above. Do you really understand the problem or not ?
>
>I don't understand it because even you don't understand it.
>
>> >> Then from there on proceed to give every table that has no parents but
>> >> level 0 parents a level 1 etc.
>> >>
>> >> Problems seem to be autojoins and multiple relations between tables.
>> >> Those problem can be solved to use every table only once in the scan.
>> >>
>> >> So the first question is: will this way of hierarchising the
>> >> relational db always valid ?
>> >
>> >> Then secondly given the hierarchy what problems remain when we talk
>> >> about insert/update/delete and all kinds of combinations of those ?
>> >
>> >Gee! Let me think. The problems are ... what is the problem again ?
>> >
>>
>> Funny guy. The problem is the database. How to get rid of its messing
>> with good OO code.
>
>I think you meant "the problem is the DBMS" instead of "the problem is the
>database" :-)
>Well, the problem could be in many places...
>
>Define what is a "view" for your application.

Every join you can make. More simple even: every information set.

>Define what is an atomic update for your "view".
Every update you can think off

>Define the types of constraints you want to handle.
All

>Then see if you can find a plan for performing the updates.
>
>
Received on Mon Dec 12 2005 - 23:05:28 CET