Do you know the ingredients of the good relational database design?

I'd wish to request this in a little unusual way, allows find out if anybody likes the concept. Also read onto observe how it isn't a replica from the question about DB design guidelines (or other question for your matter). The concept is the fact that consequently of voting we'll have ingredients rated by their perceived importance.

I'd appreciate should you could:

  1. Specify 1 (one) constituent of the good relational database design per answer, give multiple solutions should you must.
  2. Condition the concept inside a fewest quantity of words possible on top of the way to go.
  3. Then give possibly two illustrations:
    • advantage of getting the constituent
    • price of not having to pay enough focus on the constituent

Please avoid (again if at all possible) posting duplicate ingredients, if something you want to highlight has already been posted please choose another thing, unless of course, obviously you can provide a better illustration. I am sure you will find a minimum of two dozens very important things one should incorporate in to the design.

First normal form

In day-to-day practice, and disregarding the problem of whether NULL is really a value, this ends up meaning "make use of a primary key".

The advantage of getting a principal secret is that rows are unique and also have a value through which they could be researched being an O(log n) or O(1) operation.

With no primary key, rows aren't unique and several common, desirable procedures while using database are thus far reduced and/or even more difficult.

Third normal form

3NF, oversimplified, isn't storing information inside a table that's unrelated to the unique or potentially unique secrets. For example, for those who have a Vehicle table, a person table, along with a CarHasDriver table, you don't store CarAndDriverLastSeenStateAbbreviation and CarAndDriverLastSeenStateFullName in CarHasDriver, because now you are using CarHasDriver to keep non-key details about States.

3NF is sort of "2NF but much more", and also the benefits are extra time of 2NF's facilitation of getting just one, coherent reason for reference for info on a specific subject.

The disadvantages of not utilizing it offer a similar experience, though minor violations of 3NF are usually considered a lot more forgivable (for example storing Condition like a 2-letter condition code rather than a vital right into a Condition table).

Second normal form

2NF, the short version: don't store any information inside a table in the event that details are determined by only a part of an amalgamated key. This really is one degree of stating that information should "go where it goes", i.e. for those who have a Vehicle table, a person table, along with a CarHasDriver table, you do not store information that's purely concerning the Vehicle within the CarHasDriver table.

The advantage is getting a coherent reason for reference for how to locate data, staying away from excessively complex or ambiguous queries, staying away from data duplication, and usually not getting your database be considered a large mess.

The price of not using 2NF is the lack of individuals benefits. :)

Using Referential Integrity (foreign secrets)

Among the cornerstones of the good DB design would be to make certain associations between data tables and/or data tables and research tables are "guaranteed" by way of referential integrity (using foreign key associations). This can help ensure data integrity, eliminates "stale" or wrong data.

Using Appropriate Datatype &lifier datasizes

Utilizing a varchar(50) to represent a code that's always 5 figures lengthy is wrong, also utilizing a varchar(20) to represent a town title or country title is wrong too. Please size the varchar posts properly. Also employ the properly sized int or float, if your value is definitely likely to be under 32,768 make use of an int (not really a bigint, as I have seen done)