Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

table creation on one table referenced by many tables Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 2:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 9:20 PM
Points: 6, Visits: 15
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.
Post #1469740
Posted Tuesday, July 2, 2013 2:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 13,313, Visits: 12,173
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 Moden's 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)
Post #1469743
Posted Tuesday, July 2, 2013 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 9:20 PM
Points: 6, Visits: 15
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
Post #1469745
Posted Tuesday, July 2, 2013 3:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 13,313, Visits: 12,173
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 Moden's 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)
Post #1469770
Posted Tuesday, July 2, 2013 3:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 9:20 PM
Points: 6, Visits: 15
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.
Post #1469780
Posted Monday, July 8, 2013 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 13,313, Visits: 12,173
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 Moden's 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)
Post #1471177
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse