CODEWITHSUNDEEP
sqldatabase
Nigel Colpitts
Nigel Colpitts

Reputation: 11

Should I create a surrogate key?

I'm working from a dataset where materials are tested for various descriptors. Sometimes there will be multiple values for a single material-descriptor combination. (e.g. SKU ABC-001 had flammability tests done with results WUXH005 and WUXH008)

As-is, all natural primary key candidates include the results, which seems unreasonable. It is guaranteed that a material-descriptor-result is unique. Should I add an integer surrogate key, or maybe a more meaningful surrogate like below? 

{SKU}-{test shorthand}#{SKU-test index}

e.g. 

ABC-001-Flam#1 
ABC-001-Flam#2

Upvotes: 0

Views: 38

Answers (1)

JohnHC
JohnHC

Reputation: 11195

Why not use more than one table?

Material -- contains all the info about the material
m_id -- The material id, primary key
...

Tests
t_id -- The test id, primary key
m_id -- Foreign key to the material table
... -- descriptions et whatnot

Results
r_id -- the specific result id, primary key
t_id -- Foreign key to the test table
... -- descriptions et whatnot

To select the data, use a couple of joins:

select m.*, r.*
from materials m
inner join tests t
on t.m_id = m.m_id
inner join results r
on r.t_id = t.t_id

Upvotes: 1

Related Questions