database design, make one table to point several and some kind of recursive pointers

Question

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

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?

Answer 1

You are talking about a hierarchical structure. If I were asked to do it, I will en up with two table: a data and a reference.

Evaluation table

• EvaluationId
• PartId
• EvaluationText

Reference table

• PartId
• GroupId
• PartDesc

The reference table with contain these:

PartId  GroupId PartDesc
0   null    Evaluation
1   0   Distribution pattern
2   1   LEFT SIDE VIEW
3   1   RIGHT SIDE VIEW
4   1   dorsal view
5   1   Ventral view
6   0   lymph nodes
7   6   MANDIBULAR
8   6   scapular
9   6   INGUINAL
10  6   popliteal
11  0   Musculoskeletal system
12  11  Gait assessment
13  11  VALUATION OF TROT
14  11  PALPATION
15  14  TIP thoracic
16  15  Elbow and forearm
17  15  CARPO AND FINGERS
18  14  TIP pelvic
19  18  PELVIS
20  18  KNEE
21  18  HOCK
22  11  specific tests
23  22  CAJON TEST
24  22  Ortolani TEST
25  22  OTHER

Now that we have a neat table to use. How are we going to use it? Well, recursive CTE, of course. You didn't mention the DB Engine, but almost every major DBMS can do it except for MySQL. You can either look up this cool feature yourself or let us know your DB Engine and we can continue this discussion.

Answer 2

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).