CODEWITHSUNDEEP
mysqlsqldatabaserelationship
Malik Perang
Malik Perang

Reputation: 400

Need senior help to verify my understanding about Surrogate key in relational database design

I would like to know if my understanding about Surrogate key is correct or not.

E.g:

Below is my example database design

Customers Table

| id(PK) |  Name   |    Age       |   
|  1     |  John   |  34          |
|  2     |  Doe    |  30          | 

QrCodes Table
| id(PK) |  QRcode | Tracking No. | CustomerID (FK)
|  1     |  QR123  |  TR901111    |   1
|  2     |  QR345  |  TR902222    |   2


Redemptions Table
| id(PK) | Name          | Tracking No. | CustomerID(FK)
|  1     |  Redemption 1 |   TR901111   |  1
|  2     |  Redemption 2 |   TR902222   |  2
|  3     |  Rdemption  3 |   TR902222   |  2

As you can see,I have two Tracking No. column,one for qrcodes table & one for redemptions table. So then I can have a query like find all Qrcodes & redemptions by tracking no only.

Is the Tracking No. column can be classify as Surrogate key? or is that incorrect?

Upvotes: 0

Views: 48

Answers (1)

simon at rcl
simon at rcl

Reputation: 7344

It's getting a bit confused in the comments but they're basically right (upticks for all). What you need to do is drop TrackingId from Redemptions and add QrCodeId as an FK to the QrCode table.

If each QrCode has a unique TrackingId (I think this is the case) then it should have a Unique Index, but the PK is the Id. But without understanding what you're modelling it's difficult to say definitely.

Upvotes: 1

Related Questions