Help with modeling EAV in SQL/NoSQL mix (Sql server/RavenDB)

I'm designing a health SaaS app and would appreciate some help with the initial modeling. I started with this thread to confirm that I should be using EAV at all - the answer was yes due to the sparsity of clinical data. I then started looking at possibly using a NoSQL option instead of trying to fit it into SQL. It seems a combination of the two would work best. I'll try to explain the requirement and my idea's and would love any feedback. I'm using .net.

Requirement At the highest level, we have a 'Patient'. For a patient to need some medical help something would have happened, let's call that an "Incident". For each "Incident" a "Patient" can be seen multiple times, called "Visits". All clinical data (tests/history/etc) is stored per "Visit". So we have:

Patient 1 - ∞ Incidents 1 - ∞ Visits 1 - 1 Clinical data (many potential key/value pairs)

Solution (feedback would be great)

SQL Tables

Patient
- PatientID
- other patient info

Incident
- IncidentID
- PatientID
- Other incident info

Visit
- VisitID
- IncidentID
- Datetime

NoSQL DocumentDB (probably RavenDB)

{ // Visit document - id: visits/12345
 "Patient": {
   "PatientId": "patients/54321",
   "Name": "John Smith"
 },
 "Incident": {
   "IncidentId": "incidents/55555",
   "Name": "Cardiac Arrest"
 },
 "VisitData": {
   "BP": "110/70",
   "Hypertension": "True"
   "Cardiac Disease": "Angina"
   "Stroke": "False"
   .... (could be tens or hundreds of key/value pairs)
 },

}

That's what I have so far. Aside from general opinions (all welcome), I was wondering i开发者_运维知识库f anyone thinks I should put all Incidents and Visits for each patient in ONE document as opposed to having one document per visit (which is what the above is supposed to be). I believe the documents could get 'too big' (without any idea of what too big means in a document based DB) and also almost always the views are based on a visit - though we'd need to show trending reports across visits as well.

Thanks in advance!!

Mike

---

this looks appropriate according to your stated requirements.

I think there is probably something else going on, which is maybe 'Condition' that is not necessarily part of any patient Incident. For instance a person with Hypertension may simply have that condition when they present for a broken finger.

Also, Incident may be hard to define - is it a single point in time event or is it a progressive duration of deterioration? Maybe this means Incident is really just a marker on a visit, or maybe you have a visit to vist association table that lets you declare that a visit is a followup to another visit, building a hierarchy or netwrok of the care a patient received.

just a couple thoughts off the top.. hth

edit - afterthought: I would for sure recommend a SQL db with properly normalized tables...

---

a mix of databases may work best. Existing appraches use EAV but the problem is with nested facts - alert about drug interaction could be master event in a SQL table

but then how severe alert, to whom sent, which 2 drugs - those details can go to a document-based noSQL db.