Re: zusammengesetzter Primary Key mit Deleted-Flag

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 - 06:33:39 CST