eliminate duplicate entries

[uslegacies]

Using PHP and MySql, Is it possible to design a script that will check new data being submitted to the dB, against data that already exists in a table, to make sure there are no duplicate entries?

What we are trying to do is set up a new dB for genealogy information. The data we need to insert into MySql will come from multiple pre-existing files. We will be converting them over to a csv format prior to loading them into MySql. Now, here is our problem. We may come up with 10,000 people named John Smith. How can we compare the name, date and location of birth, or death, and parents names of existing data, to the new file being entered? If the new file is a new person, we want to assign him a new ID#, but if he already exists, we need to compare all the information in his family tree against existing data prior to replacing it.

This is going to be a major undertaking, so any input or help will be greatly appreciated.

[mrmufin]

One way to do this is to design your database table so that it only accommodates unique values.

CREATE TABLE person (
person_id INT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name CHAR(32) NOT NULL DEFAULT '',
middle_name CHAR(32) NOT NULL DEFAULT '',
last_name CHAR(32) NOT NULL DEFAULT '',
birth_date DATE NOT NULL,
birth_city CHAR(32) NOT NULL,
death_date DATE,
death_city CHAR(32),
moms_name CHAR(64) NOT NULL DEFAULT '',
dads_name CHAR(64) NOT NULL DEFAULT '',
UNIQUE person_index (first_name, middle_name, last_name, birth_date, birth_city, death_date, death_city, moms_name, dads_name)
);

Though a data structure like this might confound the 'E.F. Codd immaculati', it will assure that each John Smith has at least some distinction within the criteria you specified, while allowing for multiple John Smiths...

Have fun....