Re: zusammengesetzter Primary Key mit Deleted-Flag

What is the question, please ?
(I also hope you are trying to design your DB with Oracle ;)

Alkos

"ben brugman" <ben_at_niethier.nl> a écrit dans le message news: 3fa256a3$0$279$4d4ebb8e_at_read.news.nl.uu.net...
> 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 table
> 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 because
> 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
rounded
> to say
> days, seconds or milliseconds it can be made suetable for identification.
> But then a a choice MUST be made how it is rounded example days or
seconds.
> 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 (automatically
or
> 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
should
> 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
before
> 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 and
> then
> deleting the parent, another process can in the mainwhile add a new child.
> Oracle does not do any predicate locking so this insert can not be
blocked.)
> 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 TWO
> tables,
> one can also revert to VIEWS to make this available. And remember ONE or
TWO
> table implementation can be INDEPENDEND of ONE or TWO table models.
> Depending on the functional requirements the model should have one or two
> 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 tuples
> (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 enforced
> 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 data)
> 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_at_kp" <swe_at_klages-partner.de> wrote in message
> news:2f5e5c00.0310012223.6056fbe2_at_posting.google.com...
> > Knifflige Frage zu Datenbanken:
> >
> > Es gibt Tabellen, die mehr als ein PK-Feld haben; also z.B.MAID und
GMID.
> > 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 nicht
> > gelöscht; ansonsten steht der Zeitpunkt (Tag und Uhrzeit) der Löschung
> drin.
> > Dann funktioniert der PK nicht mehr, denn es können ja durchaus mehrere
> > 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_at_KP
>
>
Received on Fri Oct 31 2003 - 07:32:40 CST