I've looked around on various websites in search for a "best practice" method when trying to avoid duplicate entries in a MySQL DB.
I did find basic information on how to detect duplicate entries, but I still fail to find substantial information on how to implement a complete solution to detect for duplication and then control the execution process within a stored procedure.
I believe this is where I will now have to explain myself better:
I collect patient demographic information and insert that into a MySQL database. The information is specific to:
Patient
|
-- Patient Details (table)
|
-- Patient Contact Details (table)
|
-- Patient Address Details - Street & Postal (table)
|
-- Patient Medical Insurance Details (table)
|
-- Patient Next of Kin Details (table)
|
-- Patient Employer Details (table)
Validation is done on "Patient Details" (First Name, Last Name, SSN, DOB, etc) with the following SP:
DELIMITER $$
CREATE DEFINER=`rootuser`@`%` PROCEDURE `SP_patAddDetails`(
IN pKey VARCHAR(1),
IN pTitle VARCHAR(15),
IN pFirstName VARCHAR(30),
IN pInitials VARCHAR(5),
IN pSurname VARCHAR(30),
IN pIDNo VARCHAR(15),
IN pDOB VARCHAR(10),
IN pGender VARCHAR(8),
IN pLanguage VARCHAR(15),
IN pOccupation VARCHAR(30),
IN pDependantCode VARCHAR(2),
IN pRelationship VARCHAR(15),
IN pMemNo VARCHAR(20),
IN pDateOpened VARCHAR(10)
)
BEGIN
START TRANSACTION;
INSERT INTO patDetails(patKey, patTitle, patFirstName, patInitials,
patSurname, patIDNo, patDOB, patGender, patLanguage,
patOccupation, patDependantCode, patRelationship,
patMemNo, patDateOpened, patUpdated)
SELECT * FROM (SELECT pKey, pTitle, pFirstName, pInitials,
pSurname, pIDNo, pDOB, pGender, pLanguage,
pOccupation, pDependantCode, pRelationship,
pMemNo, pDateOpened, now()) AS tmp
WHERE NOT EXISTS (
SELECT patTitle, patFirstName, patInitials, patSurname, patDOB, patMemNo
FROM patDetails
WHERE patTitle = pTitle
AND patFirstName = pFirstName
AND patInitials = pInitials
AND patSurname = pSurname
AND patDOB = pDOB
AND patMemNo = pMemNo
)
LIMIT 1;
COMMIT;
SELECT LAST_INSERT_ID()
COMMIT;
END
Currently I am controlling the validation process inside my VB.net Forms application by checking to see if I get a new database ID returned. If a new ID is returned I know that validation passed, the patient details were written to the DB and I get a new DB ID which I can then use as my foreign key during the rest of the process. (I am happy to edit the post to remove all the remarked code lines, things I tried, if it posses any reading difficulty)
I want to believe that there is a much more logical method of doing this within MySQL rather than doing it external, but I don't seem to find the information I need to achieve this...maybe it is meant to be done from an application rather from inside MySQL (which I want to be wrong about).
Regards,
T
What I have tried:
In the Stored Procedure there are commented lines of code of things I've tried but this is still a process which it controlled outside of MySQL.
-edit: grammar