Constituents of a good relational database design

Question

What are the constituents of a good relational database design?

I'd want to ask this question in a bit unusual way, lets see if anyone likes the idea. Also read on to see how it's not a duplicate of the question about DB design best practices (or any other question for that matter). The idea is that as a result of voting we will have constituents ranked by their perceived importance.

I'd appreciate if you could:

1. Specify 1 (one) constituent of a good relational database design per answer, give multiple answers if you must.
2. State the idea in a fewest number of words possible on the top of your answer.
3. Then give possibly two illustrations:
   • benefit of having the constituent
   • cost of not paying enough attention to the constituent

Please avoid (again if possible) posting duplicate constituents, if something you'd like to highlight is already submitted please choose something else, unless, of course you can give a better illustration. I'm sure there are at least two dozens highly important things one needs to incorporate into the design.

Answer 1

It depends on the person, but for me 3rd normal form does.

Answer 2

It depends completely on the purpose of the DB but I find the first step is NORMALIZATION

Answer 3

the database always runs fast

Answer 4

First normal form

In day-to-day practice, and ignoring the issue of whether NULL is a value, this winds up meaning "use a primary key".

The benefit of having a primary key is that rows are unique and have a value by which they can be looked up as an O(log n) or O(1) operation.

Without a primary key, rows are not unique and many common, desirable operations using the database are therefore far slower and/or more difficult.

Answer 5

easy to maintain/modify without refactoring

Answer 6

Using Primary Keys

Primary keys ensure there is a unique id for querying, updating or deleting. It also allows for joins to work faster (as this column is indexed)

Answer 7

The flexibility of NOT using 3rd normal form when necessary. This usually occurs when mindlessly adhering to normalization and ending up with 50 indexes and multiple level joins.

Answer 8

Second normal form

2NF, the short version: do not store any information in a table if that information is dependent on only part of a composite key. This is one level of saying that information should "go where it belongs", i.e. if you have a Car table, a Driver table, and a CarHasDriver table, you don't store information that's purely about the Car in the CarHasDriver table.

The benefit is having a coherent point of reference for where to find data, avoiding overly complex or ambiguous queries, avoiding data duplication, and generally not having your database be a big mess.

The cost of not using 2NF is the absence of those benefits. :)

Answer 9

My favourite rule is that you have to weigh technical complexity against business benefit.

An example is a webshop that starts to normalize its database. Say they move the billing and invoice address from the customer table to a separate address table. This adds big technical complexity:

• The customer editor will now have to support a random number of addresses
• Sql queries with address require extra joins
• A new relation between orders and addresses is introduced
• Updates to the customer might deadlock with updates to the address table; you have to take care of the order of update statements
• ... and so on ...

The business benefit of this technical complexity is (usually) low; the separate address table enables no new functionality.

The price you pay is that your competition will spend this time on things that customers appreciate, and leave you behind in the market.

Answer 10

Using Appropriate Datatype & datasizes

Using a varchar(50) to represent a code that is always 5 characters long is wrong, also using a varchar(20) to represent a city name or country name is wrong too. Please size the varchar columns correctly. Also use the appropriately sized int or float, if a value is always going to be less than 32,768 use an int (not a bigint, as I've seen done)

Answer 11

Third normal form

3NF, oversimplified, is not storing information in a table that is unrelated to its unique or potentially unique keys. For instance, if you have a Car table, a Driver table, and a CarHasDriver table, you do not store CarAndDriverLastSeenStateAbbreviation and CarAndDriverLastSeenStateFullName in CarHasDriver, because now you're using CarHasDriver to store non-key information about States.

3NF is somewhat "2NF but more so", and the benefits are an extension of 2NF's facilitation of having a single, coherent point of reference for information on a particular topic.

The drawbacks of not using it are similar, though minor violations of 3NF are generally considered much more forgivable (such as storing State as a 2-letter state code instead of a key into a State table).

Answer 12

Application Determined Indexing

The database's non-primary keys should be constructed according to the queries which will be made against it by the application, potentially with compound keys designed to optimize the most speed-critical queries.

The benefit is that the application runs much faster without unnecessary cost.

If you ignore this, either you have an under-indexed table that slows down the application, possibly drastically, for no good reason, or you have an over-indexed table that wastes disk space and slows down INSERTs and UPDATEs for indexes that no one is actually using.

Answer 13

Object Representability

The entities in the database should be chosen so that they correspond naturally to object representations that developers working with the application would choose to work with. For example, if you have a Car table, its columns should make equal amounts of sense as the properties of a Car object, and if your developers' model of the system has Users who may or may not be Drivers, it's probably a good idea to work parallel to that at the database level.

The benefit of this is that it aids greatly in using object-relational mapping to interact with the database, otherwise makes database-application integration flow more easily, and in practice tends to push you in the direction of using 2NF and 3NF, which is all to the good.

The risk of not doing this is making extra, unnecessary work for developers and getting a reputation as the coder-unfriendly database guy. :)

Answer 14

Let me creatively misinterpret this as, "What are the constituents of good RDBMS (i.e. the software that manages a database in a relational manner) design?" In no particular order:

Independence of logical and physical layout. I should never have to duplicate an attribute or join two relations in my logical layout in order to speed queries; I should just tell the physical layer to do that, and have it ensure that data never gets out of sync transparently to me. The only time I should hear about it is when a change to the logical schema is incompatable with the existing physical schema, prompting me to make the appropriate changes. Thus as an admin, I can turn a row-oriented storage format into a column-oriented storage format or vice versa, or even maintain both, and my clients see nothing but faster queries. (The same goes for optimization hints, by the way: those should be entirely separate from the logical query.) As a poster previous to me mentioned, the physical design and changes should be automated to some degree, based on query history or whatever.

Abolishment of "primary keys." A candidate key is a candidate key, and should not have any logical priority over another. If you want one set of attributes indexed, but not another, that goes in the physical specification, not the logical. A relational query language that is, more or less, the relational algebra. Well beyond the various bits of small cruft one expects to accumulate here and there (and I'm not even counting the physical storage hints in this), SQL has some very basic things wrong with it that make it non-relational, and very hard to use if you're trying to use a relational model. For years now, my standard technique for formulating complex queries has been to spend twenty minutes specifying it in some fairly pure form of the relational algebra, and then forty minutes trying to translate that into SQL.

Real types, specified as easily as real types in my programming language of choice. If I have complex numbers, they should have an attribute in a relation of a single complex number, not two separate attributes as real and imaginary. And functions should be defined for types, not defined for certain types and also-does-god-knows-what for different types that come along. Yes, I'm talking about abolishing NULL, though not at all about losing the capability for it. I don't mind creating a type of "all signed integers expressable in 32 bits, plus 'unknown' and 'out of range.'" But when I call the "average" function on a set of those values, I don't want it handing me a number and snickering behind its back saying, "Ha ha! I wonder if what I just made up is similar to the answer he'd come up with?" I want it to say, "I know average for integers; for what you've got, you'd better tell me what you think it means."

(An example: "sum(3, 2, unknown)" is not "5", nor is it "unknown". It's, "at least 5."

Get rid of or fix these "ORM" layers. Going and spending a lot of CPU cycles to have a relational DBMS instead of a hierarchial or network DBMS, only in order to spend yet more CPU cycles to make that relational DBMS look like a hierarchical or network DBMS later on is not only a waste of CPU, but also leads to errors in one conversion being multiplied in another. Give me direct relational queries in my language. (I'm not a big MS fan, but I give them props for LINQ, which is a step in this direction.)

This question hit a sore point, so there's my rant.

Answer 15

Using Referential Integrity (foreign keys)

One of the mainstays of a good DB design is to make sure relationships between data tables and/or data tables and lookup tables are "secured" by means of referential integrity (using foreign key relationships). This helps ensure data integrity, avoids "stale" or wrong data.

Answer 16

Boyce-Codd Normal Form (BCNF)

This is the more complete, improved version of Third Normal Form. OK, to be honest I always have to look it up to remember what it means exactly, but if you look at the example in the Wikipedia page, it clearly makes sense that there should be a Rate Type table.

The advantage is that it removes a potential update anomaly that 3NF by itself would allow.

Answer 17

Using Natural Primary Keys

When possible, each entity should have an identifier that is used by the user community to identify that entity. If you can trust that your data entry people will get that identifier right, declare it as a primary key, rather than inventing an ID column that you then need to keep private.

Declaring a natural primary key will prevent duplicates where it really counts. It also prevents missing data where it really counts. Using a surrogate ID column as a primary key misses the biggest reason to have primary keys: to prevent bad data from undermining entity integrity. If your data lacks entity integrity, ensuring referential integrity will only win half of the battle for you.

For tables that represent many-to-many relationships among entities, this may mean composite primary keys. That's a small price to pay in efficiency compared to the loss of integrity that you might get by not declaring such a key.

Note that there is nothing fundamental in relational theory that makes one candidate key better than another as a choice for primary key. But if you know how your users use the data, you'll be able to choose a primary key among the candidates.

The index you get when you declare a primary key is only a secondary benefit. The prime benefit is data integrity.

Answer 18

Never store more than one piece or type of information in a field. Comma delimited lists should be related tables. Email should not store general notes as well as emails. (It doesn't really work well to send an email to "Talk to his secretary.")

Answer 19

Simple, Artificial, Opaque Primary Keys

Give all tables a single-column (simple, as opposed to compound) primary key that is artificial (constructed by the system rather than being a natural part of the data) and opaque (encodes no semantic content other than its function as a key), also known as a surrogate key. The easiest way to do this is usually to use an automatically generated serial primary key, though hash and GUID keys are often nearly as easy to use and have the minor security benefit of not being serial.

Benefits include retrieval by primary key being simpler and faster than with a compound key, the ability to use keys in content like Web forms without exposing potentially sensitive information about the records they index, and uniformity of interface in knowing that any given table you work with will have this feature.

If you do not use single-column primary keys, this makes your database more cumbersome to use. If you do not use artificial, opaque primary keys (or at least a unique artificial opaque key somewhere in the table), I certainly hope your database isn't touched by the Web.

Answer 20

For an OLTP system, the primary key for all Entity tables should be defined as:

• simple (single column, basic datatype)
• anonymous (meaningless, carries no semantic meaning)
• immutable (value will never be changed once assigned)
• unique (no duplicate values allowed)
• not null (no nulls allowed)
• enforced by declarative constraint(s)

Some authors refer to these qualifications amounting to a "surrogate" or an "artificial" primary key.

I've not been burned by following these guidelines for a primary key for all "Entity" tables managed by an application.

I have witnessed application users and developers burned by not following these guidelines. Just as one example, I was called in to assist in a rewrite of a "pension" system which used social security number as a primary key for a pension. Payees receiving two or more pension payments had "dummy" social security numbers assigned. The users added handwritten note of the 'real' social security number on printed reports, and manual corrections were made when reporting to the IRS.

The database had to be modified, and a whole bunch of application code had to be changed, because the database and application had been designed around a "natural key" which was almost unique.

---

My personal preference is to give the column name "ID" to the primary key column of each table. I also prefer for the primary key values to be unique within the schema.

For a non-Entity table (that is, a relationship table, that resolves a many-to-many relationship between entity tables, and that has no attributes of its own and no possibility of child tables, then I will consider using the combination of the two foreign keys as a primary key. Such as "relationship" table is not mapped to an object in the application. Often however, I find that the relationship itself may have some attributes of its own, or there is a possibility that it will be a parent to another table. So, it doesn't hurt to go ahead and add a single column "ID" to the table, as well as the unique constraint on the combination of two foreign keys.

---

Of course, most Entity tables will also have a "natural key", which will consist of one or more columns or expressions. The individual columns and/or expressions that compose the natural key should be NOT NULL and the combination should be UNIQUE, and those constraints should be enforced by declarative constraints in the database.

Answer 21

Before you start designing a table make sure that you really understand what your data is intended to represent. The first rule of database design is "Know thy data." Make sure that you understand the logical data structure inherent in your data. As far as possible keep the physical structure the same as the logical structure. Don't deviate from following the logical data structure until you understand the price that you will pay, because there is always a price to be paid.