What's the best way to design an OI database for this scenario:

A doctor's office has patients, who may each make multiple visits over time; at each visit they might have multiple diagnoses; each diagnosis can be treated with multiple medications.

Some medications treat multiple diagnoses. Virtually every diagnosis can be found in multiple patients.

A medication might be prescribed to treat a specific diagnosis, or it might be prescribed without a particular diagnosis in mind, thus linking it with the visit.

I'm experienced in SQL Server, but I'm unsure about the best way to design with OI. I'm really most interested in sound database design principles, how foreign keys work, etc. Thanks!

- this replicates something we did for an Ocular Oncologist in the UK. Patients have multiple episodes which can yield multiple diagnoses which in turn can yield multiple managements. This can be implemented using multi part keys OR sequential counters and referential integrity constraints. The final design will ultimately be driven by the business imperatives.

The Sprezzatura Group

World leaders in all things RevSoft

I find that designing the forms first to try and capture what you want the system to do, is a good place to start. That way you get the ideas of the fields and tables required.

The biggest concept to get right is the multi-valued fields as opposed to added tables in SQL. I would suggest that it is not a good idea to go to Sub-Values, and just use MV only, otherwise it can get messy.

The good thing about MV, is that you generally only need one or two reads to get the info you need rather than a heap of indexes and linked tables.

With your many levels, it will need to map the real world needs of the user, but with MV, you can adapt as you go, making it easy to change later.

Colin

Hi John

Good Question.

I came from an SQL background, so tend to normalize data whenever I am storing real attributes. However, I like to store relationships as mv lists of keys. I use have a calculated field in the parent table whose answer is the collection of keys comprising the desired property, have a family of calculated columns which use that collection to read the related records. In oi32 I like to use relational indexes kept in their own table, e.g if I have a 'customers' table I will have a customers_rel table too. With that model relational updates rarely get locked out, the system is stable and responsive, it is easy to use parent and child forms without locking / integrity issues.

This style is my own, not necessarily best practice for the community.

- Bob

John,

Great questions.

I would suggest getting a book on Multi-value type databases or read some of the white papers here to understand the difference between a "flat" file and a Multi-valued database.

There are many things to consider when designing a "good" database in this environment. Just like programming, I believe it is better to take a little extra time up front and do it right that having to take a lot of time later to make it right.

I've worked in several medical applications and have found that sub-values are worth the effort although use them sparingly.

A good way to think about setting up databases in this environment is to imagine how the data would "list" in straight report.

Since there is no normalization the mulit-values and sub-values give you a nice sideways (as compared to the editor) view of how the database looks. If it looks clean and simple it probably is.

Also, the use of multi-part keys as mentioned is a great way to organize as is using codes (which is rampant in med.) and code tables that you can simply translate (xlate in OI - join? in SQL) to for static information as well as dynamic. In addition, you can use multiple codes from single valued fields to create multi-part keys to translate to multivalued data.

BC

This may help you to get started.

Tables to create

OFFICES

PATIENTS

DIAGNOSIS

MEDICATIONS

APPOINTMENTS

1. Offices, small code table in case patients can be seen at different locations. Have a seq# for the key, then name, address, phone, doctors, contact etc.

2. Patients, this would where all general patient information would be kept and also a multivalued field to store APPOINTMENT KEYs.

3. Appointments, this could be a seq# key or a multi-part key (patient# * date * time). Within this record a diagnosis field would be multivalued that could point to the diagnosis table with a multi-part key the same as the appointment key with the addition of a diag code where you can store multivalued medication information.

(or this is where you could store multivalued diagnosis information and then sub-valued medication information.

4. Diagnosis could be used as noted in #3 or as a code table.

5. Medication could be a code table. (ie. Key=A1 =Asprin)

There are a ton of ways to do it. I was told early on to find out what information you want to get out of the database and if you can

get the information out WITHOUT writing a program, i.e. in a report fashion then it's worthy. An example of that just using one table would with multi and sub-values would be…

LIST APPOINTMENTS OFFICE PATIENT DATE TIME DIAGNOSIS MEDICATION

The code tables mentioned are not necessarily necessary but can make it easier on the end-user.

HTH

John,

I would strongly suggest that you use your sql knowledge to prototype the primary schema.

Once you have a good schema you can then de-normalize as needed for any speed you need.

John,

Regarding the database design, I think you might have gotten some good leads.

With a MV architecture it's easy to implement what you want. Here an example of our implementation. Note that the Prescribe Meds are a function of the 1) provider and 2)treatment (or diagnosis). In our example, the popup shows the Meds for someone getting ready for a Tonsillectomy.

I think you will like working OI.

Testing Vista,

Ray Chan

View this thread on the Works forum...