Molecular Clinical Database — A New Approach
Abstract and Context
I recently took a graduate course on database design. Over the semester, each student was assigned the task of designing a database of their choosing. Since I currently work as a licensed clinical molecular technologist, I decided to create a clinical molecular database.
My goal was to streamline and simplify the ordering and reporting system for a clinical molecular laboratory that focuses exclusively on molecular testing. To do this, my database was designed around a “one-order-one-tuple system,” meaning that each test order placed by a client has its own unique tuple associated with a particular assay and target pathogen.
I hope that by sharing this idea, it can be used to create better clinical databases, and hopefully help to make the world a better place. I am also hoping to display this skillset for potential employers.
Class assignments are often cast in a fictional context. In this case, my project was to create a database for a pretend company. The “Aloha” company mentioned in this document is a work of fiction, along with the names and places mentioned — many of which are obvious pop culture references. While the fictional backstory/narrative uses terms like “we” and “our team,” this is merely a fictional device used for the context of this independent project. Dr. Raied Salman was my professor on this project, however, any other names mentioned are mere works of fiction, and the work I present here is my own.
I hope you enjoy reading this project as much as I did writing it!
Aloha Clinical Diagnostics Molecular Database Design
Gregory S Muhs
University of Maryland Global Campus
Biotechnology Master’s Program, Bioinformatics Specialization
DBST 651: Relational Database Systems
Dr. Raied Salman
May 10, 2021
Introduction
The purpose of this project is to create a database for Aloha Clinical Diagnostics, a new clinical testing company that focuses exclusively on molecular testing. While most clinical laboratories have a broad range of tests that are offered, our business model centers around focusing on one single area. It is our view that this niche focus will allow our company to advance in this rapidly growing sub-field, without the complexity that comes from an over-broad area of focus. It is also our view that a database designed exclusively for a molecular testing facility can be streamlined in ways that broad clinical databases cannot.
Overview
The Aloha database is designed around the one-order-one-tuple system, meaning that each test order placed by a client will have its own unique tuple associated with a particular assay and target pathogen. As explained in this document, this is to simplify the process of ordering, testing and reporting results. Our system is designed to accommodate any number of assays and platforms, and is designed to maximize flexibility. For example, a client can order a test for SARS-CoV-2 on a particular samples, and this test can be fulfilled on any platform with a compatible assay. At the same time, the client can add any number of test orders to the same sample, given appropriate sample type and sufficient volume.
The Aloha database will interface with other software and hardware on both the client and laboratory side, along with billing and other business-related departments. This is so that the clients will be able to place orders and view their results, and so that the LIS will be able to properly interface with the database and laboratory systems.
Literature Review
A review of the literature on clinical databases reveals a number of difficulties and challenges in building any type of clinical database. Medical information can be very broad, and can be hard to define in advance. Also, different institutions and different areas of the healthcare industry may have very different needs when it some to managing information within a database. These challenges support the decision to create a database that focuses purely on clinical molecular testing.
In 1990 Friedman et al. wrote a paper on the design of a clinical database that would be all-encompassing in terms of patient clinical data, and would be patient-centric in its focus (Friedman et al.). This is very different from the laboratory-centric design of the Aloha database that places the test order at the center of the database design. In their paper, Friedman et al. argued for the use of a relational database as the best model for general clinical purposes. The largest drawback that the paper focused on was the fact that medical information is not always predictable or easy to standardize, unlike some other types of information. Focusing on clinical testing in-particular, the authors explained that different types of tests produce different types of results. Some examples they focused on were the differences between a microbiology culture, a white blood cell count, and a medical exam, all of which produce very different types of results. Fields such as data and time are generally consistent across any type of clinical test or examination, but fields such as results can vary widely.
In 2014 a paper was published by Miyata et al. that addressed many of the challenges of creating a comprehensive clinical database for Japan’s national healthcare system (Miyata et al., 2014). This paper focused primarily on the logistics and ethics of this database, rather than on the informatics challenges. In a similar line of reasoning to the aforementioned Freidman et al. paper, Miyata et al. explained that disparate medical facilities don’t always operate the same way, and might define basic terms such as “mortality rate” by different definitions. On the other hand, the authors argued that a centralized database would help to reduce medical informatics costs to institutions.
As anyone familiar with the medical field is well aware, a large part of the cost of healthcare is from the “paperwork” and the complexity of maintaining an organized records system. Having a centralized national system has a lot of potential benefits in terms of both cost and quality. Concerns about privacy and abuse of electronic records systems are clearly an issue for many people and must be addressed.
Miyata et al. also expressed the opinion that providing the best service to patients is more important than cost considerations. “Reducing medical costs is often a central policy issue in healthcare. However, the primary aim of healthcare should be to provide the best service to patients, rather than to curb medical costs[…]” (Miyata et al., 2014).
While quality is certainly an important consideration, economists often point out that everything in life is a trade-off. The healthcare industry is no exception: individuals may choose to slightly compromise on quality if the price is significantly less. Depending on circumstances, even a moderate difference in price may present a significant burden, and may make the difference between a patient being able to afford treatment or not, or having to choose between medicine and other essential goods and services. It is for this reason that both quality and affordability need to be considered in any discussion of medicine and economics.
In 2021 a paper was published by Samra et al. that explained the differences in terms of database needs for healthcare providers vs clinical researchers. As the authors stated, “Current clinical databases are primarily based on data acquisition for healthcare intentions. However, these healthcare databases lack the data analysis capability for clinical researchers.” (Samra et al., 2021) The overall message of the article was that the process of extracting data from a healthcare-oriented clinical database and integrating it into a research-oriented clinical database presents a number of technical challenges. The authors further went on to explain their proposed solutions to these challenges.
There are broad challenges involved in creating a clinical database, or even a clinical testing database. These challenges support the decision to create a simplified clinical molecular database that focuses exclusively on molecular testing, rather than on all areas of healthcare or even all areas of clinical testing.
Assumptions
As addressed in the definition guide, several assumptions were made while developing this database. The first assumption was that Aloha diagnostics will exclusively conduct clinical molecular testing. It was also assumed that Aloha’s facilities will reflect a typical facility for a clinical reference laboratory.
The most significant design assumption is that each test will require only two fields for reporting purposes. As mentioned, this is being done for efficiency purposes. The needs in this area may change in the future. Clinical testing often requires repeating the same test on a sample multiple times. One way to resolve this would be to reorder the test on the same sample to create a suborder. Alternatively, new columns could be added in the future.
(See also “Assumptions and Special Considerations.”)
Design Decisions
Several database design platforms were considered for this project, however the decision was ultimately made to create the database using Oracle SQL Developer (Version 19.1.0.094). This decision was made due to accessibility and standardization considerations. Oracle SQL Developer is a very common platform for building databases, comes with widespread online reference tools for coding purposes, and has a long history in terms of business use and support.
Key Factors Influencing Design
During the design of this database, both the IT and Clinical staff drew upon prior experience with clinical testing databases. It was decided that this database would exclusively focus on molecular testing, and that the database design would center around the “test order” rather than around the patient or the sample. While patient safety is always the highest priority in any clinical setting, our team came to the opinion that a database centered around the test order would allow for greater efficiency and simplicity in terms of the database structure.
Functional Design Decisions
As mentioned in the original Statement of Work, the design for this database was limited to the database only, including the data storage and arrangement. Any Twenty-First Century database will have to interact with an LIS (“Laboratory information system”) along with specialized software for the laboratory staff and the clients’ clinical staff who collect the original samples and place the orders. Two “views” were created for the laboratory staff for inventory purposes, however it is expected that further development will be required on both the laboratory and client-side ends as the system goes into beta testing.
It is the professional opinion of this team that the database is easily compatible with a system that allows clients to see a filtered list of patients, samples, and test orders as it pertains to the appropriate, authorized information, while still maintaining the full privacy of each of our clients and patients.
Database Management System Decisions
As previously mentioned the decision was made to create the database using Oracle SQL Developer (Version 19.1.0.094). A major part of this decision was made due to the fact that this platform is widespread and standard, and due to the suggestion that this would be compatible with other IT infrastructure, including client and laboratory tablets, computers, and LIS systems.
Security and Privacy Design Decisions
The higher-level security design will have to be implemented partly on the database level, and partly on the user interface level. The laboratory staff will have to have full access to all of the information in the database. By using the Client ID as a key, the clients should be able to see all of the relevant information that a client has access to, including results that have been “released” to the client. Results that have not been “released” by the laboratory staff should be hidden from the client.
Performance and Maintenance Design Decisions
The Aloha clinical database will need to be able to serve multiple users simultaneously and accommodate periodic maintenance. When new tuples are ordered, they will be placed in a queue before being integrated into the database, this way each tuple is unique and key identifiers are not duplicated.
During maintenance, the database will be taken offline. Ideally this will be announced in advance and will be done at a time of low traffic. During that time, orders will not be able to be placed in the database. One avenue around this that is being considered is to provide labels with unique sample identifiers to clients, along with order forms, to minimize any delays in sample shipping and order placement.
Revised Statement of Work
Overview/Executive Summary
The Aloha Clinical Diagnostics Molecular Database was designed to encompass multiple platforms and clinical molecular assays in preparation for our “Go Live” date. This project is exclusively focused on the design of the database itself. A separate team is designing the user-interface software and LIS system, along with the relevant forms.
In light of Aloha Clinical Diagnostics’ commitment to forward-compatibility, all aspects of the database design, including SQL code, have been written in a clear and concise manner to accommodate future business needs and business growth. The process of adding new assays, and new platforms, along with other tuples is written clearly within the code, and the pattern will be easy to follow.
Objectives of Database Project
The Aloha database will start out small, but will be forward-compatible so that new assays can be added, and so that the system can be adapted to changing business requirements. Because Aloha Clinical Diagnostics is a startup company that focuses exclusively on clinical molecular testing, our database will start out with four assays designed to be ran on two platforms. The two main platforms that have been validated by our clinical team are the Panther by Hologic (Hologic, n.d.a) and the Cobas 8800 by Roche (Roche, n.d.a). The featured assays will be Combo 2 (gonorrhea and Chlamydia in combination) and the Trichomonas vaginalis assay on the Panther (Hologic, n.d.b; Hologic, n.d.c), along with the HBV and HIV assays on the Cobas 8800 (Roche, n.d.b; Roche, n.d.c). Additional assays and platforms have also been included in the database, and will be implemented after the “Go Live” date pending validation.
This database will allow the clients to order tests on individual patient samples by typing in the Sample ID, the patient’s name, and selecting the particular assay from a drop-down menu. A number of fields will be included to describe each tuple in the database. One tuple will be equivalent to one test order from one patient sample. This means that one Sample ID with four orders will take up four tuples in the database.
Project Scope
The scope of this project is limited exclusively to the database design. This includes the fields and tuples associated with the objectives, and the arrangement of information within the working database (including DDL and DML code), and considerations for both client and laboratory utilization of this tool.
As previously mentioned, several additional components are being designed to integrate with this database, however these are beyond this particular scope of work. The LIS (laboratory information system) is being designed by a separate team in collaboration with the database design team and will ultimately need to be able to transfer results from the platforms to the database. Likewise, user-friendly software will need to be designed for the medical teams on the client-side end for ordering tests, and on the reference lab side for reviewing and releasing patient results.
Database Goals, Expectations, and Deliverables
The goal of this project was to create a streamlined database, where other clinical databases have, in the experience of our team members, fallen short. The center of this strategy is our one-order-one-tuple system. Our goal was that by May 10, 2022 the full database would be completed. By May 8th, this goal was achieved in full.
The steps leading up to this May 10th goal are as follows. Step 1 was the completion of the initial SOW, and this was finalized on March 15th. Step 2 was the creation of an Entity Relationship Diagram (ERD) along with an accompanying definition document (March 29th). Step 3 was to create the database objects, including tables, views, and triggers, along with catalogs and dictionaries (April 12th). Step 4 was to create and insert test data to be sure that the databased was working correctly (May 10th).
The deliverable will be the basic database. This will include the aforementioned technical details, but as previously explained, will not include additional user software or LIS software.
Database Benefits
The primary intended benefit of this database is the one-order-one-tuple system. By having each tuple represent one test order, we believe we will be able to streamline the ordering, and reporting aspects of the database. Instead of having each tuple represent one sample with multiple fields for each test/assay, along with multiple fields for results corresponding to each potential assay, this system currently consists of two fields to report results. One is qualitative (i.e. positive, negative, invalid, or null) while the second field is quantitative (i.e. the measurement of viral load, as is relevant to some assays).
It was initially suggested that combination assays that do not require a quantitative result, such as Combo 2 for Panther, each field would represent the qualitative result for one or the other target. It was suggested that this would make the storage in the database more efficient, and would avoid needlessly empty fields. Yet during the database design, it was decided that an assay such as Combo 2 would represent two “orders” rather than one. Even though this is one assay with two targets, for reporting purposes, our design team decided that this would be the simpler and more organized option.
Project Hardware and Software Tools
The operating system for this database was designed on and designed for Windows systems. Ideally this database will be compatible with both a standard laptop and desktop, but also with a standard Windows tablet. With the approval of Dr. Raied Salman, the IT team, and the other leadership teams involved, this database was designed using Oracle SQL Developer (Version 19.1.0.094). ER-Assistant (Windows Version 2.10) was used to create the ERD associated with this project.
The Aloha database will be hosted using an Oracle Cloud service (Oracle, 2022). This hosting platform was selected based on both compatibility and security considerations. The Oracle Cloud service comes with strong security measures to help to protect both client and patient confidentiality, along with Aloha’s needs for business security. Oracle’s website also showed notable support options.
SQL Usage and Style Guide
Our guiding philosophy in programming is to seek clarity and simplicity. We are also seeking to design code that can be easily adapted to future needs, in line with our company’s overall guiding principles. The database will be designed using the appropriate SQL standards for Microsoft Access. Each section of code will contain sufficient commentary so that any competent programmer can easily make adjustments as needed. Primary keys will be designed to be simple and intuitive, such as Order ID, Assay ID, Sample ID, and Patient ID. Relevant data definition and manipulation language will be used to automatically update our tables when client order forms are submitted.
Conclusions and Summary of Project
The goal of this project was and is to create a database that streamlines ordering, processing, and reporting for our clients and our clinical lab staff respectively. In accord with our company’s principles, the team designing this database has been granted as much leeway as possible to create this tool, while keeping in mind the project goals and ethical requirements for patient safety and confidentiality. It is our hope and desire that this database will facilitate Aloha in creating higher standards for the molecular testing industry.
Requirements Definition Document
Entity and Attribute Descriptions
Clients
The client is the clinical organization or hospital that collects samples from patients and orders tests from Aloha Diagnostics. The client will be identified by the Client ID, an identifying alphanumeric sequence that is unique to each client. The client will provide a primary contact and a phone number for this contact for the purposes of discussing orders and test results. This primary contact may be a physician or other contact provided by the client, and may vary from client to client. The name of the organization or hospital, along with their primary location will also be included for business and contact purposes.
Entity Name: Clients
Entity Description: The clinical organization or hospital that collects samples from patients and orders tests from Aloha Diagnostics
Main attributes of Clients:
Attribute Name: Client ID
Attribute Description: (Primary Key) An identifying alphanumeric sequence that is unique to each client
Attribute Name: Phone
Attribute Description: The phone number used for contact purposes between the client and Aloha Diagnostics
Attribute Name: Primary Contact
Attribute Description: The individual or group of individuals designated to speak to Aloha Diagnostics on behalf of the client
Attribute Name: Client Name
Attribute Description: The name of the company or organization that acts as the client
Attribute Name: Client Address
Attribute Description: The primary location listed by the client for business and contact purposes
Patient
The patient is the individual who is receiving clinical testing, and will be identified by the Patient ID. The patient’s first name, last name, sex, date of birth, and Social Security Number will be included for identification purposes.
Entity Name: Patient
Entity Description: The individual who is receiving clinical testing
Main attributes of Patient:
Attribute Name: Patient ID
Attribute Description: (Primary Key) An identifying alphanumeric sequence that is unique to each patient
Attribute Name: First Name
Attribute Description: Patient’s first name (given name)
Attribute Name: Last Name
Attribute Description: Patient’s last name (family name)
Attribute Name: Sex
Attribute Description: Male or Female
Attribute Name: Date of Birth
Attribute Description: The date that the patient was born, including the month, day and year
Attribute Name: SSN
Attribute Description: The Patient’s Social Security Number or equivalent government-assigned identification number.
Attribute Name: Age
Attribute Description: The age of the patient, calculated from the Date of Birth and the current date. (This attribute is generated during the 20 SQL Queries.)
Samples
The sample is the biological component obtained from a patient, such as blood, urine, or swab. The sample type is defined as the content of the sample, including blood, serum, throat swab, cheek swab, urine, or any other sample category that can be obtained from patients for testing purposes. The patient’s first name, and last name, along with the Patient ID will be included for confirmation of the patient’s identity. The collection date will be recorded as the date that the sample was obtained from the patient.
Entity Name: Sample
Entity Description: The original biological sample collected from the patient
Main attributes of Sample:
Attribute Name: Sample ID
Attribute Description: (Primary Key) An identifying alphanumeric sequence that is unique to each Sample
Attribute Name: Sample Type
Attribute Description: They type of sample that is collected, such as blood, urine, or cheek swab
Attribute Name: Patient First Name
Attribute Description: First name of patient
Attribute Name: Patient Last Name
Attribute Description: Last name of patient
Attribute Name: Collection Date
Attribute Description: The date that the sample was collected from the patient
Test Order
For each test that a client orders on a sample, one Test Order ID will be generated. This follows the one-order-one-assay approach to the database design. The order will be identified by the Test Order ID. The Order Date represents the date that the order was placed by the client, and is generated automatically. This may be different from the collection date. The Priority Status represents the urgency for a short turnaround time for results, and those samples with a higher priority will take precedence over those with a lower priority. The Assay represents the type of test that the client orders on a sample.
Medical Contact may or may not be the same contact as provided in the Client attributes. This allows medical providers to provide direct contact information for primary caregivers that may differ from the Primary Contact given by the client. This means that a direct contact for a particular physician can be provided, rather than a generic contact for a large hospital.
Entity Name: Test Order
Entity Description: An order for a specific clinical test to be conducted on a specific clinical sample
Main attributes of Test Order:
Attribute Name: Test Order ID
Attribute Description: (Primary Key) An identifying alphanumeric sequence that is unique to each Test Order ID
Attribute Name: Order Date
Attribute Description: The date that the order was placed
Attribute Name: Priority Status
Attribute Description: The level of priority and urgency given to this test
Attribute Name: Order Status
Attribute Description: The statues of the order (i.e. “Pending,” “Complete,” “Cancelled”)
Attribute Name: Medical Contact
Attribute Description: The medical contact representing this particular order, if different from the client’s primary contact
Test Selection
The Test Selection is a bridge entity between Order and Assay. When a medical professional orders a clinical test, they can select the type of test that they want to order (i.e. Test Selection). This Test Selection can incorporate the assays from multiple platforms. See the section on relationships for more details.
Entity Name: Test Selection
Entity Description: A bridge entity within the database for organizational cardinality purposes. Each test selection represents one test that is to be performed on a particular sample, but takes into account the fact that some assays can be interchangeable.
Main attributes of Test Selection:
Attribute Name: Test Selection ID
Attribute Description: (Primary Key) An identifying alphanumeric sequence that is unique to each test selection
Attribute Name: Pathogen
Attribute Description: The pathogen (such as virus or microorganism) associated with the requested test.
Assay
Assay, in this context, refers to a scientific procedure performed on a sample to detect a certain pathogen. (For database design purposes, assay is being distinguished from test.) Each assay can detect one or more pathogen, such as a virus or bacterium. These assays can be classified as quantitative or qualitative (including semi-quantitative assays). The Turn Around Time represents the goal for how quickly an assay result should be reported after the lab receives the sample and the order. Sample Types here are defined in the same way as in the Sample entity, except that an assay can be applied to more than one sample type.
Entity Name: Assay
Entity Description: A scientific procedure performed on a sample to detect a certain pathogen
Main attributes of Assay:
Attribute Name: Assay ID
Attribute Description: (Primary Key) An identifying alphanumeric sequence that is unique to each Assay
Attribute Name: Pathogen
Attribute Description: The pathogen that the test is designed to detect
Attribute Name: Quant or Qual
Attribute Description: A field to describe whether this test is quantitative, qualitative, semi-quantitative, or any other relevant description
Attribute Name: TAT
Attribute Description: “Turn Around Time” How quickly an assay result should be reported after the lab receives the sample and the order
Attribute Name: Sample Types
Attribute Description: Acceptable sample types for this particular assay
Platform
The platform will be identified by the Platform ID as the primary key. The platform is the instrument type that performs a test. Attributes include the Name of the platform, such as Panther or Cobas 8800. The Manufacturer is the company that manufactures the platform, such as Roche or Hologic. The Method of Detection is the type of test that is performed on a platform, such as Q-RT PCR, or TMA. Performance Time is the amount of time from when a sample is loaded to when testing is complete on an instrument.
Entity Name: Platform
Entity Description: The technological system used to perform one or more assays
Main attributes of Platform:
Attribute Name: Platform ID
Attribute Description: (Primary Key) An identifying alphanumeric sequence that is unique to each Platform
Attribute Name: Platform Name
Attribute Description: The name of the platform, such as Panther or Cobas 8800
Attribute Name: Manufacturer
Attribute Description: The manufacturer that produces the platform, such as Hologic or Roche
Attribute Name: Performance Time
Attribute Description: The time it generally takes for a platform to produce a reportable result from the moment a sample is loaded onto the platform
Attribute Name: Method of Detection
Attribute Description: The underlying scientific principle behind the platform, such as PCR or TMA
Relationship and Cardinality Descriptions
Clients to Patient
Each client can have many patients, but each patient can have only one client. While it is true that a real-world human being can go to more than one client to have samples collected, for the record-keeping and reporting purposes, these records and results must be distinguished from one another. If Client A orders a test on a patient, and Client B also orders tests on the same patient, it is important to distinguish which client collected which samples and placed which orders.
Relationship: serves between Client and Patient
Cardinality: 1:M between Client and Patient
Business rule: a client can have zero to many patients; a patient is seen by one and
only one client
Patient to Sample
Each patient can have many samples, but each sample can only be from one patient. Each sample can only be taken from one patient.
Relationship: Collected from between Patient and Sample
Cardinality: 1:M between Patient and Sample
Business rule: a patient can have zero to many samples; a sample is taken from one and
only one patient
Samples to Order
Each sample can have many test orders, but each test order can only be from one sample. When an order for a test is placed it has to be associated with one particular sample.
Relationship: placed between Sample and Test_Order
Cardinality: 1:M between Sample and Test_Order
Business rule: a sample can have one to many test orders; a test order is placed for one and
only one sample
Order to Test Selection
Each test order can have only one test selection, but each test selection can be used by many test orders. This is because each test order generated will have only one test selection, which is associated with a particular pathogen. This is the basis of the one-order-one-assay approach.
Relationship: has between Test_Order and Test_Selection
Cardinality: M:1 between Test_Order and Test_Selection
Business rule: a test order can have one and only one test selection; a test selection can have zero to many test orders
Test Selection to Assay
Each test selection can have many assays, but each assay can have only one test selection. The reason for this is that there are equivalent (interchangeable) assays that can be performed on different platforms. For example, the Panther and the Cobas 8800 can each test for SARS-CoV-2, however the assay on each platform is not scientifically identical.
Relationship: has between Test Selection and Assay
Cardinality: 1:M between Test Selection and Assay
Business rule: a test selection has one to many assays; an assay corresponds to one and
only one test selection
Assay to Platform
Each assay can have only one platform, however each platform can have many assays. This basically means that each platform can have multiple assays that test for different pathogens, such as HIV or SARS-CoV-2. While two platforms may have equivalent assays between them, it is important to distinguish between equivalent assays on different platforms.
Relationship: has between Platform and Assay
Cardinality: 1:M between Platform and Assay
Business rule: a platform can have zero to many assays; an assay has one and
only one platform
Assumptions and Special Considerations
Several assumptions are being made in the approach to this project:
1. The first assumption is that Aloha diagnostics will exclusively conduct clinical molecular testing.
2. It is assumed that each instance of each entity will have a unique ID as the primary key.
3. It is assumed that identifying information must be included under both the Patient entity and the Sample entity for quality control purposes.
4. Another assumption is that each test will require only two fields for reporting purposes. As mentioned, this is being done for efficiency purposes. The needs in this area may change in the future.
5. Beyond this, it is assumed that Aloha’s facilities will reflect a typical facility for a clinical reference laboratory.
A special consideration was granted in terms of the database design. The guidelines call for six entities at most, and at least five attributes for each of these entities. The guidelines also ask that there be no many-to-many relationships. Because there would otherwise be a many-to-many relationship between Order and Assay, the entity Test Selection was created. This bridge entity also allowed the entity “Assay” to be more clearly and accurately defined. By using “Test Selection” to incorporate equivalent assays from different platforms, it allows the ERD to be more organized and to better reflect the real world. At this time, Test Selection is only meant to be an “umbrella” category to act as a bridge between equivalent assays, and does not require five attributes, although this may change in the future as the database develops.
Detailed Database Design
Entity Relationship Diagram
Here is a link to my LinkedIn post with the full MS Word document, including the DDL and DML scripts. If you would like a downloadable copy, please send me a message on LinkedIn.
