473,890 Members | 1,359 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

zusammengesetzt er Primary Key mit Deleted-Flag

SWE
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
Jul 20 '05
11 4622
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ßkommafe ld) 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" <az****@nospam. org> wrote in message
news:bn******** *@news.rd.franc etelecom.fr...
What is the question, please ?
(I also hope you are trying to design your DB with Oracle ;)

Alkos

"ben brugman" <be*@niethier.n l> a écrit dans le message news:
3f************* ********@read.n ews.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=Datab asemodel (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.goo gle.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@KP



Jul 20 '05 #11
(I also hope you are trying to design your DB with Oracle ;) No.

Alkos


With Oracle Designer you can design DB's.

With Oracle you can implement DB's. (Databasemodel) .

But the designing process can be done with a great number
of tools (pen and paper for one). Also designing the
databasemodel can be done with a great number of tools
(pen and paper for one).

The designing process should be at least for a great deal
be independend of the database where you implement
the database. Because of RDBMS differences and
performance one can make different implementations
on different RDBMSsen. So although an RDBMS (Oracle)
for example can be in the back op your mind during the
design fase, it should not have to much influence.

When designing try to make the 'design' as generic
as possible. A design which can be implemented in
multiple different RDBMSsen is 'stronger' than a
dedicated design.
(The world is not completely ideal, so some parts of
the design are dependend on the database you are
actually going to use. There are differences in
concurrency model, in grouping of records (think iot and clustering),
in sequencing, some of these should be taken into the
designing process at an early stage.
So when possible try to be independend of an RDBMS but when
neccesary take the RDBMS into account)

ben brugman
Jul 20 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1481
by: TinTin | last post by:
Dear All, I got a wierd problem which I haven't been able to explain. I am working on MS SQL 2000. I don't know for what reason, the relationship between Parent/Child table is getting deleted. When I open up the ER diagram in MSSQL Enterprise Manager, I see the relationship line come up for just a split of a second, and disappears afterwards. This is the second time I am seeing this thing.
8
8102
by: Klemens | last post by:
The linked table has a bigint in primary key columns. I've read that service pack 8 on Jet should solve this but it didn't. On patch1 options I only found to map timestamp to char(26) entry for similar problems on timestamp fields but nothing for Bigint. DB2 is UDB8.1 Fix 6 on Win2000 Are there any other solutions? Thanks Klemens
10
2821
by: DaveDiego | last post by:
I've had a user delete one of the client records, I do have a version of the DB with all records intact before the deletion occured. Whats the best approach to getting all the related records in each of the tables? I have about 12 tables to put data back into and multiple records for each. Would I need to make an append or update query for each table?
5
29024
by: Grant | last post by:
Hi Is there a way to recover deleted records from a table. A mass deletion has occurred and Access has been closed since it happened Louis
4
5173
by: yf | last post by:
A KB article "http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells that the maximum number of records that a table may hold if the PRIMARY key data type is set to AUTONUMBER is 4,294,967,295. Suppose the PRIMARY key data type is set to "RANDOM" AutoNumber. Suppose an application (a) successfully INSERTS "X" records, then (b) successfully DELETES "Y" records (X >= Y), then
3
1555
by: Prakash | last post by:
Below is my code to delete a record in a continuous form. I can't figure out any reason but sometimes (another) record gets deleted instead of where the record pointer is positioned. Small table - just 750 records ... no primary key defined. Using Access 2003 under WinXP SP1. There must be something I've overlooked. Maybe one of you gurus could help.
7
11839
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For tables that involve a number field as the primary key, the data is returned successfully. For tables that involve a character field (e.g. CHAR(3) or VARCHAR(10)) as the primary key, I have the correct number of rows returned, but the data displayed...
1
7878
by: iam247 | last post by:
Hi I am a relative beginner with SQL and ASP. With some help after previous posts I have a page which successfully requests querystrings from another page and deletes a record from an access table, However, I get the error message: "Row handle referred to a deleted row or a row marked for deletion" I am using access 2002, the table only has 2 fields (ContactID and
1
1135
by: scott | last post by:
Hi all, got a bit of a problem with a datagrid. I have a datagrid that gets it information from a data table which in turn is connected to a data set. The data set is linked to an xml file and an xml schema for the rules.
2
12585
by: senthiltsj | last post by:
hi two days before arount 20,000 rows are manuly deleted, is there any way to recover it back. We have backup, that is 10 days old, Is there any way to recover only that 20,000 rows, Plz help me out ASAP. Regards Senthil Kumar.T
0
9826
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10925
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9641
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5855
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6061
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4682
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3283
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.