Database Design

Database Design

  • Overview
  • There is a number of factors that need to be considered when designing a database.  Firstly, is the data for storage related?  If no, perhaps several databases should be considered.  What type of database is best suited to my application?   Hierarchical databases are still widely used especially in applications that require very high performance and availability such as banking and telecommunications.  One of the most widely used commercial hierarchical databases is IMS(DL/1).  However, relational SQL databases are now more widely used and come in various guises.  Our consultants are willing and able to offer guidance on all aspects of selection.

Database Design in Power BI

Database Design in Power BI

  • Normalization
  • In simple terms, normalization is the process of reducing the repetition and redundancies of data in a database.  This will help maintain the integrity of your data.  A database before normalization is one that has not been broken down logically into smaller, more manageable tables.

    In our experience, raw (pre-normalized) databases can consist of just a couple of tables, each table housing data with hundreds of columns!  By promoting logical database design (the process of arranging data into logical organized groups), data can easily be maintained and referenced.  Thus, response times for real-time transactions are critically upgraded, which ultimately leads to an improved end-user experience.  The same of course is true for large scale batch processing applications.

    When normalizing a database, primary key and foreign key constraints are normally used to help manage the integrity of the data.

  • Data Structures
  • Many of the applications we design and code run many times faster than average because the data structures have been taken into account.  This is especially important when processing large datasets where collate and control-break processing are required.

  • Optimization / Indexing
  • Performance tuning a database is not an exact science, it comes with time and experience.  However, there are a number of factors that by default we consider.

    Adding indexes significantly improves data retrieval times, however they slow updates.  Indexes will also enforce referential integrity constraints when a unique or primary key is specified.  For small tables, the use of indexes does not result in any performance improvement.

    Understanding the type applications accessing the data is critical for efficient database design.  Ensuring well-structured queries are included in applications is also vital.  Small lookup tables, may also be loaded into memory at the start of an application.  This will significantly reduce subsequent disk I-O when processing the primary dataset.

SQL Example Query

SQL Query Tuning