Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

When I learned relational databases, the prof said that one would "almost always" want an artificial int as the primary key in a table, but did not specify what the exceptions are. At some time I stopped using them for junction tables, and never had a problem.

Now I am making a database with a lot of lookup tables, and wonder whether this is a case where leaving artificial keys out wouldn't make for a cleaner design and simple programming.

A toy example: assume that this is a mockup of the UI I want to achieve. example for a UI view

The design option with artificial IDs would be (Type is a foreign key):

LiteraryWork
Title                           Type
Winnie The Pooh                 1 
The Nightingale and the Rose    2 
Snowwhite                       2

LiteraryWorkType 
ID TypeName 
1  Novel 
2  Fairy Tale

And the option without them uses the Type name itself as the key (again, the column Type is properly declared as a foreign key):

LiteraryWork
Title                           Type
Winnie The Pooh                 Novel 
The Nightingale and the Rose    Fairy Tale 
Snowwhite                       Fairy Tale 

LiteraryWorkType 
TypeName 
Novel 
Fairy Tale

I tend towards using the second option, because I would need one less join when showing data on the screen. (I don't want to get rid of the lookup table entirely because I want to be able to restrict the values users may enter, for example by giving them a drop-down list bound to the lookup table). The only disadvantage I can think of is that, when a stakeholder says "but I want my UI to say 'story', not 'fairy tale'", I would have to update all data rows in the LiteraryWork table. I can live with this, as I don't expect it to happen often in my case.

Does the first design have any other advantages I am missing? Which of the two options is considered best practice, and why?

Edit2 As I understand it, the existing answers are afraid that I am trying to break normalization, as in

LiteraryWork
Title                           Type        LiteraryWorkTypeIsFiction
Winnie The Pooh                 Novel       Yes
The Nightingale and the Rose    Fairy Tale  Yes
Snowwhite                       Fairy Tale  Yes

To make it clear: the above is not what I am trying to do. Instead, if there really was more information pertaining to LiteraryWorkType, and I was using string IDs, I would record it this way:

LiteraryWork
Title                           Type
Winnie The Pooh                 Novel 
The Nightingale and the Rose    Fairy Tale 
Snowwhite                       Fairy Tale 

LiteraryWorkType 
TypeName         IsFiction
Novel            Yes
Fairy Tale       Yes
Conference paper No

The only difference to the "typical" database design would be that the ID is a nvarchar, not an integer. Which certainly has its drawbacks in storage needed, as pointed out, but I don't see which normalization rule it is supposed to hurt.

But this example aside, I am not trying to use string IDs when there actually is more information to be recorded about a LiteraryWorkType (so that LiteraryWorkType should be considered an entity in its own right). I am speaking about cases as simple as the toy example I gave at the beginning: the whole second table exists only because SQL has no "enum" type, and each data record in it consists of nothing but a single word, unique between records.

share|improve this question
    
regarding the edit: you are totally trying to sneak a whole entity table into another entity table. Just because the lookup table is small does not make it any less of an entity. Further, your suggestion that people can 'choose the rows to use' is completely garbage. Foreign key constraints exist specifically for that purpose - use them. –  Telastyn Mar 25 '13 at 15:06
    
@Telastyn I think the "Choose the rows to use" sentence was just a bad formulation on my part. I fully intend to use foreign key constraints. My point was that the users will not be able to add new rows to the LiteraryTypeTable from anywhere in the interface, or to type in free text for the Type field of the LiteraryWork table, so that I won't end up with a "Fairytale" entry as well as a "Fairy tale" entry. –  Rumi P. Mar 25 '13 at 15:21
    
@RumiP. use strings in the main table then. If your DB is small enough that storage and string processing is not a problem then you're OK. Don't try this on big databases though. –  gbn Mar 25 '13 at 15:33
    
@gbn Actually, I haven't yet decided which version to use. I asked this question exactly because I wanted to hear all drawbacks before committing myself to an option. I have seen examples of using meaningful strings as IDs in textbooks and lecture notes which do teach normalization too, so I was just surprised to hear that it is considered non-normalized, that's why I tried to get more info from the people who said this. –  Rumi P. Mar 25 '13 at 15:51
1  
There are a couple of questions at dba.stackexchange that address this exact question: dba.stackexchange.com/q/1910/13333 and dba.stackexchange.com/q/232/13333 –  Mike Partridge Mar 25 '13 at 15:52
show 1 more comment

3 Answers

up vote 4 down vote accepted

Repeating a variable length string over many rows (both data and indexes) is far less efficient than storing a tinyint value.

  • The string "Fairy Tale" takes 12 bytes at least on most systems including 2 for length.
  • You have denormalised and added data modification anomaly risk
  • You then have case sensitivity and collation to take into account for comparisons

Edit:

Your main problem is database size and bloating because rows are longer then necessary. This means less rows per page and more memory use for queries. See these for why

I've seen huge databases that don't use lookup tables (designed by Hibernate ORM on MySQL) and have long strings repeated. By my estimate, the database could have been 60% smaller at least.

Normalisation isn't an issue if you are using lookup table on the natural key. Which you have clarified

share|improve this answer
    
I cannot see how a data modification anomaly risk is created. As long as I am keeping the second table and using a relationship to it, I can have the type 'Novel' in my system even if I don't have a single record of a novel, so no insertion or deletion anomaly. Also, there is no other information associated with a LiteraryWorkType beside its name, so there cannot be an update anomaly. Could you please give an example of an anomaly which would occur in the second design? –  Rumi P. Mar 25 '13 at 14:30
2  
@RumiP. Database normalization might be a useful read. –  MichaelT Mar 25 '13 at 14:56
    
@MichaelT I read it. To put it in terms of this article, what I am saying is, "the type of a Literary Work is a candidate key in its own right, just like a SSN in the example, so if I use it instead of an artificially added autoincrementing column, normalization is not broken". I also clarified my question to say what I am not trying to do. If there is still a mistake in my thinking, could you please explain it, in terms of the article you linked, exactly what the mistake is? Which normal form am I hurting, and why? I am not yet sure if I really made a mistake, or if you misunderstood my q. –  Rumi P. Mar 25 '13 at 15:16
add comment

Does the first design have any other advantages I am missing?

You mean besides storing N fewer bytes per instance? Strings are big. Unicode strings are bigger.

Worse yet, by denormalizing the structure, you now have to change the name in N places rather than 1 if you have a typo. Or in another case, when you want to expose those names in other languages.

In general, the tradeoffs for this denormalization are well researched.

share|improve this answer
add comment

What happens when your users want to give multiple classifications to a story? What happens when you want to provide translations of the book type...or if you want to allow users to have their own private library with their own classifications? Once you hit one of those scenarios, future you will thank past you (or today you) for giving the table a unique key.

Table keys should have no significance within the system, because business rules change. Something that is "always" unique at first glance will have edge cases as you dive deeper.

share|improve this answer
    
Thank you for this example. While I had thought of the possiblity that I would have to change the database design (such as multiple classification), all cases I came up with would have worked with reusing strings as well as with reusing integers, or would have required such a radical redesign that all keys would have had to be changed anyway. Translation looks like an example where reusing integers would work well, while reusing string names, while still technically possible, would lead to a very confusing design. –  Rumi P. Mar 25 '13 at 15:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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