search

openreferral / specification

The Human Services Data Specification - a data exchange format developed by the Open Referral Initiative
https://openreferral.org
Other
117 stars 49 forks source link

Question: ER Diagram `SERVICE_AT_LOCATION` table's Primary key #190

Closed adityasrini closed 6 years ago

adityasrini commented 6 years ago

Hello maintainers, I have a question about your ER diagram. In your table titled SERVICE_AT_LOCATION, you have a primary key titled ID. @chewgley and I were trying to figure out whether or not the primary key was unique or if it was a composite key comprising of the service_id, and location-id fields. If it is a unique value for each entry, I want to know what purpose that would serve that wouldn't be served by a combination of the two fields.

NeilMcKLogic commented 6 years ago

Hi @adityasrini , I am not the official keeper of the spec, but my opinion is that if the service_at_location entity matches something in your own schema, then it would make sense to populate with its unique id. The purpose served is it would cleanly relate back to the source schema for purposes of doing updates, etc. In our case the equivalent entity is quite rich in data fields and functionality so we want to keep the reference to our unique id's intact when emitting HSDS.

If not, I would think a composite of service_id and location_id would make sense. I don't recall if HSDS specifies this id as an int or as a string, but if the latter you might do something like prefix the service_id with an "S" and the location_id with an "L", for clarity when generating the composite.

greggish commented 6 years ago

Thanks @NeilMcKechnie !

@adityasrini i can add this to the list of things to bring to @timgdavies' attention when he next cycles back here, but at first glance i trust Neil's suggestion. Just make sure to document whatever you choose. And let me know if I can help think this or other issues through in the meantime!

timgdavies commented 6 years ago

@adityasrini I'd agree with @NeilMcKechnie here. HSDS is agnostic about how you construct id values - and they will vary in their structure from system to system.