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.

Thanks very much!

Looks ok

But take a step back

Forget about relationships

Start with identifying buckets (entities) of things you need to know about

I would start with a business process model and define all the function I need to accommodate

I would then interrogate each function and ask...what data (attribute) do I need to perform that function...I would then create a list of attributes for each function

I would then take all the attributes and drop them into their appropriate entities

THEN I would relate the entities

HTH

I'm going to give this a try. I'm new to this so... I'm not sure I can do this right.

Thanks for the help.