Changelog

We will attempt to track all changes to the datamodel within this section.. clicking on the resultant changes will take you to those sections within the table documentation..

Version: 1.00β (Development)

• Concept
  • (+) concept_proposal (new table, click on link for more information)
  • (+) concept_proposal.concept_proposal_id (PK, required, int)
  • (+) concept_proposal.concept_id (FK: concept.concept_id, required)
  • (+) concept_proposal.encounter_id (FK: encounter.encounter_id)
  • (+) concept_proposal.original_text (varchar 255, required)
  • (+) concept_proposal.final_text (varchar 255)
  • (+) concept_proposal.obs_id (FK: obs.obs_id)
  • (+) concept_proposal.obs_concept_id (FK: concept.concept_id)
  • (+) concept_proposal.state (varchar 32, required)
  • (+) concept_proposal.comments (varchar 255)
  • (+) concept_proposal.creator (FK: users.user_id, required)
  • (+) concept_proposal.date_created (datetime, required)
  • (+) concept_proposal.changed_by (FK: users.user_id)
  • (+) concept_proposal.date_changed
• Form
  • (+) field.default_value (text)
  • (+) form.build (int)
  • (+) form.published (tinyint)
  • (+) form.encounter_type (FK: [[)
  • (+) form.infopath_solution_version (varchar 50)
  • (+) form.uri (varchar 255)
  • (+) form.xslt (mediumtext)
  • (-) form.definition
• Obs
  • (+) note.parent (FK: note.note_id)
  • (u) obs.comment = obs.comments
  • (-) note.weight
• Patient
  • (+) patient_identifier.preferred (tinyint)
  • (+) patient_identifier_type.check_digit (tinyint)
• Relationship
  • (u) relationship.relationship =(FK: relationship_type.relationship_type_id)
  • (u) relationship_type.relationship_id = relationship_type.relationship_type_id
• User
  • (+) role_role (new table)
  • (+} role_role.parent_role (FK: role.role, PK)
  • (+) role_role.child_role (FK: role.role, PK)
  • (+) users.system_id (varchar 50)
  • (+) user_property.property (varchar 100, required)
  • (u) user_property.value = user_property.property_value
  • (-) groups
  • (-) group_role
  • (-) user_group
• Property
  • (+) global_property (new table)
  • (+) global_property.property (varchar 255)
  • (+) global_property.property_value (varchar 255)

Version: 0.81 (Release)

• • (u) concept_datatype.datatype_abbreviation = concept_datatype.hl7_abbrevation
  • (u) concept.retired = concept.retired (required)
  • (u) concept_class.is_set = concept_class.is_set (required)
  • (u) concept_numeric.precise = concept_numeric.precise (required)
  • (u) drug.combination = drug.combination (required)
  • (-) concept_numeric.creator
  • (-) concept_numeric.date_created
  • (-) concept_numeric.changed_by
  • (-) concept_numeric.date_changed
• Encounter
  • none yet
• Form
  • (+) field.default_value (varchar 1024)
  • (u) field.select_multiple = field.select_multiple (required)
  • (u) field.name = field.name (required)
  • (u) field_type.is_set = field_type.is_set (required)
  • (u) form.retired = form.retired (required)
  • (u) form_field.required = form_field.required (required)
• Obs
  • (+) note.parent (FK: note.note_id)
  • (u) obs.voided = obs.voided (required)
  • (-) note.weight
• Order
  • (u) drug_order.complex = drug_order.complex (required)
  • (u) drug_order.prn = drug_order.prn (required)
  • (u) orders.voided = orders.voided (required)
  • (u) orders.discontinued = orders.discontinued (required)
• Patient
  • (+) patient_identifier_type.format (varchar 50)
  • (u) patient.voided = patient.voided (required)
  • (u) patient_address.preferred = patient_address.preferred (required)
  • (u) patient_address.voided = patient_address.voided (required)
  • (u) patient_identifier.voided = patient_identifer.voided (required)
  • (u) patient_name.preferred = patient_name.preferred (required)
  • (u) patient_name.voided = patient_name.voided (required)
  • (u) tribe.retired = tribe.retired (required)
  • (-) patient_identifier.format
• Relationship
  • (u) relationship.voided = relationship.voided (required)
• User
  • (+) user_group (new table)
  • (+) user_group.user_id (FK: users.user_id) (required)
  • (+) user_group.group_name (FK: groups.group_name) (required)
  • (+) user_property (new table)
  • (+) user_property.user_id (PK, FK: users.user_id)
  • (+) user_property.property (PK, varchar 100)
  • (+) user_property.value (varchar 255)
  • (u) users.voided = users.voided (required)
  • (u) group = groups
  • (u) group.group = groups.group_name
  • (u) group_role.group = group_role.group_name

Version: 0.80

• Concept
  • (+) concept_map (new table)
  • (+) concept_map.concept_map_id (int, PK, AI)
  • (+) concept_map.source (FK: concept_source.concept_source_id)
  • (+) concept_map.source_id (int)
  • (+) concept_map.comment (varchar 255)
  • (+) concept_map.creator (FK: users.user_id)
  • (+) concept_map.date_created (datetime)
  • (+) concept_source (new table)
  • (+) concept_source.concept_source_id (int, PK, AI)
  • (+) concept_source.name (varchar 50)
  • (+) concept_source.description (varchar 1024)
  • (+) concept_source.hl7_code (varchar 50)
  • (+) concept_source.creator (FK: users.user_id)
  • (+) concept_source.date_created (datetime)
  • (+) concept_source.voided (tinyint)
  • (+) concept_source.voided_by (FK: users.user_id)
  • (+) concept_source.date_voided (datetime)
  • (+) concept_source.void_reason (varchar 255)
  • (+) concept_word.concept_word_id (int, PK, AI)
  • (+) concept_word.synonym (varchar 255)
  • (+) concept_word.locale (varchar 255)
  • (u) concept_datatype.definition = concept_datatype.description
  • (u) concept.description moved to concept_name.description
  • (u) concept_name.name = concept_name.name (removed PK status)
  • (u) concept_set.concept_set = concept_set.concept_set (FK: concept.concept_id)
  • (u) concept_set2 = concept_set_derived
  • (u) concept_word.concept_id = concept_word.concept_id (FK: concept.concept_id)
  • (u) concept_word.word = concept_word.word (varchar 255)
  • (-) concept.bev_count
  • (-) concept.column_name
  • (-) concept.correct_concept
  • (-) concept.name_alt
  • (-) concept.old_datatype
  • (-) concept.table_name

• Encounter
  • (+) encounter.form_id (FK: form.form_id)
  • (u) encounter.creator_id = encounter.creator
  • (u) location.zipcode = location.postal_code

• Form
  • (+) form.retired_reason (varchar)
  • (+) form.retired_by (FK: users.user_id)
  • (+) form.date_retired (datetime)

• Observation
  • (+) mime_type.mime_type_id (int, PK)
  • (+) note (new table)
  • (+) note.note_id (PK, int)
  • (+) note.note_type (varchar 50)
  • (+) note.patient_id (FK: patient.patient_id)
  • (+) note.obs_id (FK: obs.obs_id)
  • (+) note.encounter_id (FK: encounter.encounter_id)
  • (+) note.text (text)
  • (+) note.priority (int)
  • (+) note.weight (int)
  • (+) note.creator (FK: users.user_id)
  • (+) note.date_created (datetime)
  • (+) note.changed_by (FK: users.user_id)
  • (+) note.date_changed (datetime)
  • (+) obs.date_started (datetime)
  • (+) obs.date_stopped (datetime)
  • (+) obs.accession_number (varchar 255)
  • (u) obs.value_text = obs.value_text (text)
  • (u) mime_type.description = mime_type.description (text)
  • (u) complex_obs.mime_type (string) = complex_obs.mime_type_id (FK: mime_type.mime_type_id)

• Patient
  • (+) patient_address.preferred (tinyint)
  • (+) patient_identifier.format (varchar 255) ...serves as regular expression format specifier
  • (+) patient_name.changed_by (FK:users.user_id)
  • (+) patient_name.date_changed (datetime)
  • (+) patient.changed_by (FK: users.user_id)
  • (+) patient.date_changed (datetime)
  • (u) patient.tribe = patient.tribe_id
  • (u) patient_identifier.location = patient_identifier.location_id

• Order
  • (u) drug_orders = drug_order
  • (-) drug_order.creator
  • (-) drug_order.date_created
  • (+) orders.date_voided (datetime)
  • (+) orders.discontinued (tinyint)
  • (+) orders.voided (tinyint)
  • (+) orders.voided_by (FK: users.user_id)
  • (+) orders.void_reason (varchar)

• User
  • (+) group (new table)
  • (+) group.group (PK, varchar 50)
  • (+) group.description (varchar 255)
  • (+) group_role (new table)
  • (+) group_role.group (FK: group.group)
  • (+) group_role.role (FK: role.role) NOTE: could not write this FK for some reason.. looking into why (PB)
  • (+) users.secret_question (varchar 255)
  • (+) users.secret_answer (varchar 255)
  • (+) users.salt (varchar 50)
  • (+) users.void_reason (varchar)
  • (u) users.user_name = users.username

Version: 0.70

Current Picture of Data Model::amrs_data_model_0.70.png/view right click here:amrs_data_model_0.70.png to download .PNG -- the most recent graphical representation of our data model

Current XML Version of the data model:amrs_data_model_0.70.xml/download -- to work with the model download DBDesigner:http://www.fabforce.net/downloads.php

Previous Versions

Version 0.60: .PNG:amrs_data_model_0.60.png/view & .XML:amrs_data_model_0.60.xml/download

Version 0.52: .PNG:amrs_data_model_0.52.png/view & .XML:amrs_data_model_0.52.xml/download

Version 0.51: .PNG:amrs_data_model_0.51.png/view & .XML:amrs_data_model_0.51.xml/download

Version 0.50: .PNG:amrs_data_model_0.50.png/view & .XML:amrs_data_model_0.50.xml/download

Version 0.01: .PNG:amrs_data_model_0.01.png/view & .XML:amrs_model.xml/download

Version 0.001: .PNG:amrs_data_model_0.001.png/file_view

Changelog

Version: 0.60

• Patient
  • (+) patient.mothers_name (varchar). Serves as a placeholder for maiden name or other reported maternal identifer.
  • (+) patient_address.creator (FK: users.user_id)
  • (+) patient_address.date_created (datetime).
  • (u) patient_name_alias.family_name = patient_name.surname1.
  • (u) patient_name_alias.last_name_prefix = patient_name.surname2.
  • (u) patient_number_alias.number = patient_number_alias.medical_record_number.
  • (+) tribe.retired (bit).
• Encounter
  • (+) encounter.encounter_type (FK: encounter_type.encounter_type_id).
  • (+) encounter_type (new table):
  • (+) encounter_type.encounter_type_id (int).
  • (+) encounter_type.name (varchar).
  • (+) encounter_type.description (varchar).
  • (+) encounter_type.creator (FK: users.user_id)
  • (+) encounter_type.date_created (datetime).
  • (+) location.creator (FK: users.user_id).
  • (+) location.date_created (datetime).
• User
  • (+) users#old_user.users.old_user (int)
  • (u) users.passwrd = [[users#password|users.password].
  • (-) users.enabled

CONCEPT:

term:

added view_count (int).

term_class:

added is_set (bit).

term_drug:

added term_drug_id (int) (primary key).

added name (varchar).

added creator (FK: users.user_id -> term_drug_creator).

added date_created (datetime).

term_numeric (renamed from term_measurement):

added precise (bit).

added changed_by (FK: users.user_id -> user_who_changed_term_numeric).

added date_changed (datetime).

term_note (new table):

added term_id (FK: term.term_id -> term_notes).

added creator (FK: users.user_id -> term_note_creator).

added date_created (datetime).

added note (blob).

term_set:

added creator (FK: users.user_id -> set_creator).

added date_created (datetime).

FORM (new section):

field (new table):

added field_id (int).

added name (varchar).

added description (varchar).

added field_type (FK: field_type.field_type_id -> field_type).

added term_id (FK: term.term_id -> term_for_field).

added table_name (varchar).

added attribute_name (varchar).

added select_multiple (bit).

added creator (FK: users.user_id -> user_who_created_field).

added date_created (datetime).

added changed_by (FK: users.user_id -> user_who_changed_field).

added date_changed (datetime).

form_field (new table):

added form_field_id (int).

added form_id (FK: form.form_id -> form_containing_field).

added field_id (FK: field.field_id -> field_within_form).

added field_number (int).

added field_part (varchar).

added page_number (int).

added parent_form_field (FK: form_field.form_field_id -> form_field_hierarchy)

added min_occurs (int).

added max_occurs (int).

added required (bit).

added creator (FK: users.user_id -> form_field_creator).

added date_created (datetime).

added changed_by (FK: users.user_id -> user_who_changed_form_field).

added date_changed (datetime).

field_answers (new table):

added field_id (FK: field.field_id -> field_answers).

added answer_id (FK: term.term_id -> answers_for_field).

added creator (FK: users.user_id -> field_answer_creator).

added date_created (datetime).

field_type (new table):

added field_type_id (int).

added name (varchar).

added description (varchar).

added is_set (bit).

added creator (users.user_id -> field_type_creator).

added date_created (datetime).

form (new table):

added form_id (int).

added name (varchar).

added version (varchar).

added description (text).

added schema_namespace (varchar).

added definition (text).

added retired (bit).

added creator (users.user_id -> form_creator).

added date_created (datetime).

added changed_by (users.user_id -> user_who_changed_form).

added date changed (datetime).

OBSERVATION:

obs:

added glob_id (int).This value allows the system to link question concepts together to provide a more complex way of representing clinical data.See the updated description of the potential use cases, at: http://amrs.iukenya.org/docs/obsprimer:http://amrs.iukenya.org/docs/obsprimer.

added obs_datetime (datetime).

changed enterer to creator.

changed data_entry_time to date_created.

complex_obs:

added complex_value (blob).

ORDER:

orders:

changed order_comment to comment.

changed creator (FK: users.user_id -> order_creator).

drug_orders:

added start_reason (FK: term.term_id -> reason_started).

added dose_frequency (varchar).

added discontinued_reason (FK: term.term_id -> reason_discontinued).

changed orderer (FK: users.user_id -> user_who_ordered_drug).

changed stop_date to discontinue_date.

changed term_id to term_drug_id (FK: term_drug.term_drug_id -> drug_ordered).

removed discontinued (bit).Superceded by discontinued_date and discontinue_reason.

changed term_id (FK: term.term_id -> primary_drug_term).

Version: 0.52

obs:

added numeric_modifier (varchar).This value is ordinal in nature, and allows for more complex characterization of numeric values (ie, <4, 2-4, >1000, etc).See a more detailed description, with use cases, at:http://amrs.iukenya.org/docs/obsprimer:http://amrs.iukenya.org/docs/obsprimer.

added foreign key for term_id (FKterm.term_id -> obs_term)

added foreign key for value_coded (FKterm.term_id -> answer_term)

patient:

changed datatype of death_date to datetime

removed required flag of race

users:

added creator (FKusers.user_id -> user_creator)

added date_created (int)

added changed_by (FKusers.user_id -> user_who_changed_users)

added date_changed

added enabled (bit)

added password (varchar)

Version: 0.51

complex_obs:

changed the paradigm of data storage.Each new complex observation�??s data will be referenced by a Uniform Resource Name (URN), which serves as a persistent reference to a data source.It�??s basically a non-volatile pointer which gives our data model the flexibility to refer to locations in separate physical files, or other solutions if necessary.

drug_orders:

changed �??expire_date�?? to �??completion_date�??

added quantity (int)

added dose_frequency (varchar(250) for now.. option to code these answers in a separate table.. likelythat there will be a combinatorial explosion of possibilities.. ie, 3 qam, 2 qnoon, and 3 qpm vs. 1 bid etc)

added discontinued (bit)

added discontinue_reason (varchar(1024))

added discontinued_by (FKusers.user_id -> user_who_discontinued_drug)

added creator (FKusers.user_id -> drug_order_creator) (required)

added date_created (datetime) (required)

removed weekly_dose.Useful in some settings, but paradigm isn�??t useful across a standardized data model.For example, there are some drugs that might be dosed across multiple months (depo-provera, B12 injections, etc).This can be optionally placed in model if needed.

encounter:

added patient_id (FKpatient.patient_id -> encounter_patient)

renamed encounter_date to encounter_datetime

deleted form, field, and form_field

moved icd10 to DEVELOPMENTAL/BUSINESS group

deleted icd10_counts

location:

added description (varchar)

added address1 (varchar)

added address2 (varchar)

added city_village (varchar)

added state_province (varchar)

added country (varchar)

added zipcode (varchar)

added latitude (varchar)

added longitude (varchar)

obs:

removed value_complex:column made obsolete by complex_obs.. still some debate on whether complex_obs table is needed, but sticking with separate table for now

orders:

added instructions (varchar(1024))

added order_status (varchar)

added comment (varchar(255))

added discontinued (bit)

added discontinued_by (FK: users.user_id -> user_who_discontinued_order)

added discontinued_reason (varchar(255))

added creator (FK: users.user_id -> order_creator) (required)

added date_created (datetime) (required)

added auto_expire_date (to auto-expire orders)

patient:

renamed surname1 to family_name, based on conventions in HL7

deleted surname2:can be an optional attribute for a model if needed, not included within HL7 standard

added last_name_prefix (varchar):part of HL7 standard

added creator (FK:users.user_id -> patient_creator) (required)

added date_created (datetime) (required)

patient_name_alias:

renamed surname1 to family_name, based on conventions in HL7

deleted surname2:can be an optional attribute for a model if needed, not included within HL7 standard

added last_name_prefix (varchar):part of HL7 standard

added creator (FK:users.user_id -> patient_name_alias_creator) (required)

added date_created (datetime) (required)

patient_number_alias:

added location (FK:location.location.id -> mrn_alias_location): specifies location that�??s specific to alias.. 3rd part of table�??s primary key (required)

added creator (FK: users.user_id -> mrn_alias_creator) (required)

added date_created (datetime) (required)

renamed patient_medical_record_numbers relationship to patient_mrn

term:

added changed_by (FK: users.user_id -> user_who_changed_term)

added date_changed (datetime)

term_class:

deleted is_set

moved term_legacy to DEVELOPMENTAL/BUSINESS group

moved term_note to DEVELOPMENTAL/BUSINESS group

term_set:

changed sort_value to sort_weight

Version: 0.50

Original release of changelog

Version: 0.01

Regenstrief / Partners-in-Health summit in Indianapolis

Version: 0.001

Initial brainstorming in Eldoret, Kenya