[Solved] Unique person

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

[Solved] Unique person

Post by dreamquartz »

Hi Everyone,

I am struggling to make sure all persons entered in a database are really unique.
The reason for this that records are to be linked to the right person in the database.
Having an 'other' entry will therefore not link data to the correct person.
It is required that all historical data, potentially going back over decades, is linked to the right and therefore same person.

I use a trigger 'before insert'

Code: Select all

CREATE TRIGGER PUBLIC.PERSON_BI BEFORE INSERT ON PUBLIC."tPerson"
	 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
	 BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 "tPerson"."Surname",
					 "tPerson"."GivenName",
					 "tPerson"."Nickname",
					 "tPerson"."MiddleInitial(s)",
					 "tPerson"."DateOfBirth"
				 FROM
					 PUBLIC."tPerson"
				 WHERE
					 (
						 "tPerson"."Surname" = NEWROW."Surname" AND
						 (
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth" OR
							 "tPerson"."GivenName" = NEWROW."GivenName" OR
							 "tPerson"."GivenName" = NEWROW."Nickname" OR
							 "tPerson"."Nickname" = NEWROW."GivenName" OR
							 "tPerson"."Nickname" = NEWROW."Nickname" OR
							 "tPerson"."MiddleInitial(s)" = NEWROW."MiddleInitial(s)"
						 )
					 )
			 )
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = 'There appears to be a similar Person already recorded. Please verify the Existing entry!';
		 END IF;
END
checking on existing entries.

There are potential, historical, errors in the database, due to missing information, or typos.
Missing information could be a NickName.

I have added a Boolean called 'Check' column added to the record, so that I can now update/modify an existing person, as a result of new personal information coming to light, or as a result of that typo.
The trigger 'before update'

Code: Select all

CREATE TRIGGER PUBLIC.PERSON_BU BEFORE UPDATE ON PUBLIC."tPerson"
	 REFERENCING NEW ROW AS NEWROW FOR EACH
	 ROW BEGIN ATOMIC
		 IF EXISTS
			 (
				 SELECT
					 "tPerson"."Surname",
					 "tPerson"."GivenName",
					 "tPerson"."Nickname",
					 "tPerson"."MiddleInitial(s)",
					 "tPerson"."DateOfBirth"
				 FROM
					 PUBLIC."tPerson"
				 WHERE
					 (
						 (
							 "tPerson"."Surname" = NEWROW."Surname" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 ) OR
						 (
							 "tPerson"."Surname" = NEWROW."Surname" AND
							 "tPerson"."GivenName" = NEWROW."GivenName"
						 ) OR
						 (
							 "tPerson"."GivenName" = NEWROW."GivenName" AND
							 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
						 )
					 ) AND
					 "tPerson"."Check" = FALSE
			 )
			 THEN SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = 'There appears to be a similar Person already recorded. Please verify the Existing entry!';
		 END IF;
 END
helps here.
The reason for this trigger is that by changing information, there could be now a match with an existing record, that was not there before.
The User needs to verify the information, and make a decision. The decision could be YES indeed it is the same person, so the 'latest' must be removed, and all the links that are relevant must be rerouted to the initial entry, or NO the 'update is correct, and the Person is indeed unique.
The 'Check' can be set, so the 'before update' trigger allows the changes to proceed, and the 'check' can be removed.

This setup works in most cases, but NOT when the following is the case:
There is a person recorded with the same Surname, GivenName, but with a different DOB.
You could argue that you should set that Boolean 'check' for the existing record to prevent the 'before update' trigger to fire, but it might not be known to the User that there a similar person in the database.
You could also say, make the 'before update' trigger not look at that, but the verifications used in the 'before update' trigger are already as minimal as possible, so I do not really want to change much.
I also do not want to add a second Boolean 'check' because there is no way of telling how many similar persons are to be entered.

As a NOTE: All the Boolean 'Checks' in different tables are removed so that no related table does have any 'checks' set for a new entry, using

Code: Select all

'Remove all ""Check"

Sub Main(e)
Const cResetPersonCheck = "UPDATE ""tPerson"" SET ""Check""=False"
Const cResetPhoneFaxCheck = "UPDATE ""tPhoneFax"" SET ""Check""=False"
Const cResetEmailAddressCheck = "UPDATE ""tEmailAddress"" SET ""Check""=False"
Const cResetAddressBareCheck = "UPDATE ""tAddressBare"" SET ""Check""=False"
  oActiveConnection = e.Source.Model.Parent.ActiveConnection
  oPrepPersonCheck = oActiveConnection.prepareStatement(cResetPersonCheck)
n = oPrepPersonCheck.executeUpdate()
  oPrepPhoneFaxCheck = oActiveConnection.prepareStatement(cResetPhoneFaxCheck)
n = oPrepPhoneFaxCheck.executeUpdate()
  oPrepEmailAddressCheck = oActiveConnection.prepareStatement(cResetEmailAddressCheck)
n = oPrepEmailAddressCheck.executeUpdate()
 oPrepAddressBareCheck = oActiveConnection.prepareStatement(cResetAddressBareCheck)
n = oPrepAddressBareCheck.executeUpdate()
NextRecord 'Call Sub NextRecord
End Sub

Sub NextRecord
Dim oDocument As Object
Dim oDispatcher As Object
oDocument   = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDispatcher.executeDispatch(oDocument, ".uno:NewRecord", "", 0, Array())
End sub
Courtesy of 'GrandMaster' Villeroy

My questions are:
Can anyone indicate if they are dealing with the high level of uniqueness of personal information entries I am dealing with, and
How do they go about that?

I thought I could solve this with TRIGGER 'before insert' and 'before update', but am unable to figure out how to deal this, in the situation where it is unknown how many similar people are, going to be, entered.

Any suggestions/thoughts are really appreciated

Dream
Last edited by dreamquartz on Fri Jun 25, 2021 9:33 pm, edited 1 time in total.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique person

Post by Villeroy »

A unique constraint index solves this. It takes care that you can not update or insert any duplicates.
The Base index dialog can do this even with external HSQL. Open the table for editing and then Tools>Index Design... The creation will fail until you eliminated all duplicates which would violate the index.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Unique person

Post by dreamquartz »

@villeroy
I have to look into that again.
I remember that the User wanted to see a useful message in relation to an entry. That is why I started using TRIGGERS.
I might be able to come up with something here.

Preciate your comments,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unique person

Post by Villeroy »

On form level, you can handle this error with a form error event.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Unique person

Post by dreamquartz »

After discussion with the Client we decided to simplify the TRIGGER Before Update, because of all the scenarios we could come up with in relation to updating the Person Data.
We dropped the 'Surname'-'GivenName' Check as a whole.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply