May 10, 2018 at 8:22 pm
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?
May 11, 2018 at 6:05 am
You can, although overall that sounds like a poor design. Name and address in both tables could lead to duplicate data and all sorts of issues.
I'd rather see a Person table with names, and Address table with addresses (two or more people share an address) a PersonType table (has rows for sender and recipient) and then a mapping table, let's call it PersonToPersonType that let's any one person be more than one type. Use artificial keys for the primary keys on all the tables (IDENTITY or GUID, I'm easy either way). Identify natural keys and make them constraints but don't use them for foreign keys (lots of reasons for this, but the biggest one in my sights these days is the GDPR).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2018 at 3:10 pm
Thank you, for your response. But there is quite a bit of problem. Your answer is too complex to understand. Could you water it down a little so that I can understand it, please. Thanks much. Really appreciate it.
May 11, 2018 at 3:17 pm
My question is what don't you understand?
May 11, 2018 at 4:23 pm
So, your question is, can you make the four columns a primary key on one of the tables and then make a foreign key on the other table, right? The answer is yes, you can. Here's how you create a primary key. Here is how you create foreign key.
The problem is, as you defined your data, both tables have names and addresses. So, today, I'm a Sender and you put me into the Sender table and my name is 'Grant'. Tomorrow, I'm a Recipient. Do you add me to the Recipient table? If so, what happens if you spell my name 'Grunt'? You have me twice in your database and my name is spelled two different ways.
So, in relational databases what we do instead is create a table for people. Then you enter my name one time, 'Grant'. And the table has an identity column. We'll make that the primary key. Let's say we also add more names, my kid 'Det'. My dog 'Nika'. My wife 'Julie Newmar' (sigh). Now there are four rows in that table. Each one has an identity value, 1-4. I'm #1, because, of course I am. So, we also create an Address table. That table also gets an identity column and a primary key. We add my address, '1313 Mockingbird Lane'. It gets an identity value of 1 too because it's the first row into the table. Now, we want to match the people to the address. All four of us live in the same place. We're going to create a new table called PersonAddress. It's going to have two columns, PersonID and AddressID, the two identity columns from the Address table. We'll create a foreign key between the Person table and the PersonAddress table and another between the Address table and the PersonAddress table. We'll also create a primary key on the PersonAddress table, but this one will be what is called a compound key, two columns, PersonID and AddressID. That way, you can't add the same person to the same address. Then, we add data to the PersonAddress table. 1,1 is me. 2,1 is Det, 3,1 is Nika and 4,1 is Julie (and Grant is very happy).
Do the same exercise to create a PersonType table (which can be 'Recipient' and 'Sender') and then a PersonPersonType table which matches that new table to the Person table, so that you can add me as either a Recipient or a Sender.
Now, there are actually a ton of other stuff I'd want to add out of the gate. A Begin and End date for the address table (probably for when Julie moves out because she can't stand me) so that you end a relationship without deleting it. Other stuff like that as you think of it will come up.
Is that more helpful?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2018 at 8:32 pm
@Grant Fritchey.. Lol. I definitely like that explanation. It was enjoying and educational and funny of course. . Thanks a lot.
But I should let you know that from your explanation I have somewhat started to Admire you.... hahahahaha
I have read what you said carefully. The way you form your explanation has cause me to think alot as not alll the time I get an explanation like that. well said and well organized. Right now I am still unable to stop talking about it..lol.. This has brought across my mind to make friends with you.
1st reason: I want to form an I.T. Group in my community where everyone in the community who is interested in this area of study can get the opportunity to learn the basics and advancements in the I.T. industry. With this being said, having someone capable of making an ant understanding your basic knowledge is really beneficial.
2nd reason: I want to make more friends in the I.T. industry. that way ideas of others can be brought up into connecting many great upcoming industries. meaning that one can bring an idea they want to achieve and others can help to implement that idea by giving some pointers as to make that idea a reality.
3rd reason: Not many people around me are I.T. skill wise and knowledgeable. So making friends with you is even more necessary and hard to find people to discuss it with..
I hope those reasons could persuade you to accept my request.
Oh and this request is also forwarded to all who read this message.
May 12, 2018 at 6:18 am
Happy to help. I'm always around here on the forums, or you can go to my blog. I also post the occasional video.
One recommendation for finding like-minded people to discuss things, see if there is a PASS Local Group near you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2018 at 4:35 pm
@Grant Fritchey, Thanks. Your help was much appreciated. second problem.
I need to know how to utilize this structure in C# windows form application. Could you give me and Idea as how it would be set up.
May 13, 2018 at 6:14 am
Sorry, not the person to help you with that. My C# skills are rusty to the point of immobility. Pretty sure there's a programming forum. I'd post there. Someone will pick it up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2018 at 12:29 pm
@ Grant Fritchey. Okay, I understand. I thank you for the help that you have provided me with thus far. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply