2

Sorry but I don't know how to call in the Title what I need.

I want to create an unique key where each two digits of the number identify other table PK. Lets say I have below Pks in this 3 tables: 

Id  Company     Id  Area        Id  Role
1   Abc         1   HR          1   Assistant
2   Xyz         2   Financial   2   Manager
3   Qwe         3   Sales       3   VP

Now I need to insert values in other table, I know that I may do in 3 columns and create a Composite Key to reach integrity and uniqueness as below:

Id_Company  Id_Area Id_Role ...Other_Columns.....
1           2       1
1           1       2
2           2       2
3           3       3

But I was thinking in create a single column where each X digites identify each FK. So the above table 3 first columns become like below (suposing each digit in an FK)

Id  ...Other_Columns.....
121
112
222
333

I don't know how to call it and even if it's stupid but it makes sense for me, where I can select for a single column and in case of need some join I just need to split number each X digits by my definition.

Flib
  • 165
  • 3
  • 14

1 Answers1

2

It's called a "smart", "intelligent" or "concatenated" key. It's a bad idea. It is fragile, leads to update problems and impedes the DBMS. The DBMS and query language are designed for you to describe your application via base tables in a straightforward way. Use them as they were intended.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83