linking one table to one of many other tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have an Address table which can be referenced in many other tables (Employee table, University table, Work_Experience table , ... other tables )
what is the best practice to link address table with the other tables knowing that an address row will be only for one table.

Posted 17-Apr-15 22:49pm

Eng.Yahya92

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-04-17T23:06:00

Use an ID row in the address table (INT or UNIQUEIDENTFIER is fine) and set that as a FOREIGN KEY in the other tables.

Then when you want to get the address for an employee, you just use a join:

SQL

SELECT a.EmpID, a.EmpName, b.Address 
FROM Employees a
JOIN Addresses b
ON a.HomeAddress = b.AddrId

Suvendu Shekhar Giri · Accepted Answer · 2015-04-17T23:15:00

Solution 2

You can take reference of AdventuresWorks sample DB provided by Microsoft.
Schemas in AdventureWorks[^]
Take a look at the Address[^] table

Just a Primary Key - Foreign Key relation between Address and dependent tables should be enough.

Hope, it helps :)

Posted 17-Apr-15 23:15pm

Suvendu Shekhar Giri