Ranges of Data

A Range of data is defined as all available values that could be present for a given data field.  And while that seems pretty straight forward, there are always twists when you design databases.  Populating your new database with all the possible domains of data you quickly start to think about what all the possible values could be.

Working back at our example of a sandwich and more specifically the spices selections, options could be 1) salt 2) pepper and 3) both.  So the resulting data joined domains would have potential ranges of 1, 2, and 3.

No discussion of the concept of Range is complete without covering the enforcement of the values.  Most database platforms offer some type of unique constraint or foreign key relationship what can be used to ensure only the correct Range of values find their way into the field.  However, the employment of a constraint has consequences to the application adding data to the domain.  So what to do?

Essentially, as a database designed and developer the answer is pretty simple.  What the answer requires is a solid architecture that places a layer of stored procedures between the application and the tables.  As a rule, allowing an application access to base tables is generally bad, because it leaves the database open to corruption.  If something happens in the application, the resultant bad data could be disastrous.  Protect the data!  And add a layer of stored procedures ensuring the database is in control of the data itself.

Stored Procedures and the code that controls the CRUD operations against a table is the best answer for enforcing  a defined Range of data.  A backup could be a Foreign Key constraint, as long as the option to cascade deletes is not employed.  Again, control the name of the game, so using the code to enforce the values is best and the upside is a nice error message to the application, instead of a nasty constraint message.  If the CRUD operations are handled through stored procedures then the Foreign Key is only in place to help make the data look pretty and keep anyone with table access from making a bad mistake.

Happy Cooking — Data Chef out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: