Data types managed database design for dynamic content: A database design for Personal Health Book system

Data types managed database design for dynamic content A database design for Personal Health Book system Seddiq Alabbasi, Ashir Ahmed, Kunihiko Kaneko, Andrew Rebeiro-Hagrave and Akira Fukuda Department of Advanced Information Technology Kyushu University Fukuoka, Japan Abstract— Electronic Health Records (EHR) are digitized and stored for efficient patient management in hospitals or clinics. At the patient side, health records are not digitally stored and thus difficult to manage across different hospitals. We propose a new system, where medical sources (firms & devices) import a person’s medical records into Personal Health Book (PHB). This will give the person (the owner of the records) the capability to manage, control and share his/her own Personal Health Records (PHR). However, medical sources have different structure and database design. In this paper, we propose a decomposed database design for PHB that accommodate different structured data. We test the capability of generating a meaningful report and compare it to the original EHR received from source (Hospital). We use a framework that exports the data from PHB into an Extensible Markup Language (XML) file format, then view it using predefined Extensible Stylesheet Language (XSL). We observe that our database design can generate a report with same content of original EHR in a meaningful manner that the owner and authorized parties can understand it. Keywords—Electronic Health Record (EHR); Personal Health Record (PHR); Personal Health Book (PHB); life-time data; database; XML; database design; XSL; Entity Relationship (ER);

I.

INTRODUCTION

We previously introduced a solution for storing all the persistent data & transitory data related to a person in one location and called it Personal Life-time data and its smart management [1]. Personal Life-time data can be profession related, academic related, resident related, health related, finance related and transport related. In fact it is any data related to an individual. As the research scope is large, we decided to focus on healthcare domain to test the capability of having such solution. We introduced the concept of a Personal Health Book (PHB) [2] in which our main goal is to allow Personal Health Records (PHR) from multiple sources to be portable, manageable, controllable and sharable by the person (see Fig. 1.). All PHR will be stored in one centralized location and the person will have full control to manage his or her own health records and share with doctors, family and friends. Multiple Electronic Health Record (EHR) sources with different data types and structures raise a question; how should the database

978-1-4799-4075-2/14/$31.00 © 2014 IEEE

Fig. 1. Personal Health Book (PHB) basic structure

of PHB be designed so that it can be dynamic and we can import all incoming data regardless of how it is originally structured. We decided to focus on the database design and make sure that PHB database can dynamically accommodate all incoming data from any medical firm, even if there were new items or terms introduced. At the same time we should be able to export data from PHB in a meaningful manner, that the owner or any shared party can understand it, with same content as originally imported. We started by implementing a simulation to decide which database design direction to go for and concluded that a decomposed model will be the best to achieve our purpose [2]. In this paper we introduce a decomposed database for PHB and use Entity Relationship (ER) diagram to enable a better understanding of how data is related in PHB database structure. This is helped us to design a framework which understands PHB database structure that is capable of querying the data more efficiently, and built an Extensible Markup Language (XML [3]) file that can be used for sharing or viewing by incorporating Extensible Stylesheet Language (XSL [4]). In section 2 we introduce the database design and explain its structure and data integrity using an ER diagram. In section 3, we explain how the framework builds an XML file from the database and view it using an XSL. In Section 4, we discuss the results of the sample data queried from PHB database and compare it to the content of the original EHR imported into PHB. We conclude our paper in section 5.

Fig. 2. Entity Relationship Diagram of PHB database.

II. PHB DATABASE DESIGN A. Entity Relationship Diagram of PHB The Entity Relationship (ER) diagram is used to elaborate a non-technical explanation of how PHB database entities are interconnected and give a meaningful output when the flow followed, as shown in Fig. 2.

developed in that environment, as basic database concepts are the same with all database engines. As it shown in Fig. 3. PHB database consists of 8 tables: •

User Profile table: User personal details, such as name, date of birth, and address.

•

Items table: All data naming/label will be stored here, as an index table for them, as any new item in the future introduced, it will be stored here too. An item can be name, age, temperature, height and description.

•

Categories table: Category table is used to manage data types, such as hospital, doctor, drug and checkup details.

•

Templates table: Because PHB is accepting all data coming from different sources with different structures, there is a need to keep track what items related to each source.

Similarly, other connected entities have relationships in a meaningful way.

•

B. Database of PHB For experiment and proof of concept we used MS SQL database. However, this does not mean that PHB has to be

Sources table: All data sources details will be kept in this table, such as hospitals, doctors, and drugs. A source can be sub of another source (for an example, doctor is sub of hospital)

•

Records table: All health records will be mainly stored in records table as well as related records for an

In the ER diagram, we view the entities User, Record, Source, Category, Value and Item. Among these entities, relationships exist which connect the entities in the diagram. For an example, User and Record are connected via the relationship “Owns” (one to many). In other words, the user will own many records. Similarly, entities Source and Record are connected via the relationship “Imported from”. Here, a record will be imported from a source. In another scenario, record and Value are connected via “Has values”. Which means, a record will have many values.

Fig. 3. Database of PHB

example, a one hospital visit record has some prescribed drugs and lab reports, these records have their own related data, different elements and more than one occurrence. Hence they will be saved as subrecords in the records table. •

•

Record Source Map table: Each record sored in the database, will be related to a certain source, such as, a doctor can be a source of a record, or a drug will be source of the prescription. Values table: All the values of the items that specified in the templates table will be stored here, which is actually the values of the sources and records details.

We have designed the database in a way that there will never be a “Null” value or a blank stored in any table. C. Data types management One of the main features in PHB is the capability to accept all incoming data from multiple sources with different structures. This is possible with the help of the “Template”, “Categories” and “Items” tables. Any structure will be stored in the templates table, any new category/type will be stored in the categories table, and any new item/label will be stored into items table.

III. EXPERIMENT: READING PHB DATABASE For our experiment, we designed a framework that understands PHB and creates an XML file as an output. The framework understands database content integrity as explained in the ER diagram and depending on the type of the request it can query the data and creates an XML file containing integrated and viewable data. For testing the performance we queried the data and arranged it based on source, so every source will have related data (such as sub-sources and records) grouped within a block. We used 2 medical firms’ data structures, Portable Health Clinic (PHC) [3] and Bangladesh Institute of Research and rehabilitation in Diabetes, Endocrine and Metabolic disorders (BIRDEM) [4]. We filled them with samples of data and used them in different cases (same doctor visit, multiple prescribed drugs and less checkup details). We briefly explain the framework working steps with the queries used: A. Views We have pre-defined 2 views to shorten the queries: •

“uvw_SourceWithCategoryName”: With this view we can query sources with their category name, SELECT SourceId, StartRow, EndRow, CategoryId, TemplateId, ParentId, CreationDate, Categories.Name AS SourceCategoryName FROM Categories INNER JOIN Sources ON Categories.CategoryId = Sources.CategoryId

•

“uvw_recordsWsources”: With this view we can query records with their sources details and category of each,

The templates table will link all related items together with their order of stored values in the “Values” table.

SELECT r.RecordId, r.CategoryId, Categories.Name AS recordCategory, r.TemplateId, r.StartRow, r.EndRow, r.ParentId, r.ImportDate, s.SourceId, s.StartRow AS sourceStartRow, s.EndRow AS sourceEndRow, s.TemplateId AS sourceTemplateId, s.CategoryId AS sourceCategoryId,Categories_1.Name AS sourceCategoryName, s.ParentId AS sourceParentId, s.CreationDate AS sourceCreationDate FROM Sources AS s INNER JOIN RecordSourceMap ON s.SourceId= RecordSourceMap.SourceId INNER JOIN Records AS r ON RecordSourceMap.RecordId = r.RecordId INNER JOIN Categories ON r.CategoryId = Categories.CategoryId INNER JOIN Categories AS Categories_1 ON s.CategoryId = Categories_1.CategoryId B. Queries These are all needed by the framework to generate an XML file: •

SELECT ItemId, Name FROM Items

•

SELECT SourceId, StartRow, EndRow, CategoryId, TemplateId,ParentId,CreationDate,SourceCategoryNa me FROM uvw_SourceWithCategoryName WHERE ParentId=0 AND SourceId NOT IN (SELECT SourceId FROM RecordSourceMap)

•

•

•

•

SELECT SourceId, StartRow, EndRow, CategoryId, TemplateId,ParentId,CreationDate,SourceCategoryNa me FROM uvw_SourceWithCategoryName WHERE ParentId=@ParentId SELECT RecordId, CategoryId, recordCategory, TemplateId, StartRow, EndRow, ParentId, ImportDate, SourceId, sourceStartRow, sourceEndRow, sourceTemplateId, sourceCategoryId, sourceCategoryName, sourceParentId, sourceCreationDate FROM uvw_recordsWsources WHERE SourceId=@SourceId AND ParentId=@ParentId SELECT CategoryId, recordCategory FROM uvw_recordsWsources WHERE ParentId=@ParentId GROUP BY CategoryId, recordCategory SELECT RecordId, CategoryId, recordCategory, TemplateId, StartRow, EndRow, ParentId, ImportDate, SourceId, sourceStartRow, sourceEndRow, sourceTemplateId, sourceCategoryId, sourceCategoryName, sourceParentId, sourceCreationDate FROM uvw_recordsWsources WHERE CategoryId=@CategoryId AND ParentId=@ParentId

•

SELECT TemplateId, ItemId, itemOrder FROM Templates WHERE TemplateId=@TemplateId ORDER BY itemOrder

•

SELECT RowNumber, Value FROM Values WHERE RowNumber >= @StartRow AND RowNumber