数据库设计，使一个表指向几个和某种递归指针 [英] database design, make one table to point several and some kind of recursive pointers
问题描述
我有一些医疗信息，我想存储在一个关系数据库，但不知道是热门模型最好的方法。
这里的问题是我有几个字段分割在其他字段中，同时细分为其他字段....
例如我有这个sql代码：
CREATE TABLE Distribution_patterns（
id_Distribution_pattern INTEGER NOT NULL PRIMARY KEY，
LEFT_SIDE_VIEW VARCHAR（40）NOT NULL，
RIGHT_SIDE_VIEW VARCHAR （40）NOT NULL，
dorsal_view VARCHAR（10）NOT NULL，
ventral_ view VARCHAR（10）NOT NULL，
CONSTRAINT uc_Info_d_p UNIQUE（id_Distribution_pattern）
）;
CREATE TABLE lymph_nodes（
id_lymph_nodes INTEGER NOT NULL PRIMARY KEY，
MANDIBULAR VARCHAR（40）NOT NULL，
肩胛骨VARCHAR（40）NOT NULL ，
INGUINAL VARCHAR（10）NOT NULL，
popliteal VARCHAR（10）NOT NULL，
CONSTRAINT uc_Info_l_n UNIQUE（id_lymph_nodes）
）;
CREATE TABLE评估（
ID_evaluation INTEGER NOT NULL PRIMARY KEY，
Distribution_pattern INTEGER，
lymph_node INTEGER，
肌肉骨骼系统INTEGER，
FOREIGN KEY （Distribution_pattern）引用Distribution_patterns（id_Distribution_pattern），
FOREIGN KEY（lymph_nodes）引用lymph_nodes（id_lymph_node），
FOREIGN KEY（Musculoskeletal_system）引用Musculoskeletal_system_parts（id_Musculoskeletal_system），
CONSTRAINT uc_Info_evaluation UNIQUE（ID_evaluation）
）;
- 分配模式
- 左侧视图
- 右侧视图
- 背侧视图
- 腹侧视图
- 淋巴结
- MANDIBULAR
- 肩胛骨
- INGUINAL
- popliteal
- 肌肉骨骼系统
- 步态评估
- 评估价值
- PALPATION
- TIP thoracic
- 肘和前臂
- CARPO AND FINGERS
- TIP pelvic
- PELVIS
- KNEE
- HOCK
- 具体测试
- CAJON TEST
- Ortolani TEST
- 其他
- TIP thoracic
当然这不是整个数据，因为有200个字段和14个类别.....
对于具有更多细分的肌肉骨骼系统，这种做法是否正确？
是否有其他方式来建模这种情况，或者我需要有几个表，其中ID是指向其他表的指针，其中有一个指向其他表的指针等等。
如果我想提供一个查询，给出一个巨大的表与所有数据医疗评估什么是最好的方法，几个加入ON ID？
这是一个潜在的大型数据库模式，所以我将专注于肌肉骨骼系统。在我进入之前，我应该提到数据库规范化的重要性。这一点很重要！有很多原因主要是通过设计一个好的，规范化的数据库，现在您可以在确保数据的完整性和可靠性的同时，将自己的问题保存下来。始终保持头脑这个数据库将如何增长，如果我需要添加更多的系统或视图或分发模式怎么办等等。你自己，一个你的雇主，赞成和阅读。简要概述可以在这里阅读： http://databases.about.com/od/特定产品/ a / normalization.htm
到数据库设计：
你想保留冗余列和列，这些列是从aa表中的其他列的子集到其自身的。例如，看看你的淋巴结表。如果从现在开始一年，你会意识到需要添加另一个淋巴结？还是删除一个？或者如果你只是从客户端看一个节点呢？而不是用你的方式，将桌子绑定到一个较大的对象（我不是医生，所以我不知道什么是安慰者）像病人或淋巴系统。或两者。例如：
患者有许多系统（患者表和系统表之间的一对多关系）
淋巴系统有许多器官（一对多关系淋巴系统和器官）
系统或器官有许多测试（系统和测试之间有一对多关系）。
示例： p>
TablePatient
PatientId（int PK） p>
PatientFName（string）
PatientLName（string）
此表格应仅具有与单个患者有关的数据
TableSystems
SystemId（int PK）
SystemName（String）
SystemDescription（string）
此表应该只有专门针对所有系统的列。系统可能是淋巴系统，呼吸系统，排泄系统等。
TablePatient_TableSystems
< blockquote>
Patient_System_ID（int PK）
PatientID（int FK）
SystemID （int FK）
你不可能有很多关系。这个表解决了。如果没有这个，您需要在每个患者/系统的每个表格中保留冗余记录。
TableOrgans
OrganID（int PK）
OrganName（string）
OrganDesc（string）
TableOrgan_TableSystem
Organ_SystemID PK）
OrganID（int FK）
SystemID（int FK）
为系统和器官解决许多问题
现在进行测试。测试具体到器官还是系统？或两者？这个例子将说明
TableTest
TestID
TestName
TestDesc
TestCost
Tabel_Test_Stytem
TestSytemID（int PK）
TestID（int FK）
SystemID（int FK）
Tabel_Test_Organ
TestSytemID（int PK）
TestID（int FK）OrganId（int FK）
这里有很多，所以我认为这是一个很好的地方停下来。阅读数据规范化，当您有任何问题时，请发回到这里（或发信息）。
I have some medical information I would like to store in a relational database but do not know hot to model it the best way.
The issue here is I have several fields that subdivide in other fields that at the same time subdivide into other fields....
For instance I have this sql code:
CREATE TABLE Distribution_patterns(
id_Distribution_pattern INTEGER NOT NULL PRIMARY KEY,
LEFT_SIDE_VIEW VARCHAR(40) NOT NULL,
RIGHT_SIDE_VIEW VARCHAR(40) NOT NULL,
dorsal_view VARCHAR(10 ) NOT NULL,
ventral_ view VARCHAR(10 ) NOT NULL,
CONSTRAINT uc_Info_d_p UNIQUE (id_Distribution_pattern)
);
CREATE TABLE lymph_nodes (
id_lymph_nodes INTEGER NOT NULL PRIMARY KEY,
MANDIBULAR VARCHAR(40) NOT NULL,
scapular VARCHAR(40) NOT NULL,
INGUINAL VARCHAR(10 ) NOT NULL,
popliteal VARCHAR(10 ) NOT NULL,
CONSTRAINT uc_Info_l_n UNIQUE (id_lymph_nodes)
);
CREATE TABLE evaluation(
ID_evaluation INTEGER NOT NULL PRIMARY KEY,
Distribution_pattern INTEGER ,
lymph_node INTEGER ,
Musculoskeletal_system INTEGER ,
FOREIGN KEY (Distribution_pattern) references Distribution_patterns (id_Distribution_pattern),
FOREIGN KEY (lymph_nodes) references lymph_nodes (id_lymph_node),
FOREIGN KEY (Musculoskeletal_system) references Musculoskeletal_system_parts (id_Musculoskeletal_system),
CONSTRAINT uc_Info_evaluation UNIQUE (ID_evaluation)
);
- Distribution pattern
- LEFT SIDE VIEW
- RIGHT SIDE VIEW
- dorsal view
- Ventral view
- lymph nodes
- MANDIBULAR
- scapular
- INGUINAL
- popliteal
- Musculoskeletal system
- Gait assessment
- VALUATION OF TROT
- PALPATION
- TIP thoracic
- Elbow and forearm
- CARPO AND FINGERS
- TIP pelvic
- PELVIS
- KNEE
- HOCK
- specific tests
- CAJON TEST
- Ortolani TEST
- OTHER
- TIP thoracic
Of course this is not the whole data as there are like 200 fields and 14 categories.....
Is it correct to do this approach for "Musculoskeletal system" that has more subdivisions?
Is there other way to model this kind of situation, or I need to have several tables where an ID is the pointer to other table which has a pointer to other table and so on....
If I would like to make a query that gives a huge table with all data for "medical evaluation" what would be the best approach, several joins ON id?
This is a potentially large database schema, so I will just focus on Musculoskeletal system. Before I get into that, though, I should mention the importance of database normalization. It's important! And for many reasons. Chiefly, by designing a good, normalized, database now you save yourself problems down the road while ensuring the integrity and reliability of your data. Always keep in the back of your mind "How will this database grow", "What if i need to add more systems or views or distribution patterns", etc. Do yourself, an your employer, a favor and read up on in. A brief overview can be read here: http://databases.about.com/od/specificproducts/a/normalization.htm
To the database design:
You want to keep redundant column and columns that are subsets of other columns out of a a table and into its own. For instance, look at your Lymph Node table. What if a year from now you realize you need to add another lymph node? Or remove one? Or what if you are only looking at one node from a client? Instead of doing it your way, tie the table to a larger object (Im not a doctor so Im not sure what the anser is) like Patient or Lymph Systems. Or both. Example:
A patients has many systems (one to many relationship between patient table and systems table) A lymph system has many organs(one to many relationship between lymp system and organs) A system, or organ, has many test (a one to many relation ship between systems and tests).
Example:
TablePatient
PatientId (int PK)
PatientFName(string)
PatientLName(string)
This table should have only data pertaining to an individual patient
TableSystems
SystemId(int PK)
SystemName(String)
SystemDescription(string)
This table should have only columns specific to all systems. Systems might be lymph systems, respiratory systems, excretory systems, etc.
TablePatient_TableSystems
Patient_System_ID(int PK)
PatientID(int FK)
SystemID(int FK)
You cant have many to many relationships. This table resolves that. If you didnt have this, you would need to keep redundant records in each table for each patient/system
TableOrgans
OrganID(int PK)
OrganName(string)
OrganDesc(string)
TableOrgan_TableSystem
Organ_SystemID(int PK)
OrganID(int FK)
SystemID(int FK)
Resolves the many to many for systems and organs
Now for tests. Are test specific to organs or systems? Or both? This example will say both
TableTest
TestID
TestName
TestDesc
TestCost
Tabel_Test_Stytem
TestSytemID(int PK)
TestID(int FK)
SystemID(int FK)
Tabel_Test_Organ
TestSytemID(int PK)
TestID(int FK) OrganId(int FK)
There's a lot here, so I think this is a good place to stop. Read through data normalization and when you have questions, post back here (or message me).
这篇关于数据库设计，使一个表指向几个和某种递归指针的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
