I have a table CUSTOMERS containing customers and an additional table defining types of customers (with only two records, Business and Residential). There's a relationship between them defining the types of customers but integer type PK/FK for CUSTOMERS_TYPES/CUSTOMERS is used. Is it clever to use CHAR(1) PK instead of integer (e.g., "B" and "R" instead of 1 and 2)?
Moreover, when I list my customers it is clear enough to present to users only "R" and "B" (FK fields) instead of making an additional join to retrieve real customer type names?
For your example, I would definitely use the CHAR(1) primary key. That is, assuming you are certain there will be only the two values. If it should happen that you need a third, be sure to choose a different letter. There is no good reason to use an integer key here, when the letters B and R have the added advantage that you will not need to do the join in most of your queries. Good call.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading