2

I have a column varchar titled Frequency where I can only insert a pre-defined set or strings

'Daily', 'Weekly', 'Monthly', 'Yearly'

and nothing else and they won't change for sure.

Is there any reason to create a separate table titled Frequencies and a reference to its ids via a foreign key or should I keep the column Frequency in the main table in terms of performance, readability, maintenance, etc.

Performance-wise, int is better, yes, but one additional request to a separate table is worse.

Or maybe it's better to keep integers in the main table, not to create the 2nd table and on a client side create a map:

1 -> 'Daily', 
2 -> 'Weekly', 
3 -> 'Monthly',
4 -> 'Yearly'
Alan Coromano
  • 24,958
  • 53
  • 135
  • 205
  • Possibly related question [Is using char as a primary/foreign key a no no?](http://stackoverflow.com/q/1419210/4519059) ;). – shA.t Apr 10 '16 at 07:58

2 Answers2

2

Reasonable RDBMS will optimize the lookup to the small table of frequencies into hash table lookup or such. Storing full string is a waste of space in the main table. Use numeric indices and the separate table. If your client wants to do the mapping at client side then then can still do it, if they want to get full names by joining the small table, they can still do it.

wilx
  • 17,697
  • 6
  • 59
  • 114
  • This is an old discussion: **Artifical keys** versus **natural keys.** Both have pros and cons. I have written databases with both systems. Today, I prefer natural keys, because when you look into a table, you directly can see and edit its content without needing to join with a 2nd table everytime. – SQL Police Apr 10 '16 at 15:33
2

Design Your database as normalized, that mean separate table for those values. It is best for data consistency (no duplications) and future proof flexibility. Denormalize for performance reasons later when needed (denormalization on client, materialized view etc.). You can increase readability by using char(1) instead of int as a primary key - D, W, M, Y.

Antonín Lejsek
  • 6,003
  • 2
  • 16
  • 18