Table PERS_APPLN : violation of primary key, errors?

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

ndbac41
Posts: 6
Joined: Mon May 07, 2007 6:18 pm

Table PERS_APPLN : violation of primary key, errors?

Post by ndbac41 » Thu Jan 30, 2014 9:47 am

I'm in the middle of uploading the latest PATSTAT version, and I encounter a problem I already reported years ago: the suggested primary key for table PERS_APPLN is not valid, i.e. the combination of fields PERSON_ID and APPLN_ID is not unique.

First of all, the suggested primary key is not the logical primary key, the logical primary key is the combination of fields APPLN_ID, APPLT_SEQ_NR and INVT_SEQ_NR (a patent is related to applicants and inventors).

From a practical perspective, I agree it makes sense to make the combination of PERSON_ID and APPLN_ID the primary key (individual searches will mostly target APPLN_ID and PERSON_ID, and rarely APPLT_SEQ_NR or INVT_SEQ_NR).

But, as said, this primary key cannot be validated, the same combination of APPLN_ID and PERSON_ID can occur multiple times.

Just one example:

PERSON_ID APPLN_ID APPLT_SEQ_NR INVT_SEQ_NR
15370756 14864515 2 0
15370756 14864515 1 0

The same person is assigned to the same patent as first and second applicant.

This does not make sense. What is going on?

Regards,
Tom Magerman


nico.rasters
Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm
Contact:

Re: Table PERS_APPLN : violation of primary key, errors?

Post by nico.rasters » Thu Jan 30, 2014 4:02 pm

From the documentation: "Conceptually, the combination of PERSON_ID and APPLN_ID should be unique. In practice, due to duplicates in the source data also the attributes APPLT_SEQ_NR and INVT_SEQ_NR must be part of the Primary Key."
So they are aware of the issue, but just haven't corrected it yet.

Btw, note that there is a new TLS209 table.
https://dl.dropboxusercontent.com/u/580 ... merged.zip
________________________________________
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship
http://www.daigu.nl/


Post Reply