0

I have 3 excel documents and have imported them to SSMS. The 3 excel documents are related but does not have any "ID's" or "Product ID" and I created it manually.

The excel contains lots of data but just an example:

Excel Table 1

+----+--------------+--------------------+
| ID |     Town     |      Address       |
+----+--------------+--------------------+
|  1 | Piccadilly   | 979 Church Street  |
|  2 | Bloomsbury   | 70 Broadway        |
|  3 | Holborn      | 9027 South Street  |
|  4 | Southwark    | 94 St. John’s Road |
|  5 | RedBridge    | 4 Park Lane        |
+----+--------------+--------------------+

Excel Table 2
+----+-------------+--------------------+
| ID |   Town      | Train_Station_Name |
+----+-------------+--------------------+
|  1 | Bromley     | Station A          |
|  2 | Enfield     | Station B          |
|  3 | Holborn     | Station C          |
|  4 | Bexley      | Station D          |
|  5 | Westminster | Station E          |
+----+-------------+--------------------+

Excel Table 3

+----+---------------+-------------+
| ID | Property_Cost |    Town     | 
+----+---------------+-------------+
|  1 | $35,000       | Piccadilly  |
|  2 | $39,000       | Piccadilly  |
|  3 | $31,000       | Holborn     |
|  4 | $36,000       | Bexley      |
|  5 | $33,000       | Piccadilly  | 
+----+---------------+-------------+

The problem I'm facing is that right now I want to create a new table for "Town" and if I create it, I have no idea how I should create the relationship between the 3 tables. If I put their ID's as primary key, it will mess up the data

Zafear
  • 13
  • 4

2 Answers2

0

Maybe you should know DB normalizations(such as, 1st, 2nd, 3rd) then I think you will find the answer for your question

  • Hi Khon. Thank you for the reply! I just watch some youtube videos (from edureka! ) on DB normalization, (Still learning on DB). I'm just not sure how does the linking/relationship works. If I create another table call "Town" with ID and set it as primary key to connect the three tables, e.g ID 1 = Piccadilly , ID 2 = Bloomsbury , how is it linked to Table 2 and 3 if the IDs are different or the Town have duplicate names? Or do I have to create another "Town" table for each table? – Zafear Mar 29 '20 at 04:56
  • Based on your questions, I can say that you still don't understand some concepts about databases. Connecting two tables(table #1 ==>> table2) means that You reference to the primary key of Table #2 from your Table #1. Plus, there MUST NOT be duplicate data according to database normalization requirements. Each normalization has its own requirements. You can look at the following resources to understand db normalizations: https://www.w3schools.in/dbms/database-normalization/ –  Mar 29 '20 at 15:23
0

I believe this is already answered how to create relationship between tables here:

Creating relationships between tables

Furthermore: if you want to give 1-1 relationship

Set the foreign key as a primary key, and then set the relationship on both primary key fields. That's it! You should see a key sign on both ends of the relationship line. This represents a one to one.

YASH KUMAR
  • 1
  • 3
  • Hi Yash Kumar. Thank you for the reply, I just not sure how the relationship works if the IDs are different, correct me if I'm wrong, but in terms of SQL relationships, it's only integers that are unique that can be in a relationship? – Zafear Mar 29 '20 at 05:06
  • Please go through some basic videos on relationship between tables in ssms .and let me know if u get that. https://www.youtube.com/watch?v=jyklg0cTN3M – YASH KUMAR Mar 30 '20 at 01:14