5

I'm working as a "guest" dev at a random company.

I'm working with a table that does not have a primary key by definition(Oracle).

The column named "SOMETHING_ID" is varchar2(10) and constructed at insert time by the following operation. And it is used as a primary key and referenced by other tables.

'A_STRING' || LPAD(SEQUENCE_NAME.NEXTVAL, 7, '0')

Some examples of the primary key data are

CH-00004321
CH-00004322
CH-00004323

My questions are,

first, in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ?

Second, in what circumstances would it be justified not to have a primary key defined but you have a column that distinctively distinguish each row ?

Third, is my perception correct that I see the combination of the type string(CH, IN, MX) and a sequence number is against the 1st normalisation ?

Fourth, in the table, there is only one "type string" value (let's say CH). Is it okay to add that string to the pk while it does not seem necessary since there is only one value ? (The nature of the table does not seem to allow any other value for the type string though unlike my example of food. Perhaps you can see the table name as "CHINESE_FOOD", hence no other type string value would seem logical.)

Note - 1. Actual column, sequence names are replaced with arbitrary names.
2. The aim of this software is prediction of the damages caused by natural disasters.

  • 2
    Oh my! How lucky you are to have only one table set up this way. I work in a galaxy of 950 tables ALL set up this way. – Pieter Geerkens Dec 1 '15 at 4:09
  • 2
    Well, that was one example out of mere 118 tables. It's not a competition but, wow... I feel for you. – Ascendant Dec 1 '15 at 4:18
5

in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ?

Under no circumstances would it be justified. A column containing two different data elements as one value is a violation of first normal form. 1NF requires, among other things, that each column contain a single value. This practice also destroys the guaranteed logical access of every data element by table name, column name, and key value. Now if each data element were in its own column, and the combination of columns made up a key, that is not a problem. Ultimately this depends on the entity type for which the table holds entity occurrences. The key chosen should be something used by people in the real world to identify occurrences. Perhaps this makes sense if we are talking about a menu, and the menu has a section for Chinese food, and then gives each dish a number like #1, #2, etc.

Second, in what circumstances would it be justified not to have a primary key defined but you have a column that distinctively distinguish each row ?

Again, under no circumstances. If there is a column whose values uniquely identify each row that column must be declared as a unique constraint so as to ensure the occurrences entered are not duplicated and thus become inconsistent with the real world entities they represent.

Third, is my perception correct that I see the combination of the type string(CH, IN, MX) and a sequence number is against the 1st normalization ?

Absolutely, as described above.

Fourth, in the table, there is only one "type string" value (let's say CH). Is it okay to add that string to the pk while it does not seem necessary since there is only one value ?

No, for the same reason as this violates 1NF. Again that column contains 2 different values and each column must contain only a single value to be in 1NF.

Fabian Pascal's Practical Database Foundation Series is a great reference to dive more deeply into these fundamentals. Paper #4 addresses keys specifically.

  • I agree this isn't a great setup and that splitting the type and record number into two columns as a composite PK is much better. Assuming the PK is a clustered index could the original dev have been thinking about using this hack as a way to improve range scans/locality of similar types while allowing one column FK relationships? That seems like one possible benefit, but a composite PK would obviously be the better choice. – Erik Nov 30 '15 at 23:40
  • I just had a meeting with a "senior" dev and he said he would create tables that each has a column of multiple values put into one big string and I have 7 business days to finish this project :) So I couldn't really read all the posts on the blog but the link you provided seems really the kind of stuff I should be reading and would love to read. Thanks ! – Ascendant Dec 1 '15 at 6:14
1

Under no circumstances would it be justified.

Too strict. If the combination is for just visual purposes and it is guaranteed that it will always be accessed by the DBMS as a single value". The problem is that such guarantees are shaky.

Now if each data element were in its own column ...

This would be useful if other codes were added to CH, otherwise one of the two columns would have the same value in every row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.