Dear All,

im given a hard copy of a form that is used by a hospital to gather information about its patients ! the form is divided into sections and each section has A LOT of YES/NO check boxes.


For example, one of the section is the History section that has more than 20 unrelated yes/no fields:

smoker ( yes or no)

diabetes (yes or no )

Chronic Lung (yes or no )
.
.
.
.
.

Another section is the "stress test data" section that has questions like the following:

Standard test (yes/no) , if yes , what are the results ( negative or positive),
if positive ( what is the extent (low or high, or intermediate )

...

i am asked to design a database, show the relation tables and the relation between them ! this is looks NOTHING like the "student-class database" or the "CD company database" i came across ! this is just..i dont know..DYNAMIC :s:s

i have NO IDEA how to even START designing this database or what are the things that i should read or look into or pratice in order to design a database for such a form !!

PLEASE HELP !!

Start by listing the various entities that you will track, and then the attributes those entities will have.
If you'd like, post what you come up with on the forum and we can give you some feedback. But we don't do homework assignments for you.

first of all thank you for your fast reply and concern.

well its not a homework, its a project that im trying to do . the form is 20 pages long so i was just trying to get an advice from you on how to get started and what are the things that i need to read and practice that would help me in designing such a database.

i got the patient table ( ID, name, birthday, sex ...)

but take the history section i told you about:


For example, one of the section is the History section that has 20 unrelated yes/no fields:

smoker ( yes or no)

diabetes (yes or no )

Chronic Lung (yes or no )
.
.
.
.
.

If i put all of these (smoker, diabetes..) in one table called HISTORY, its not efficient at all ! because if the patient checks the smoker field and leave the other 19 unchecked because they don't apply, we will have wasted place in the table of that patient ...so im really lost !


im really not asking for you to write the design for me, all i want is a hint to get me started even a reference to similar problem/db design that i can read and learn from

thx

Talk with whoever gave you the form. Find out what information they want to capture, and how they want to use that information... This will give you far more guidance than we can because then you'll be working on the problems that your user(s) want to solve instead of an abstract that we concoct from thin air.

-PatP

Horrors! Wasted space! Soon, the World will be running out of bits and bytes as we squander them needlessly in nullable columns!

Seriously though, hardly any DBA gives a crap about wasted space any more. 20 or 30 years ago when Pat was a young DBA (ok, 50 years ago...) data storage was expensive, which led developers to do loony things like store years as two-digits (we all know how that ended up). These days, storage space is cheap. I can buy a terabyte for under $200 and walk around with it in my pocket (or am I just happy to see you?).
Where space does make a difference is in indexes, because that affects efficiency, but chances are that none of the Boolean values in your list are candidates for indexing anyway. Heck, as bit data types, you can store eight off them in a single byte.

If it were me, I wouldn't even store them in a history table. I'd store them directly in the patient table, unless you need to track changes to their status over time.

Here's a couple ideas to get you started.

patient table with name being one of the fields??? Do you update when someone marries or gets adopted or do you make them a new patient. How about a patient table with non-changing info? i.e; DOB, ?Sex?(i guess that could change if you are right kind of dr.), etc... then a name table(hint: patient id, seq number, name fields, begin/end dates) address table(see prior hint), phone table(see prior hint), etc....

How about a question table that stores all of your unrelated questions.
How about a patient response table, where you store the patient id, question id, and response? If a patient does not respond to a question, you don't insert a row.

Dave