Database model

1.DREAM 6 Database #

The DREAM 6 database model mainly consists of the following tables:
  • D_Person , D_Person_att and D_Person_fam, contain people’s data and their family relationships
  • D_Events and D_Events_att ,┬á which contain scheduled events (appointments) or events that have occurred, related to people
  • D_Evaluations and D_Evaluations_att┬á ,which contain assessments and questionnaires, related to people
  • D_Status which contain status changes related to people
  • D_Dictionary contains the dictionary of the variables and indicators used and indicates their position in the attribute tables
  • D_RefTables contains reference tables
the tables with the suffix “_att” contain additional attributes to dynamically expand the information that can be stored. Tables ending in “_att” ┬ámay have multiple child rows referring to the parent record, each identified by a particular domain value.

2.D_Person and D_Person_att #

D_Person

DREAM 6 NOTE
 id_person unique id for the database
 id_dream unique id for the DREAM center
 id_national unique id for the national health system
 id_usr1 service id 1
 id_usr2 service id 2
 pe_surname Surname
 pe_name Name
 pe_dob date of birth
 pe_birth_place birth place
 pe_sex gender
 pe_address address
 pe_phone phone number
 pe_marital_status marital status
 date_started date of start of assistance
 date_stopped end date of assistance
 reason_stopped reason for the end
 note_stopped note for the end
 date_assistance_start end date of assistance in this center
 pe_note notes
 pe_medical_note medical notes
 pe_district district
 pe_province province
 pe_town town
 pe_area area
 pe_father_name father name
 pe_mother_name mother name
 pe_center_ref center of reference
 pe_document document type and number
 pe_guardianname guardian name
 pe_guardianphon guardian phone
 pe_guardianrela guardian relationship
 pe_id_person_mo database id of the mother
┬ápe_born_program P –> PMTCT_C
E –> PMTCT_E born in the vertical transmission prevention program
 usr_ins user who entered the data
 usr_mod user who changed the data
 usr_datamod date of modification of the data
┬ádeleted true if the record was ‘deleted’

 

D_Person contain peopleÔÇÖs data. Some information is stored directly in this table. Other additional information can be stored in the related attribute table D_PERSON_ATT

For example in D_PERSON_ATT, the row with domain value A_PEDIATRIC indicates the information regarding the pediatric card.

 

D_PERSON_ATT (┬ádomain =┬á ‘A_PEOPL’)

Column name NOTE
id_person unique id for the database
domain ‘A_PEOPL’
vt1
vt2 Destination center if transfered
vt3 Destination center ID if transfered
vt4
vt5
vt6
vt7
vt8
vt9
vt10
vt11
vt12
vt13 Coming from Center (name)
vt14  Coming from Center ID
vt15 smoker ?
vn1 N┬░ abortos
vn2 N┬░ children alive
vn3 ID assistant
vn4  N° children
vn5  N° births
vd1
vd2
vd3
vd4
vd5
vb1 Agree to FUP
vb2
vb3
vb4

 

D_PERSON_ATT (┬ádomain =┬á ‘A_PEDIATRIC’)

Column name Note
id_person unique id for the database
domain =┬á ‘A_PEDIATRIC’
vt1 unique id of the father
vt2 unique id for pregnancy
vn1 height at birth
vn2 APGAR at birth
vn3
APGAR 2 at birth
vn4 birth weight
vd1 time of birth
vd2 nevirapine date
vd3 nevirapine time

3.D_Status #

D_Status in DREAM 6 Note
id_status unique id of the status for database
id_person D_Person unique id
domain domain of the status
date_started date of start of status
status status
status_type status type
status_reason status reason
status_treatment status treatment
status_treat_type treatment type
status_diag
st_vn1 numeric 1
st_vn2 numeri 2
st_vd1 date 1
st_vd2 date 2
st_vt1 text 1
st_vt2 text 2
st_vt3 text 3
st_vt4 text 4
st_vt5 text 5
st_vb1 bool 1
st_vb2 bool 2
st_vm1 note 1
st_vm2 note 2
usr_ins
usr_mod
usr_datamod
usr_del
usr_datadel
active = 1 if last
first = 1 if first
restart = 1 if restart (from No to Yes)
deleted = 1 if deleted

D_Status table contain status changes related to people. For example , when a particular assistance program started and ended. Or when he started or stopped a certain type of therapy.

Each particular state that you want to control is identified by a specific domain.

For example, the ASSIS domain identifies the type of assistance.

date_started stores the date on which a certain status started, while the status field indicates the type of service that started on that date. ( For example HOMECARE or DAYHOSP  service)

 

Value of the status field Description Values Description
ST_PREGN pregnancy
status (whether pregnant or not)
YES
or NO
TBSTA Status
of TB therapy
TBC_PRE_COT Preventive
therapy with Cotrim.
TBC_SI
and TBC_SI_2
Yes
TBC_NO No
FOODI Status
of Food Integration 
FOOD_INT_YES Yes
FOOD_INT_NO No
ASSIS Assistance
regime
HOMECARE Home
Care
DAYHOSP Day
Hospital
RICOVERO Assistance
in hospitalization
SERVICE ENDASS End
of Assistance
NODREAM General
Medicine
MCPC PTV
MCPC_C PTV
for Children
CCHC HAART
VCT Voluntary
Counselling and Testing
TBCARE In
TB Care
PEP In
Post Exposition Prevention
TRANSIT In
Transit
TARV Status
of antiretroviral therapy
TARV_NO Not
in ARV
TARV_SI In
ARV
HIVST HIV_INSEC Insecure
reult
HIV_NO HIV
Negative
HIV_STBY In
standby
HIV_YES HIV
Positive
HIV_TEST In
testing
FEED Type
of feeding for newborns
TA_ARTIFIC artificial
feeding
TA_MISTO mixed
breastfeeding
TA_ALSENO breastfeeding
TER-COT cotrimoxazole
therapy
NO
or YES
TBC_PRE_TPI isionazide
therapy
NO
or YES
TER_FLU Fluconazole
therapy
NO
or YES
BFEED Is
(mother) breastfeeding ?
NO
or YES
PAR_GAAC NO
or YES
self
help group
PAR_FASTFLOW NO
or YES
Fast
flow
PAR_3MDELIVERY NO
or YES
quarterly
delivery
PAR_6MDELIVERY NO
or YES
half-yearly
delivery

4.D_Events and D_Events_att #

D_Events

contain scheduled events (appointments) or events that have occurred, related to people

Physical
Column Name
Type PK Nullable  Note
id_event CHAR PK NOT NULL
id_person CHAR NOT NULL
domain NVARCHAR(50) NOT NULL
event_type NVARCHAR(20)
date_apt DATE
date_apt_exe DATE
apt_status NVARCHAR(20)
date_start DATE
date_event DATE
event_status NVARCHAR(255)
ev_vn1 FLOAT
ev_vn2 FLOAT
ev_vn3 FLOAT
ev_vn4 FLOAT
ev_vn5 FLOAT
ev_vd1 DATE
ev_vd2 DATE
ev_vd3 DATE
ev_vd4 DATE
ev_vd5 DATE
ev_vt1 NVARCHAR(255)
ev_vt2 NVARCHAR(255)
ev_vt3 NVARCHAR(255)
ev_vt4 NVARCHAR(255)
ev_vt5 NVARCHAR(255)
ev_vm1 [-16]
ev_vm2 [-16]
deleted BIT
usr_ins NVARCHAR(20)
usr_mod NVARCHAR(20)
usr_datamod DATE
usr_del NVARCHAR(20)
usr_datadel DATE
time_stamp DATE

D_Events_att

Column Name Type PK Nullable  Note
id_events_att INTEGER PK NOT NULL
id_event CHAR NOT NULL
id_person CHAR NOT NULL
domain NVARCHAR(15) NOT NULL
att_date DATE
vn1 –> vn30 FLOAT
vt1 –> vt30 NVARCHAR(255)
vb1 –> vb50 BIT
vd1 –> vd4 DATE
vm1 NOTE
vm2 NOTE
time_ttamp DATE

 

For example, a medical examination is an event.  In the D_Events table we will find for each visit a row identified by the APPOINTMENT domain and by the VISIT event_type. The appointment date and the execution date will be specified in this line.

The visit data are instead stored in the D_events_att table with domain A_VISIT. For children, an additional line with the A_MOTORM domain will store information regarding the assessment of psychomotor development. Further evaluation regarding the visit could in the future occupy a new row in d_events_att identified by a new domain.

5.D_Evaluation and D_Evaluation_att #

These tables contain the assessments and questionnaires relating to people.
Currently, the data concerning the social card and the evaluation on the hiv stage are stored in these tables.

D_Evaluation
filed name description
id_eval
id_person
domain
category
eval_date
eval_status
eval_value
eval_note
first
active
deleted
usr_INS
usr_MOD
usr_DATAMOD
usr_DEL
usr_DATADEL
Time_Stamp

 

D_Evaluation_att
filed name description
id_eval_att Primary Key
id_eval Foreign Key
id_person
domain
eval_date
vn1
vn2
vn3
vn4
vn5
vn6
vn7
vn8
vn9
vn10
vn11
vn12
vn13
vn14
vn15
vn16
vn17
vn18
vn19
vn20
vn21
vn22
vn23
vn24
vn25
vn26
vn27
vn28
vn29
vn30
vd1
vd2
vd3
vd4
vt1
vt2
vt3
vt4
vt5
vt6
vt7
vt8
vt9
vt10
vt11
vt12
vt13
vt14
vt15
vt16
vt17
vt18
vt19
vt20
vt21
vt22
vt23
vt24
vt25
vt26
vt27
vt28
vt29
vt30
vb1
vb2
vb3
vb4
vb5
vb6
vb7
vb8
vb9
vb10
vb11
vb12
vb13
vb14
vb15
vb16
vb17
vb18
vb19
vb20
vb21
vb22
vb23
vb24
vb25
vb26
vb27
vb28
vb29
vb30
vb31
vb32
vb33
vb34
vb35
vb36
vb37
vb38
vb39
vb40
vb41
vb42
vb43
vb44
vb45
vb46
vb47
vb48
vb49
vb50
vm1
vm2
Time_Stamp
Help Guide Powered by Documentor
Suggerire  la  modifica