mssqltips logo

SQL Server Logical Data Model



By:

Overview

Before you dive into creating your tables and writing any code, you should initially dedicate time up front to develop a logical data model that attempts to achieve at least 3rd Normal Form.

Explanation

Logical data modeling is where you will define the business entities which will eventually become tables, it's attributes (columns) and how the entities inter-relate with each other. This initial step involves the process of normalization. Normalization is where you break down your entities and remove redundancy from them, eliminate any repeating data from them, and where eventually each entity has a primary key where the attributes all rely solely on this key. This process is usually performed leveraging data modeling tools which can visualize the model.

There are 3 basic normalization ("normal") forms:

  • 1st Normal Form (1NF) (a primary key is identified, attributes reduced to their smallest component, and repeating data is identified and removed)
  • 2nd Normal Form (2NF) (non primary key attributes not dependent on the entire primary key are removed)
  • 3rd Normal Form (3NF) (remaining non primary key attributes that have dependency on other non primary key attributes (i.e. transitive dependencies) are removed

There are additional normal forms such as Boyce-Codd normal form (BCNF), 4th normal form (4NF), 5th normal form (5NF), etc. I generally find that achieving 3rd normal form is adequate. Your mileage may vary.

Why go through all this effort to normalize? Normalization can help reduce the amount of data in your tables leading to fewer I/Os to retrieve data for queries as well as memory savings in SQL Server's buffer pool. It can also help prevent issues with data modification anomalies where data is updated in one column but perhaps missed in other columns. Lastly, referential integrity, such as foreign key validation, becomes easier to maintain.

Depending on your design, there might be a performance penalty to normalizing to 3NF and, especially, beyond since many JOINs could be required to satisfy a query request. This shouldn't preclude you from initially designing to at least 3NF. If a query issue is later identified, you do have the option of introducing controlled redundancy into your design. In this process, you explicitly introduce redundancy to solve a performance issue. However, don't automatically add denormalization to a design; instead allow a real performance situation to dictate it.

Additional Information


Last Update: 9/10/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools