Database design
-
hey guys.....
i have a scenario here......I was ask recently to modify our database to facilitate the collection of new information, but i'm having a lil problem with designing this new addition of Entities.
Requirements:
1. Collect info on "HAART" (new entity).
note: Haart is a fix collection of things
2. Patient can have either a "Change HAART" or "Switch HAART".
3. There are seperate Reasons for "Change_HAART" and "Switch_HAART".
ok i just want to see how you guys would design an ERD or a relational diagram to represent the above.
thx much
-
Status = 'Change' or 'Switch'
-
did you post an image?
-
Yes, I don't know what happened.
-
I am not sure if this is what you are looking for.
Tbl_HAART
ColumnName
Data Type
Comment
Haart_ID
Int
PK
Desc
Varchar
Tbl_HAART_Patient
ColumnName
Data Type
Comment
EntryID
Int
PK
Haart_ID
INT
FK to Tbl_HAART
Patient_ID
INT
FK to Tbl_Patient
Status
CHAR
‘C’- Change
‘V’- Switch
Reason
VARCHAR
Modify_date
Datetime
Tbl_Patient
ColumnName
Data Type
Comment
Patient_ID
Int
PK
Patient_Name
Varchar
-
Image from Denby to review at: http://www.sqlservercentral.com/forums/images/denverdavis_ERD.GIF
-
thx a lot steve.....
Loner or anyone you can have a look at the diagram for an idea of what i'm trying to design.
my prob with it is that i don't know how to connect the "Switch Haart" and "Changed Haart" to it's parent table "Haart Record".
any ideas from you guys would be glady appreciated.
thx again
-
First I don't understand why you have a table for 'Switch Haart' and a table for 'Changed Haart', and a table for 'Switch Haart Reason' and a table for 'Changed Haart Reason'. Maybe you have a reason, but that's what I would design.
Table Haart (HaartID INT PK)
Table Patient (PatientID INT PK, Patient Name VARCHAR)
Table HaartStatus (HaartStatusID INT PK, Status VARCHAR)
Status = 'Changed' or 'Switch', it later on the users want to add another status, you can just add it here.
Table StatusReason (ReasonID INT PK, HaartStatusID INT, Reason VARCHAR)
HaartStatusID foreign key to HaartStatus.
Table HaartPrimaryRecord (HaartRecordID INT PK, PatientID INT, Date_Seen Datetime, HaartID INT, DateHaartStarted Datetime)
Table HaartSupplementalRecord(SuppRecordID INT PK, HaartRecordID INT, HaartStatusID INT, ReasonID INT, StatusDate DATETIME)
HaartRecordID FK to HaartPrimaryRecord
HaartStatusID FK to HaartStatus
ReasonID FK to StatusReason
-
Ok loner, thx for your suggestions.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply