I need help with my ER-Diagram. Right now I'm ignoring the 1NF issue regarding the Address. I will address that after i resolve what i consider to be more important issues.
Previously this ER-Diagram had a two entities -- one for Provider and the other for Patient. (I've provided a picture of this design.) Then i decided to create a Super/Sub type relation with Person being the "parent" entity/table and Patient and Provider being the "Child" entity/tables.
What I was hoping to achieve was a situation where a Provider could also be a Patient also. But my current design doesn't seem to achieve this. It seems that I may need just one entity -- Person! (If i move guarantor into Person, i dont see a need for Patient.)
If I only used one entity (for patient, guarantor, and provider), I don't think I'd need an additional recursive relationship on person. But since I've not developed databases much, I'm not sure if this is a good design or not. I can't think of any reasons why i should not design it this way other than the fact that it could get confusing fast! There is also a difference between a Guarantor and Provider. A Guarantor is responsible for the Bill and is the owner of the insurance. So every patient has at least one guarantor. But a Patient is not always seen by the same doctor if there is more than one doctor in the facility. I guess i could have a check box that simply says IsDoctor. I'm not sure how I would enforce the restraint on this design. (RESTRAINT on BillingStatement: Check to make sure Person has IsDoctor check??? If not reject PersonID as invalid?)
Alternatively, I guess, could have a Person entity as it appears and a Provider entity as it appears but no Patient entity.
A similar related issue is that I believe that the BillingStatement should have both Patient info and Guarantor info.
I also believe that the Guarantor should be associated with the Payment entity. I think it is the Guarantor that is responsible and not the patient. I'm wondering if i should reconsider my Patient entity.
These are related issues bearing on my design and i thought it was best to bring all these complexities to the fore before trying to get information and then having to change designs again once i added more details.