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@kp" <sw*@klages-partner.de> wrote in message
news:2f**************************@posting.google.c om...
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@KP
