Good Software Design When Business Rules in Code Rely on Database Lookup Values

Sometimes there is situations where I need to take a value from a database lookup table similar to:

Id | Description
----------------
1  | descriptive text for 1
2  | descriptive text for 2

The Id vlaue is often stored in a variable:

private int DB_LookUp_Id = GetIdFromDB();

This DB_LookUp_Id value is then used to perform validation or other business logic like:

if (DB_LookUp_Id == 1)
    return true

This code will break if the Id value changes from, say 1 to 3

What is a good way to eliminate this problem?

Thanks

Answers


Have a code-based key alongside the database ID that is static, we tend to use either Guids or enumerated values for this:

Id | Key | Description
----------------
1  | 1   | descriptive text for 1
2  | 2   | descriptive text for 2

Then in code you could have:

enum DescriptiveTextParts
{
    Part1 = 1,
    Part2 = 2,
}

if (DB_LookUp_Key == DescriptiveTextParts.Part1)
    return true;

Not sure if this is "best practice", but it works for us. Database primary keys do not always need to be known or used by the application, though most of the time they are.

Alternatively, if it's all just text, ship it all into a resource file. This gives you localisation, compile-time benefits, and performance benefits. To the detriment of database access to resource strings (pretty much no access), and ability to release new strings without re-compilation.


The best practice that I have seen needs more semantic information in the database. Put all reference data is put in a database and multiple tables are used to normalize the data.

Imagine an online ordering system that needs to know what state a customer is in and it needs to know what states are valid shipping locations.

1) You might have a database tables called STATES

  KEY | NAME
 --------------------
  AL  | Alabama
 --------------------
  AK  | Alaska
 --------------------
  PR  | Puerto Rico
 --------------------

2) Create a reference data object to wrap a row in the database.

class StateReference {
    string key
    string name
}

3) Create a relational table of shipping locations, called SHIP_LOC, and keyed by state code ('WA', 'OR, etc.)

  KEY | VALID_SHIP
 --------------------
  AL  | true
 --------------------
  AK  | true
 --------------------
  PR  | false
 --------------------

4) Now create a method like this:

bool isValidShippingLocation(StateReference object) {

     return dbLookup('SHIP_LOC', object.key)
}

The first advantage of this approach is that it moves hard-coded values out of the code and into a database, where they are much easier to find and easier to change. Also, referential integrity between tables can be enforced and that helps guarantee valid results.

Furthermore, reference data now has semantically meaningful names. Eliminates guessing about what a '1' or code 'X' in the source code means.


Need Your Help

Cluster quality measures

matlab cluster-analysis data-mining evaluation

Does Matlab provide any facility for evaluating clustering methods? (cluster compactness and cluster separation. ....)

Getting the number pixels in width and height inside an UI Object?

objective-c ios object pixels

Currently I do something that looks a bit fuzzy because I am dealing with points with my UI Object but what I want to do is get the width and height of the UI Object (in my case a UIImageView) in p...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.