Home | I.T. Management | Business Analysis & Project Management | VB.Net | SQL Server | About |

Database Design and Modeling
  1. Summary
  2. The purpose of a database is to capture the necessary data needs of the software application, research project, organization, etc. It is important to think conceptually as well as specifically when designing your database model. Entity Relationship Modeling is a good starting point in order to accomplish this objective. Entity Relationship Modeling helps to identify the tables, columns, fields and relationships that will constitute your database design. Once the overall design is defined, it is important to review the database model for compliance with the best practices of database design such as Relational Database Design, Third Normal Form and Referential Integrity. In addition, review your data model for how effectively it meets the necessary requirements for Data Integrity, Data Management and Data Availability.

  3. Master Data Management
    1. Keep in mind that the databases across your organization's enterprise should expose an interface to a Single Source of Truth1 about your most vital business assets, i.e. customers, products, orders. Data duplication across the databases of your organization's Information Technology Landscape inevitably leads to data attributes about vital business assets that are not synchronized across applications and databases. Consequently, your database design must take into account the organizational need to establish and maintain a Single Source of Truth about your organization's vital data. A Software Engineer's role is not to re-write every application in the Information Technology Landscape of the organization. However, Software Engineers are responsible for putting in place the basic building blocks, facilitating the business processes, and integrating with existing mechanisms in order to establish and maintain: a Single Source of Truth, Data Quality, and Master Data Management. Every information technology project must take responsibility for maintaining and improving Organizational Data Quality.
    2. An organization's vital data --such as customer data, product data, and order data-- must be actively maintained. The ability to cleanse and audit data must be built into your applications and databases in order to ensure the required levels of Data Quality.
    3. Vital business data requires an active approach to Data Quality and Data Governance.

  4. Technical Advice
    1. Use unique indexes in order to enforce unique value requirements across the rows of a column in a database table. Furthermore, use unique indexes --rather than a Primary Key-- in order to enforce a Unique Integer Identifier value across the rows of a table. In general, database tables should have only one Primary Key.
    2. Define your own Unique Integer Identifiers and maintain them from within your application or database. Re-numbering Auto-Increment Identities can cause a lot of manual work when, for example, restoring tables or replicating data across database servers. This is especially true when taking lookup tables2 into account.
    3. Design lookup tables that can be easily built into your application architecture by standardizing the names, and having only the Primary Key and the Text Value associated with the Primary Key in the table.
    4. Add an Insert Date Column and an Update Date Column to every table. Use a default value to populate the Insert Date Column, and update the Update Date every time an Update Statement is run against a table.

1A Single Source of Truth is a single data representation of a Data Entity that is the organization's best available information about the Data Entity.
2Lookup tables are tables that contain sets of values that apply to multiple records across database tables.
ID Month
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December

Copyright © Devon Manelski