One table Address is used by many tables. like member, provider and sales representative.While creating Sales Order, User would entry information for member, provider and sales representative.and address for each entity

One table Address is used by many tables. like member, provider and sales representative.While creating Sales Order, User would entry information for member, provider and sales representative.and address for each entity

Why does a sales representative have an address? I don't quite understand what is a member and a provider as it relates to a SalesOrder. Is a member the customer? If so, what is the address? Is is the billing address, the shipping address? I can't even hazard a guess as to what a provider is.

Personally I would avoid overnormalizing by creating an address table to hold historical information. This data would not be editable so you are not gaining anything by putting it in its own table.

I will be happy to help you if you can provide some more details about exactly what you are trying to do.

User is creating a claim for a patient/member (patient address) and provider is doctor(doctor billing address) and service location (where patient was treated).

address is common in all 3 tables

1. should i create a address table or have address columns in all 3 tables.2. create FK columns for patient, provider and location in address table3. Create address table and add address PK as FK to each table

User is creating a claim for a patient/member (patient address) and provider is doctor(doctor billing address) and service location (where patient was treated).

address is common in all 3 tables

1. should i create a address table or have address columns in all 3 tables.2. create FK columns for patient, provider and location in address table3. Create address table and add address PK as FK to each table

Thanks. That makes perfect sense now. I would probably do a hybrid here. I doubt your provider address is going to change or the service location? Those would make perfect sense to have a Provider table and a ServiceLocation table. Each of those tables would have their own address. Then you would be able to use the foreign key for both of those. For patient data I would record the patient address in the claim table. This way you have the historical information about the address at the time of the claim (since people move).

So in other words the Claim table might look like:

ClaimID bigint,ProviderID bigint, --this is a foreign key to ProvidersLocationID bigint, --this is a foreign key to ServiceLocationsMemberID, --this is a foreign key to MembersMemberAddress, --MemberCity,MemberST,MemberZip,...whatever other columns (amount, service date, service type, etc)