Saujib (7/2/2013)
let me rephrase my requirement.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 table
3. 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 Providers
LocationID bigint, --this is a foreign key to ServiceLocations
MemberID, --this is a foreign key to Members
MemberAddress, --
MemberCity,
MemberST,
MemberZip,
...whatever other columns (amount, service date, service type, etc)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/