Keeping track of multiple statuses and their history RRS feed

  • General discussion

  • I have designed the following database for a small project. Please review and leave feedback. The main problem i'm having is to keep tracking of many statuses. The user wants to know the status of particular action on a given date. Do I have to create seperate table for just statuses with date added, date modified etc?

    Thursday, February 16, 2012 5:15 AM

All replies

  • First critique. There are no explanations other than "small project". :)

    Second, naming is inconsistant. Docs, or doctors, for example.

    Third, why the word "set". This is annoying, the TABLEs are named in plural and have the word "set" on them?!?!

    Naming aside, a bit of explanation would go a long way.

    Thursday, February 16, 2012 1:39 PM
  • Yep it is a small project.

    Fixed the namings. 

    I'm using lightswitch, the word set is added to tables by default. 

    Any idea on different statuses ? What is the best way to keep track of statuses with history and date ?

    Friday, February 17, 2012 4:58 AM
  • Hi,

    There are few observations as based on your DB design,

    1. Try to give detailed names for the tables/colums unless if any word is a business term. And maintain consistency on the same across all tables.

    2. Separate data/attibutes into master and detail tables.

       example: DoctorsMaster --with non repeatable data attributes(Name, Registration number, etc) and

                     DocterDetails -- Address, email, phone numbers etc

    DoctorsMaster , PatientsMaster, HospitalMaster etc. can be made as master tables as per my opinion.

    3. The relationship between DoctersHospitalsSet, DocsAddresses, DoctorsSet found to be a deadlock/looping situation. Avoid such relationships and restructure it. You can make use of map/bridge tables if necessary.

    4. If one patient can have multiple statuses. Create a Master table of different statuses. Make foreign key in main/transaction table. Thus if you want to add a new status for a patient that can be accomodated very easily. Following can be transaction table for maintaining multiple statuses for each patient.

    PatientId StatuID Date



    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, February 17, 2012 7:28 AM
  • Suresh,

    Thank you so much for your tips. I have implemented them and this is latest version. But I couldn't come up with a better way to map for your 3rd point. Each doctor works for multiple hospitals and this doctor has obviouslu different address at each hospital.

    When you are free please have a look at our codeplex project to understand our requirements. I just want to make sure I haven't missed out anything. 


    Friday, February 17, 2012 9:20 AM
  • Hi,

    Please see below, this may be helpful for your design.

    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, March 1, 2012 7:33 AM
  • Some simple questions

    1.Can a person be a doctor and also a patient  ?

    2.How XXMaster and XXDetails are different? 


    Thursday, March 1, 2012 9:52 AM
  • SvgSuresh,

    Thanks for the posting. I will revise accordingly. I'm printing a letter in my application to send it to Doctor. Since each doctor has multiple addresses. I'm not able to keep track to which address I should send. Guess, I have to link the HospitalAddress to Patients too ? 


    Honestly that's a good point. I would really love to see how can we handle if person can be both doctor or patient ? Create a table with Person and Person type ? 

    Thursday, March 1, 2012 9:59 AM
  • Hi,

    Answer to Q1:

    If a person can be both Doctor as well as Patient, in that case there will be a record in both PatientsMaster and DoctorsMaster table. As these two are differents records. But, there will be a drawback, if you have such record in Patient associates to its own id(doctor).

    Answer to Q2:

    As per your explaination, I guess, a doctor is associated to a hospital by his address and not with his ID(license#). In such case you can change the relationship in DoctorHospitalMap table. Instead of DoctorsId put DoctorsDetailId, that way you can get all the details.

    i.e. Hospital, associated doctor and his patient or vice versa. Please refer the sample below,

    Hope it helps.

    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, March 1, 2012 11:24 AM
  • Yes, I'd suggested having Person entity for most common data such as Name, Birthday, Sex which do not depend on the roles the person plays. Doctor and Patient are subtypes of Person. 


    Thursday, March 1, 2012 6:25 PM