Roughly translated. (very Rougly ?).
"
A table PK fields MAID and GMID.
Tuples are deleted but not removed from the table.
There is a field GELOESCHT (REMOVED).
A 0 (I think a NULL) in this means not removed a datetime
gives the time when this tuple is removed.
Using this then the PK doesn't work anymore because including
these removed fields there can be more than one MAID and GMID
combination.
Adding the GELOESCHT field to the PK doesn't work because
this gives trouble with a ???floatfield???(=Fließkommafeld) and
is a 0 equal to a 0 ?? (I think a NULL equal to a NULL).
Does anybody have an Idee ?
"
The anwser I have given is more extended than the request.
I did elaborate with datamodel and databasemodel fase, to
get above table what the problem was and included a table
which has referential constriants with the table.
This because to 'solve' the problem one has to know exactly
wat it is to be used for. (Hense the datamodel).
And how this can be solved. (Hence the databasemodel).
ben brugman.
"Alkos" <azerty@nospam.org> wrote in message
news:bnto9p$m861@news.rd.francetelecom.fr...[color=blue]
> What is the question, please ?
> (I also hope you are trying to design your DB with Oracle ;)
>
> Alkos
>
> "ben brugman" <ben@niethier.nl> a écrit dans le message news:
> 3fa256a3$0$279$4d4ebb8e@read.news.nl.uu.net...[color=green]
> > Hello all stakeholders,
> >
> > My German is worse than my English so I am going with English.
> > (This is a long anwser, I do appreciate respons and if there are
> > questions please ask. I have a view of what a Datamodel (logical)
> > and a implementation of a Datamodel=Databasemodel (fairly fysical)
> > is. This view I have may be unfamilliar to you or you might not
> > agree with this view).
> >
> > I like to split the problem into two different 'fases', that of
> > datamodel and that of implementation into a database (databasemodel).
> >
> > DATAMODEL
> > Requirements are a table were MAID and GMID are the 'logical' PK.
> > But a tuple can be deleted, were we want to keep the data, but need
> > an indication that the tuple was deleted and when the tuple was deleted.
> >
> > Suppose that there is a 'child' table which has a relation with the[/color][/color]
table[color=blue][color=green]
> > over the MAID and GMID PK. (Two different situations can exist the
> > child table can not contain children of deleted 'parents', or the child
> > table can contain children of deleted 'parents'.)
> > I do not know if your situation includes such a table.
> >
> > The deleted tuples can be seen as historical tuples, to be kept.
> > This can be modeled as a sepparate table.
> > This can also be modeled with extra information within the tuple.
> > (A deleted indication or a deleted date or both).
> >
> > So the modelling of this table can be one table or an actual table
> > and a historical table.
> > (THIS IS STILL ONLY THE MODEL AND NOT THE IMPLEMENTATION).
> > Depending on the way the information is viewed on or the other can
> > prevail.
> >
> > NOW TOWARD THE IMPLEMENTATION. (dataBASEmodel).
> > This should implement all the rules given in the datamodel fase, but
> > how to implement this is still a 'free' choice.
> > Here we can implement the model as it is, but independed of the model
> > one can choose for the one table or two table implementations.
> > (If we still have the choice here, why do we need a model ? The model
> > describes what we need these needs have to be implemented, the model
> > should help to make the choices not make the decisions).
> >
> > If implemented as TWO tables, selection on actual information is easy,
> > selection on historical information is ease. Selection on both can
> > be done with a union.
> > With two tables the relation between the children and the parent.
> > Easiest to enforce deleted parents can not have children.
> > Second option deleted parents can only have deleted children also
> > in a historical children table.
> > Nasty implementation both the actual parents and deleted parents
> > can have children in the one children table. This is not a nice
> > implementation.
> >
> > If implemented in ONE table. Extra columns are needed and the PK has te
> > be extended with some historical info. First we should avoid NULLs[/color][/color]
because[color=blue][color=green]
> > they are not nice in PK's and not nice in relationships. (This is an
> > understatement).
> > So if you have a deleted indication use at least two values one for not
> > deleted one
> > for deleted.
> > Datetime fields or stamps, personaly I think that time is continuous and
> > therefore
> > not suetable as a PK or other identification, but if the datetime is[/color]
> rounded[color=green]
> > to say
> > days, seconds or milliseconds it can be made suetable for[/color][/color]
identification.[color=blue][color=green]
> > But then a a choice MUST be made how it is rounded example days or[/color]
> seconds.[color=green]
> > If there is only one deleted tuple for each MAID and GMID than use the
> > identification. If there are more I would go for a version[/color][/color]
(automatically[color=blue]
> or[color=green]
> > not) and one could define the highest as actual, but maybe it would be
> > better to define zero (Not null) as actual and keep the higher numbers
> > as historical. A fixed number zero (or any other number) for the actual
> > tuple makes selection easier.
> >
> > Children.
> > If deleted tuples can have children the deleted indication or version[/color]
> should[color=green]
> > be incorperated in the relational key. This makes updating difficult.
> > Because
> > both can not be updated in one go or defered checking has to be used.
> > (Defered checking has limitations and should if possible not be used).
> > (A write and delete cyclus is possible, first make a historical parent,
> > then move the children then delete the first parent).
> >
> > If children are not allowed on deleted tuples they should be deleted[/color]
> before[color=green]
> > a parent can be deleted (with the deleted signaling).
> > Problem the if only the MAID and GMID are checked, the database can not
> > enforce this rule. So applications should enforce this rule.
> > (Problem with applications enforcing rules that checking for children[/color][/color]
and[color=blue][color=green]
> > then
> > deleting the parent, another process can in the mainwhile add a new[/color][/color]
child.[color=blue][color=green]
> > Oracle does not do any predicate locking so this insert can not be[/color]
> blocked.)[color=green]
> > One could include the actual indication in the relation. Then at least
> > during
> > a concurrency conflict at least one transaction will fail.)
> >
> > If there are specific requirements that the solution should be ONE or[/color][/color]
TWO[color=blue][color=green]
> > tables,
> > one can also revert to VIEWS to make this available. And remember ONE or[/color]
> TWO[color=green]
> > table implementation can be INDEPENDEND of ONE or TWO table models.
> > Depending on the functional requirements the model should have one or[/color][/color]
two[color=blue][color=green]
> > tables.
> > Depending on the model and nonfunctional requirements this should be
> > implemented
> > as one or two tables. (Which queries are done how offten, how many[/color][/color]
tuples[color=blue][color=green]
> > (actual
> > and deleted) and how fast should the queries and the updates be).
> >
> > General filosofie :
> > First there should be a Datamodel to describe what we want.
> > Then a DataBaseModel should be made from this and be implemented.
> > (At this point some decisions have to be made which rules will be[/color][/color]
enforced[color=blue][color=green]
> > by the RDBMS and which rules have to be enforced by business logic.)
> > After this the business logic can be build.
> >
> > If there are questions of which things are allowed (combinations of[/color][/color]
data)[color=blue][color=green]
> > one should look at the databasemodel. (The database model and functional
> > model (or design) should be able to anwser all the questions of what is
> > functionaly allowed).
> > If there are questions of how to interface with the RDBMS one should
> > look at the Databasemodel.
> > The RDBMS and business logic togethere should implement all the
> > constraints 'dictated' by the databasemodel.
> >
> > If you are still there, thanks for your attention,
> >
> >
> > ben brugman.
> >
> >
> >
> > "SWE@kp" <swe@klages-partner.de> wrote in message
> > news:2f5e5c00.0310012223.6056fbe2@posting.google.c om...[color=darkred]
> > > Knifflige Frage zu Datenbanken:
> > >
> > > Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und[/color][/color]
> GMID.[color=green][color=darkred]
> > > Soweit ok.
> > >
> > > Nehmen wir an, aus dieser Tabelle sollen keine Datensätze physikalisch
> > > gelöscht werden, sondern der Löschstatus durch ein Datenfeld GELOESCHT
> > > angegeben werden. Hat das Feld den Wert 0, dann ist der Datensatz[/color][/color][/color]
nicht[color=blue][color=green][color=darkred]
> > > gelöscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Löschung[/color]
> > drin.[color=darkred]
> > > Dann funktioniert der PK nicht mehr, denn es können ja durchaus[/color][/color][/color]
mehrere[color=blue][color=green][color=darkred]
> > > Datensätze mit gleicher MAID und GMID vorhanden sein. Aufnehmen von
> > > GELOESCHT in den PK geht theoretisch, ist aber gefährlich (da
> > > Fließkommafeld, und wer weiß schon, ob 0 immer gleich 0 ist...).
> > >
> > > Hat jemand eine Idee?
> > >
> > > SWE@KP[/color]
> >
> >[/color]
>
>[/color]
