SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


table creation on one table referenced by many tables


table creation on one table referenced by many tables

Author
Message
reddysrinu
reddysrinu
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 41
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25969 Visits: 17525
Saujib (7/2/2013)
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.


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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
reddysrinu
reddysrinu
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 41
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25969 Visits: 17525
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
reddysrinu
reddysrinu
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 41
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25969 Visits: 17525
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search