Design feedback

[SaviorSix]

Hello there,

I have a question, which I know is most likely very basic, but for some reason I keep drawing a blank on what to do. I figured I would post what I have so far, and see if anyone had some feedback.

This database is based on an outline on paper. SO for example:

1. Category
....a. diagnosis
........i. sub-diagnosis
........ii. sub-diagnosis
....b. diagnosis
........i. sub-diagnosis
............1. sub-sub-diagnosis
2. Category

and so forth.
As you can see in the database, I have designed 4 tables, with one-to-many relationships joining them.

each category, diagnosis, sub-diagnosis, sub-sub-diagnosis, will be pre-entered into the database. The DB will be for a user to enter an ID number (I have not added this element into the db design yet)
and then select diagnoses from combo boxes, which, ideally, will cascade depending on the choices made.

For example, If I enter ID #1 into the main form (text box most likely), each record in the subform will have 4 combo boxes, each corresponding to Category, diagnoses, sub-diagnoses, sub-sub-diagnoses. Depending on which category is selected, the choice will cascade down into the remaining 3 cbo, limiting the choices available to select.

I can't seem to figure out how to arrange a table where the results of the selections can be saved!

 

[georgedwilkinson]

I tried hard to ignore this request but I got sucked in.

I was unable to look at the forms/queries in your database, as too many objects don't seem to exist.

However, I will say, from your explanation, that you are way off on your design.

Why do you think you need a separate table for sub-diagnoses? What happens if you one day have a sub-sub-sub-diagnosis? Or a sub-sub-sub-sub-diagnosis? Or diagnoses that are related to each other in a way other than parent/child?

Only one (possibly 3) table(s).

If one table, DiagnosisTypes: ID (PK), CategoryID (FK), ParentID (FK - self-join), DiagnosisCode, Description, etc.

If 3 tables, second is DiagnosisTypeRelationships: ID, FromTypeID (FK), ToTypeID (FK), RelationshipTypeID (FK), Comments, etc.

Third is DiagnosisTypeRelationshipTypes: ID, Type, Comments, etc.

Either of these can be used but should be tempered against what you're actually going to use the database for (which you didn't say).

Also, unless you are an insurance company, the DiagnosisTypes are a minor part of your system, acting as lookup tables. You must design the tables to store a person's diagnosis.

It looks like you have a long way to go.

 

[dsfcom]

I agree with georgedwilkinson -- one has to look "big-picture" when designing datasets. You would need to cosider needs for future expansion without too many large design changes (i.e. addition of another level of subcategory). His method would seem best. Personally, I would go the single-table route then possibly use queries based on the main diagnosis table to filter levels of diagnosis to populate your form used to select a category. As far as storing the category selected, is there really a need? You could simply create a query based on the main category table and use that to populate a dropdown with the values from the category query (query mainly for sorting purposes).

 

[SaviorSix]

Either of these can be used but should be tempered against what you're actually going to use the database for (which you didn't say).

Also, unless you are an insurance company, the DiagnosisTypes are a minor part of your system, acting as lookup tables. You must design the tables to store a person's diagnosis.

It looks like you have a long way to go.

Yes that is what I tried to explain in my first post, I need to design the table to store a person's diagnosis. Unfortunately I FAILED MISERABLY according to you,
thanks for trying to help though, I think...

Anyway, at first I did think of designing it as one table only! Then I started to second-guess myself into breaking down the categories into separate tables. This seems to make things easier. SInce I can make one table with all diagnoses, and use that simply as a lookup table.

 

[georgedwilkinson]

I think the answer you may have been looking for (though you never said it) is:
People:
ID (PK)
FNAME
LNAME
etc.
(please don't put the address and phone number in this table!)

PatientVisits:
ID (PK)
PatientID (FK from People)
DoctorID (FK from People)
VisitTime
Description
etc.

Diagnoses (I think, whatever the plural of "Diagnosis" is-I think this might be the answer to your original posting):
ID (PK - In case you need it in later design)
PatientVisitID (FK from PatientVisits)
DiagnosisID (FK from DiagnosisTypes)
Comments
etc.

Nothing stands alone, as my friend dsfcom said. You really do need to look at the big picture.

Is that more what you had in mind?