SQLTeam.com | Weblogs | Forums

Relationships in SQL


#1

Creating a relationship in SQL.
Suppose I have two tables. One is called Sender's Table and the other is called Recipient's Table. They both contain First Name, Last Name Address, telephone #
Can I create a relationship between first name, last name, telephone, Address in Sender's Table and address in recipient table?


#2

Yes, you could use a foreign key, for example.


#3

a better design would be to have one table called people or persons with primary key peopleId or personId.
then another table that is called maybe mail or transaction with senderid, recipientid that point to peopleId each as fk

create table #persons(personId int, firstname varchar(50), 
lastname varchar(50), telephone varchar(50))

create table #correspondence(senderId int, 
recipientId int, message varchar(50), sentdate datetime )


insert into #persons
select 1, 'Darth', 'Vader', '1212' union
select 2, 'Luke', 'Skywalker', '3334355 Wars' union
select 3, 'Chewy', 'Backer', '2061213333' union
select 4, 'Princess', 'Lee Yah', '20612122' union
select 5, 'Ewok dem little teddy bears', 'Real Woke', '20612145454' union
select 6, 'Darth', 'Revan', '2061217766' union
select 7, 'Darth', 'Sidious',  '2061213336' union
select 8, 'Han', 'Solo',  '611' 

insert into #correspondence
select 1,2  , 'I am your dada', getdate() -100 union
select 2,3  , 'Engage thrusters!', getdate() -90 union
select 7,4  , 'hey cutie', getdate() -80 union
select 3,8  , 'mmmmrrrmmmmrrrhhhhh', getdate() -70


select sender.firstname + ' ' + 
         sender.lastname + ' said ' + c.message 
+ ' to ' + recipient.firstname + ' ' + recipient.lastname
	   
  from #persons sender
  join #correspondence c on sender.personId = c.senderId
  join #persons recipient on recipient.personId = c.recipientId

drop table #persons
drop table #correspondence

#4

It seems you don't quite understand the question I asked.

I will try to rephrase.

Is it ok to create a relationship between table with the same column name but will never have the same data within them and the purpose is quite different as well.

As I stated in the original question I want to know if the address in the sender table can be linked to the address in the recipient's table.

I hope I clear up the misunderstanding of the question with this explanation.

Thank you, for your response. It is much appreciated.
If you now understand the question please respond.. Thank you.


#5

no you cannot. you cannot create a many columns (first name, last name, telephone, Address) to one column relationship (address in recipient table)


#6

That somewhat answered my question. Thanks very much.