SQL | SQL Classes | SQL FAQ | Database Design Tips

Table of Contents: SQL Database Design Tips

• Business model first
• Identify domains
• Work from the outside in
• Identify schemata
• Use numeric keys
• Normalize

Get the business model and requirements done first. This should not involve any data modelling.

many people tend to forget the basic rule of computers: GIGO. Garbage in, garbage out. And most business requirements reflect this amnesia. Just because a spec weighs in at 50 kilgrams does not mean it is any good.

Most systems fail (or simply never go into production) simply because of a poor business specification.

A recent NASA study into the Voyager and Galileo projects showed that of the 197 critical errors, only three were programmer errors, the rest due to incorrect or unclear specifications. A study by JPL showed that two thirds of errors were due to incorrect or unclear requirements.

By keeping data modelling out of the issue, you simplify the business specification process, and also prevent shackling your data-modellers with decisions made too early.

By this I mean to look around for columns or groups of columns that will be commonly used around your site. Surnames, for example, should all be the same length. Addresses are an example of a group of columns that should be consistent, and with a set of packaged functions developed to deal with them.

Personal names are another common domain made up of a group of columns. Consider putting in a flag for the convention used when putting surnames together: Asian (surname first) or European (surname last) and having a packaged function to assemble the name for letters, et al.

Anyway, these columns and associated functions should be tested, documented, handed to a librarian, and the information on them propagated to all developers so they don't go around re-inventing the wheel.

A low-level cheap way of doing this is to have an 'include' file for SQL*Plus with rows like
define DOM_DEPT_ID NUMBER(4,0)
and start this from the script containing your create table statements, which would look like this:
create table DEPT (
ID &&DOM_DEPT_ID not null,
NM &&DOM_ORG_NM not null,
....

If you don't have SQL*Plus, think of using m4(1). In combination with make(1) you can regenerate table scripts automagically when domains change.

Start your data design from the outside of your still-nebulous data model.

You can tell which entities are on the outside: they do not depend on anything else. Many are code/description tables. These are usually the first to become clear as entities, so why not model them first?

Because these entities are simple, they are easy to implement. You can then quickly develop prototype screens/reports for these entities and populate them with real data.

These early screens, having simple logic, let you sort out the look and feel of your application, and get approval from the users. They are also ready for plugging in to the next layer of screens dealing with the more complex objects. They are also simpler to train the early users with, and you can let them put the real data in for you, giving them something to do, and making it look like you are achieving something (which you are).

Populating them with real data gives you a better chance of picking up problems with the next-most-complex layer of entities, be these design or implementation bugs.

Each step inwards is resting on a solid base - the more you get towards the centre of your data model, the more complex things get, but the references are already in place.

Oh, and if this is sounding like bottom-up programming, I make no apologies. I consider it building on solid foundations rather, infinitely better than hanging walls from a roof and then putting the floor in, and then the foundations.

As you are developing the entities, you should identify the schemata that own them. This is important, as if you are lazy, you can end up having to perform surgery later that is as delicate as separating siamese twins and you may have to junk quite a bit of your process model.

Do not use strings as part of the primary keys. These are likely to mutate, and besides, integers are quicker to compare when running down an index. (Most compilers generate code that compares numbers a word at a time rather than byte-by-byte.)

These integers should have no meaning and are probably generated using sequences.

Of course, you can hide these numbers from users, giving them pseudo-keys to play with.

If you think I'm wrong, consider the way Oracle® stores the information in its internal tables such as SYS.USER$, SYS.OBJ$ and SYS.COL$. Numeric keys. To find a column using the ninety bytes potentially required for schema, table and column name is grossly inefficient.

This approach also means that it is highly unlikely that your primary key will ever contain data that needs to be updated.

Normalize your data.

If you must denormalize your data, do so after normalizing and have very good reasons for doing it.

To normalize is to make more normal. Normal, in general usage can either express an 'average' or otherwise an 'ideal'. For example, we could say 'murders are normal in New York' versus 'murdering is not normal'. We could say 'the normal operating system is Windows' or 'it is normal to have an IQ of 100 or even less'. The concept of normality is much discussed in almost every first year philosophy course.

Similarly, normalization in databases is a philosophical, if not religious issue.

Normalization in databases is an expression of how you get to data - how much data redundancy there is and/or repeated subgroups in records. For a full discussion of normalization, see any good RDBMS text, notably C.J. Date's 'Introduction to Database Systems'.

I am a big fan of normalization. It makes modifying a design much easier. I'd certainly consider normalizing all your longs into a separate table with a unique id generated by a sequence number. (This makes your queries not hitting the long column more quickly, too).

Highly normalized data is also more adaptable to a changing environment. Your system, if normalized, allows extra information types to be put in without redesigning your data model and programs. This extra information tends to pop up a few months into production!

Home | Technical Schedule | Application Classes | Class Outlines | MCSE, MCDBA, MCSD Training | Microsoft .NET Programming | Cisco Classes | Linux, Unix, AIX | CompTIA Certification | Webmaster Training | Pricing | Locations | Financing | E-mail Us