July 2, 2013 at 2:15 pm
hi
Here a requirement that I am trying to implement
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
member, provider, sales representative tables has reference to sales order table
Which is best way to create tables
1. member, provider and sales representative have FK to Address table
2. Address table has 3 columns for member, provider and sales representative.
3. Instead of address table, add columns to each table
member, provider and sales representative to address is one-one.
July 2, 2013 at 2:25 pm
Saujib (7/2/2013)
hiHere a requirement that I am trying to implement
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
member, provider, sales representative tables has reference to sales order table
Which is best way to create tables
1. member, provider and sales representative have FK to Address table
2. Address table has 3 columns for member, provider and sales representative.
3. Instead of address table, add columns to each table
member, provider and sales representative to address is one-one.
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.
_______________________________________________________________
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/
July 2, 2013 at 2:36 pm
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
July 2, 2013 at 3:21 pm
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/
July 2, 2013 at 3:36 pm
thank you very much.
What if, provider and service location address will change per claim.
Or the user while creating the claim should have the ability to modify provider and service location address for this claim.
July 8, 2013 at 7:20 am
Saujib (7/2/2013)
thank you very much.What if, provider and service location address will change per claim.
Or the user while creating the claim should have the ability to modify provider and service location address for this claim.
Unless you need to allow for multiple services per claim the structure I suggested would support this just fine.
_______________________________________________________________
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply