Design a database model

May include but is not limited to: normalization, entities, entity relationships

Much could be said and has been said already about this subject. I don’t really think that I could show anything new, but here is some topology.

Normalization is the process of making an effective database solution. There are five normal forms, of which we’ll discuss the first four:

  • 1NF: quite basic rules, eliminate duplicate columns from the very same table (a rule which isn’t hard to implement) and separate related data into different tables, and identify them with a primary key.
  • 2NF: remove data that apply to multiple rows of a table from that, and create separate tables for them, linking to the predecessors with a foreign key. And of course, 1NF.
  • 3NF: the first two plus removing the columns which aren’t dependent on the primary key.
  • 4NF: the first three and a new definition for relations: a relation is in 4NF when it has no multi-valued dependencies.

Now the concept of entities. I must confess that the word entity is too abstract for me. I’d like to map English words to their Hungarian counterparts, but entity lacks one. So I can’t really describe what an entity is- I can only say that it is an entity.

In software architecture, you try to map the logic of the business to something you can code – the results are the business entities, let them be objects or tables in your model. The boring example: Customer is an entity, and it has multiple Orders, what is built from one or more Products and so on. So these entities relate to each other – mostly by the mean of foreign keys – as long as you’re mapping with SQL.

Entity relationships can be one to one – something isn’t so obvious to implement in SQL Server. I become aware of that when my Entity Framework model complained that a relationship I imagined as one to one is in fact one to many. The solution was simple: I removed the existing foreign key constraint, and created a new one which linked the two tables’ primary keys. One to one implemented.

One to many is really simple: any foreign key do it for you easily. Just create one between two tables, set the primary key to the master table’s primary key field, and create a dedicated non-primary key field in the slave table.

Many to many is interesting. It needs three tables: the two you’d like to link as usual, and a helper one, which links the two. So you place two fields in the helper table, and create two foreign keys in it to the primary key fields of the original tables. Sounds messy, but it’s very easy, and it gives you many to many. A real world example for the purpose: in a blogging application, each post can have multiple tags, and each can be mapped to multiple posts – making life and searching easier.

Further Readings

Database Normalization Basics

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