Multiple roles and addresses

[andrad]

Hi everyone! I am trying to figure out how a person can have multiple addresses with multiple roles. My database will consist of three types of people that will be entered, a client/customer, a doctor/health professional, a business contact.

A client will have a home address, a doctor/health professional will have their clinic address and business contacts will have their business address. I am not sure how to implement this.

Here is a link to my diagram,

http://members.shaw.ca/joso/design.jpg

let me know where you think should be changes. Thanks!

Joe

 

[johnherman]

You have a basic person. As I understand it, a person can only have one role (subtype). Each person will have a subtype (one of the 3 at this time). Each subtype of person has their own attributes. Clients will have home addresses, providers will have clinic addresses and business contacts will have business addresses. You can store the addresses separately or together at your option. Each subtype of person will probably also have other attributes specific to their subtype (provider might have specialty code, business contact might have e-mail address or website, etc).

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[andrad]

Thanks John for replying. So you from my diagram you think i'm ok with storing basic fields (lastname, firstname, etc..) in one table (person table) than split the roles into different tables like i have? You are right, i do have a website and fields unique to each role. I was originally going to create an address table linked to the Person Table than link that to 3 role tables.

What's you opinion on take the nextaction (future appointments) stuff out of the interactions table and creating another table for that.

thanks

Joe

 

[johnherman]

I am having trouble reading your diagram as my software wants to compress the whole jpg image into one screen which makes the print impossible to read.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[andrad]

I created a better jpg, here it is

http://members.shaw.ca/joso/diagram.jpg

If you have any suggestions let me know. if anyone else has suggestions, feel free to let me know as well. thanks

Joe