Should person be of type bigint instead of int in the standard CDM DDL?

[ohdsi]

Should person be of type bigint instead of int in the standard CDM DDL?

[chrisknoll]

Hmm, I take it back, the cdm v5 ddl in the repo does use integer consistently but in our own environment we apply it as bigint. So, perhaps this issue should be to convert integer to bigint for mssql DDL.

[clairblacketer]

@chrisknoll is this still an issue?

[chrisknoll]

Yes and No. See here:
https://github.com/OHDSI/CommonDataModel/blob/master/Sql%20Server/OMOP%20CDM%20ddl%20-%20SQL%20Server.sql#L227

Originally, I thought i found cases where person_id was a bigint in the default DDL but I must have been looking at our internal CDM schema and not the raw DDL on the CDM repo.

The 'No' part is that the CDM schema DDL on the repo is consistent: all person_ids are declared as Integer.

The 'Yes' part is that the sources that we ETL commonly have values for person ID that exceed the limits of an integer, and therefore bigint might be more appropriate. That's the root of the issue I reported here. I'll update the title to reflect this.

@cgreich , could you chime in on this, is this the sort of thing that should be a WG change or is this something we can leave as int and let third parties make a one-off change to their CDMs if an integer is not large enough to store a person_id?

[vojtechhuser]

what is the boundary for int - big int? Is the boundary the same for all db engines? Change to bigint in canonical specs would be a CDM change to discuss in the CDM WG (in my view)

[chrisknoll]

yes, bigint is same across databases: 64bit number, vs int which is 32bit.

[cgreich]

Not sure what to say. We have been not being specific about this and leave it to the ETLers. Why do you think it is necessary to standardized this? Is there a consequence of some folks have it as INT?

[chrisknoll]

Nope. If left to their own ETL process, it can be handled that way.