Database Normalization

For those IT professionals that know me know that I have a passion for data and data architecture.  I also do many other IT things (for example I have a small web development company).  One of my things that drives my passion for data is that it is the basis that drives efficiencies in almost all areas of IT and business.  I find it very frustrating when we pay a bunch of money for an application and install it and discover that the data model is so poorly designed that the application performs poorly or it is difficult to get good info out of it.  For this reason I put a lot of stress on data models on ever IT project that I work on.

The processes where by a person would go through tuning a data model is called normalization and denormalization.

Normalization is the process where you organize data using different steps or levels.

First Normal Form (1NF) contains the following steps:

• Organize data set(s) into data sets specific content (e.g. data relative to persons and then data related to address).
• Identify each record in each data set with a unique ID (primary key).
• Remove duplicate columns with the same information

Second Normal Form (2NF) contains the following steps:

• Meets 1NF criteria.
• Remove subsets of data that create multiple rows (e.g. an employee is in multiple divisions, create a table that links the employee to the division with foreign keys).
• Create a a table and foreign key relationships for data that is duplicated across data sets (e.g. the account, contact and order data sets all have address fields.  The address info should be put into a table and then a foreign key to that table should then be stored in the account, contact and order data sets).

Third Normal Form (3NF) contains the following steps:

• Meets 2NF criteria.
• Any data element that could be repeated in multiple records for a single field should be put into its own table and then referenced via foreign key (e.g. The university name in a list of national students should be put into a university data set and then a reference should be used in the national student list).

Boyce-Codd Normal Form (BCNF) contains the following steps:

• Meets 3NF criteria.
• Create separate tables for data that is dependent upon a value other than the primary key (e.g. a table with address information might have street address, city, state and zip.  The city and state are dependent on the zip code which should then be the key to another table that contains the city and state info to reduce duplication of data). 

There are other Normal forms though most processes do not typically go beyond 3NF.

Denormalization is the process of violating Normal Forms for the purpose of performance.  For those not familiar with the concept of "Query Cost" there is a performance hit for each table joined together in a query.  For this reason there are reasons to violate these forms to improve query performance.  One common example is to include a full address in the address table instead of just having the street address and zip code and another table with the zip code to state and city associations.

I hope that others find this information useful.

Oracle: Check to see if a value is numeric

One of the functions that MS SQL has that Oracle does not have by default (which can be frustrating at times) is a function that checks to see if a value is numeric. After playing around a bit and digging through oracle in line function documentation I finally found something that would pass for this functionality.

I wanted something that would provide a result of 0 (is not numeric) or 1 (is numeric). Below is a SQL statement that I used during th e"playing" around process.

SELECT
    CASE WHEN nvl(length(translate(trim('111d'), ',.+-0123456789', ' ')),0) > 0 THEN 0 ELSE 1 END NonNumeric,
    CASE WHEN nvl(length(translate(trim('1111'), ',.+-0123456789', ' ')),0) > 0 THEN 0 ELSE 1 END Numeric
FROM DUAL

The idea behind this code is that first we take a string value (or field) and use the translate function to remove all of the numeric values and characters that are used with numeric values with nothing. Around that value I used the length function to return the length of "translated" value. I then evaluate the length. If it is larger than zero then I return a value of 1, becuase any value with a length greater than zero has a non-numeric character in it. Otherwise it returns a value of 1 (which means that the value is a numeric value).

I attempted to put together syntax to create a function called ISNUMERIC and pasted it below. I do not have an Oracle database on this server or have access to an Oracle in this environment to be able to run this code to make sure that it executes properly. I believe it will work though it has not been tested.

CREATE OR REPLACE FUNCTION ISNUMERIC (VALUE IN VARCHAR2)
    RETURN NUMBER
AS
BEGIN
  IF nvl(length(translate(trim(VALUE), ',.+-0123456789', ' ')),0) > 0
  THEN
    RETURN 0
  ELSE
    RETURN 1
  END IF
END ISNUMERIC;