Getting rid of "Magic" numbers in SQL Server

Question

In code one can specify globally accessible constants/enums/etc once that can then be reused through out the application. This gives the ability to use meaningful names like 'Mazda' instead of numbers like '2'.

We would like to do the same with in our SQL Server stored procedures, but are not sure of the best way of implementing this.

E.g for the following tables (The proverbial car schema):

Car       ManufacturerId 
350Z       1
Hilux      2
Yaris      2

ManufacturerId   Name
1                Nissan
2                Toyota

So instead of writing

SELECT * FROM Car WHERE ManufacturerId = 1 -- Nissan

We would like to write something like

SELECT * FROM Car WHERE ManufacturerId = @Nissan

One restriction we have is that we cannot rely on the Manufacturer.Name staying the same for the life of the App. We did think about having a column "Code" that never changes and the joins are looked up like this:

SELECT * 
FROM Car c 
    INNER JOIN Manufacturer m ON c.ManufacturerId = m.ManufacturerId
WHERE m.Code = 'Nissan'

I'm a bit hesitant on this as it uses an extra join and string comparisons that can be misspelled.

What is the best way for this to be accomplished without having to declare the variables in every Stored Procedure?

Answer 1

We've done this a couple ways:

• User-defined function that returns the "magic number" by name.
• Use a nchar(4) column instead of int column and come up with slightly less inscrutable codes. So Nissan would be "NISN" instead of 1. A little nicer.

Answer 2

You don't need to store numbers in the database. There are DB admins out there who still love the idea of 'natural keys'. Instead of using an ID, they simply use a natural key that uniquely defines the manufacturer. Often this can lead to multiple primary keys. You might have to have a 'Mazda' 'Canada' as a unique identifier for manufacturer.

Personally, I dislike this method, and prefer to have a unique ID for every identifiable object stored in my tables. This means creating a new table which is a lookup. 2, 'Mazada'. Then you can create a view that pulls 'Mazda' in where there is 2 in the database, then run the query against the view. SELECT * from v_cars where maker = 'Mazda'