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.

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

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

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.

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