How do I handle entitys with entries of different sets of attributes in a database design?

Question

0.00/5 (No votes)

See more:

Hi!

I have an amateur project to design a database for medical purposes. I aim for a flash interface to interact with a SQL database. I'm still at design phase.

Suppose I have an entity (table/list) of patients and another of symptoms and I represent the assignment of a symptom to a patient (a many-to-many relationship) through an associative entity of 'Patient_Symptom'. Now, such assignments share some attributes across different symptoms (starting time, duration, etc) but not some others: A symptom of pain will have a location, but nausea won't, for instance. And this situation seems to come back alot.

1) Is the database context an appropriate one for this kind of storage of structured data? If not, what should I aim for?

2) If appropriate, how do I handle situations of entity entries with different sets of attributes assigned to them?

Thanks for help!

ps. Has read Shivprasad Koiralas great article on database design (11 important database designing rules which I follow), but couldn't figure how to ask him directly.

Posted 8-Nov-14 22:40pm

Member 11218475

Add a Solution

Comments

Mehdi Gholam 9-Nov-14 4:46am

Use the comments section at the end of the article to ask questions of authors.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Maxxx_ · Answer 1 · 2014-11-10T01:27:00

It is a vast and complex field. Governments have ploughed millions into the development of software that tries to do this - and mostly failed!

The medical industry have a number of classification/encoding systems many of which aren't cross-compatible.

As this is an amateur project, I'd keep it very simple;

define all of the attributes possible for any symptom you are dealing with, and store them all on a single table - leaving null any that are not applicable.

If you do want to be more complex, then I'd suggest:

A table of symptoms
A table of attributes
A cross-reference table many-many linking which attributes are applicable to which symptoms

now use a Patient-Symptom-Attribute table to collect the data

this table holds the value for an attribute for that patient with that symptom.

If existing coding systems are anything to go by, you will have a lot of symptoms.

see ICD10[^]
for example.