When designing the structure of a database you often need to group data together in specific areas or 'Entities'. Creating relationships between these 'Entities' allows you to link organisation data into practical structures to improve the data capture and querying process.
For example, a ‘Customer’ is one type of 'Entity' and so is a ‘Sales Invoice’. The ‘Sales Invoice’ will link to the ‘Customer’ using the ‘Customer Id’. Designing the relationships can be challenging to get right.
Relationships between entities enable applications to access the data quickly and easily whilst reducing data duplication and complexity.
At Westwood Rock we work hard to provide an optimal data structure for your data and the 'Entities' that they represent.
Database normalisation is a process designed to structure and organise a database with the minimal amount of data duplication, redundancy and to increase data integrity.
There are different levels of normalisation with different requirements, protocols and benefits to ensure there are no anomalies during insert, update or delete operations. These can be achieved through separation of duplicate data into their own 'entities' and using foreign-key linking.
Normalisation goes hand in hand with the Database Relationship design process and by understanding where data duplication may occur you can create additional 'Entities' to fit the requirements.
When designing a database, you also need to think about the reporting requirements and what information you will need to record. For example, its more prudent to include separate Net, Tax and Gross fields, rather than just Net and Gross, as you can perform sums and aggregates independently without additional calculations being needed (Gross - Net = Tax).
You may need to think about the granularity of your data and how often its captured so the data can be used to report a specific metric and performance indicator.
When designing reports, you need to factor in the audience (who’s going to see the report), what data is to be displayed, will it be a static report or using a Business Intelligence tools like Microsoft Power BI, Board or SAP BusinessObjects.
We have the skills and experience to help you get the most out of reporting and ensuring your database structure meets your reporting needs.
A 'Data Warehouse' (DW) is a system for storing your fast-changing transactional data in a database and structure designed for super-fast reporting and analytics.
This often involves flattening the structures established during the Relationships and Normalisation phases and combining data from different entities into a single table in the Data Warehouse database.
While transferring the data from the transactional system into the database warehouse, the data can be shaped to fit the requirements of the reporting system. Calculations can be run and the results stored in the target tables, which reduces the amount of work needing to be done by the database server during a reporting run, increasing processing speed.
Data Warehouse's are a great fit for analytics and Business Intelligence (BI) tools such as Microsoft's Power BI, Board or SAP BusinessObjects because the data is available to the tools without any linking or calculations to be done. The tools can also use repeating data, such as a customer's account code or state, to group and summarise data points more easily.
Using Database Relationships, Normalisation and Data Warehousing to store and produce large datasets of analysable data, Artificial Intelligence (AI) and Machine Learning (ML) can process the data and do some really exciting things.
Using a Data Warehouse to structure sales transactions, for example, could allow a trained machine learning model to predict the sales patterns of your customers and AI could make suggestions of products that they might be interested in.
Alternatively, AI and ML could look at atmospheric or weather data and determine environmental patterns and anomalies in the data, allowing companies and customers to prepare for or mitigate at those times.
Designing the Data Warehouse for use with ML and AI is something that Westwood Rock has experience in and can help you work through.
