帮助在 SQL/NoSQL 混合（Sql 服务器/RavenDB）中对 EAV 进行建模

发布于 2024-10-06 12:04:20 字数 1352 浏览 3 评论 0原文

我正在设计一个健康 SaaS 应用程序，希望在初始建模方面获得一些帮助。我从这个线程开始确认我应该使用EAV - 答案由于临床数据稀疏，答案是肯定的。然后我开始考虑是否可以使用 NoSQL 选项，而不是尝试将其融入 SQL。看来两者结合起来效果最好。我会尽力解释要求和我的想法，并希望得到任何反馈。我正在使用.net。

要求在最高级别，我们有一个“病人”。对于需要医疗帮助的患者来说，可能会发生一些事情，我们称之为“事件”。对于每个“事件”，可以多次见到“患者”，称为“访问”。所有临床数据（测试/历史/等）均按“访问”存储。因此，我们有：

患者 1 - ∞ 事件 1 - ∞ 就诊 1 - 1 临床数据（许多潜在的键/值对）

解决方案（反馈会很好）

SQL 表

Patient
- PatientID
- other patient info

Incident
- IncidentID
- PatientID
- Other incident info

Visit
- VisitID
- IncidentID
- Datetime

NoSQL DocumentDB（可能是 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)
 },

}

这就是我到目前为止所拥有的。除了一般意见（欢迎）之外，我想知道是否有人认为我应该将每位患者的所有事件和就诊放入一份文档中，而不是每次就诊一份文档（这就是上面应该的内容）。我相信文档可能会变得“太大”（不知道太大在基于文档的数据库中意味着什么），并且视图几乎总是基于访问 - 尽管我们也需要显示跨访问的趋势报告。

提前致谢！！

麦克风

原文

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 if 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

分享到QQ

分享到微博