Database planning

Effective database planning means that your software is capable of managing and consolidating all the data generated and relied upon by your business. A good database plan will allow your organisation to develop a clear structure for the way in which data is stored and managed by every person or application using it. This process will also help identify a base for future needs and could provide the framework for both an immediate and long term growth strategy.

With your input, we can produce detailed plans and database diagrams to represent the structure of your database. Our goal is to optimise the performance of the database by determining the exact amount and type of information required. Planning databases in this way will also identify any necessity for default values and constraints on the data being stored, as well as the potential relationships between certain areas of the database.

Relational databases allow for multiple types of data 'objects' to be stored with keys and indexes to link related data across multiple tables. For example, one table holds customer details and assigns each customer a numeric ID, while another table holds order details, including the customer ID - removing the need to duplicate customer details if there are multiple orders from the same customer. This approach is referred to as data normalisation and in its simplest form, attempts to minimise data redundancy. Normalised database structure ensures concise database architecture with the elimination of unnecessary data duplication, as well as the potential for optimised database operations and balancing workload between the databse server and the web server, for example. Having the correct plan and structure for your database also has arguably the most important benefit; greatly reducing the potential for human error, making the data flowing through your bespoke software robust, reliable and scalable.

Our database engine of choice is Microsoft® SQL Server® for all of our new build projects. Similar to many other enterprise level database engines, Microsoft® SQL Server® allows the planning process to go a step further towards managing your data, as opposed to just storing it.

We try to take advantage of these features wherever possible. Some of the features we commonly recommend in our database plans:

• A feature that our clients find more useful than any other is 'Full text catalogs'. This feature allows the database engine to create a detailed store, or 'Full text index' of desired textual content within your database. This full text index enables textual searching based on language, proximity of words, inflectional forms and thesaurus-based expansion sets, and can rank results on their accuracy. This is true search engine capability.
• 'Stored procedures' are one of the most commonly used functions provided by modern relational database engines. Stored procedures are scripts written in the native language of the database engine and housed in the database that can perform almost any operation, from the simple to the extremely complex. They execute only within the database, but can be invoked from both inside the database (as part of a trigger, for example) and outside the database from connected software, like a web or desktop application.
• Using 'Views' as part of your database structure, the database engine can cache predefined queries that join data from multiple sources into a single table, encouraging faster access to commonly requested data.
• Including database 'Triggers' into your plan allows for the provision of programmable functionality that is executed automatically upon an action occurring within the database. For example, your content management database maintains a virtual path for each folder and file within it. Updating a folder's parent ID (moving it from one folder to another) triggers the database to recalculate and reassign the virtual path for each folder and file beneath the one moved. The content management system we built for this site does exactly that.
• Defining relationships between tables adds an extra layer of assurance that your data maintains its integrity. In simple terms, this means that changes made to data in one table can cascade down through related tables. For example, assigning a new stock keeping unit (SKU) code to a product in your products table will cascade down to your orders table; updating any orders awaiting processing that contain the same product.

Click here for more information about our expertise in database specific development. (https://www.pasilda.com/expertise/development-for-sql.htm)

For more information about database planning please contact us (https://www.pasilda.com/contact/).