There are many things that you have to take into account when designing your own bespoke database. They can be split into two main categories:
The business requirements – What function the database serves
The technical requirements – How you are going to make it all work
In this section we are going focus on the technical aspects of your database design.
It is important to establish how the database is going to be accessed by your users. In many organisations users will only have access the database over the LAN, typically from the same office, whereas in other organisations users may have access to the database over a VPN, or terminal services, perhaps they will be using laptops and handhelds in which case you must ensure that your new database will run efficiently both locally and/or remotely and that its performance will not be affected by slow or unreliable Internet links
Your information is valuable and it’s your responsibility to protect it from loss, theft or misuse by protecting it on the LAN, using encryption, and restricting server access from the Internet.
Securing your live data is important but so is recovery if the unexpected happens. You need to make sure your backups are securely stored and working. Do not store your backups using the same storage as the live database, always store multiple offsite copies, and remember that even geo redundant cloud infrastructures can be compromised which may result in a total loss of data. Read the “Code Spaces” story.
In addition to securing the database as a whole you also need to consider further reducing your risk by applying different access levels, and restricted permissions to certain parts of the database.
As part of the database design process you need to perform stress tests to make sure that your database will run without problems given the anticipated number of users, existing amounts of data and factoring in your anticipated growth rate. There is no point testing your database with a few hundred records and a couple of users if the production database is going to host millions of records and hundreds of users in the near future. You have to load large volumes of data and generate multiple requests to simulate simultaneous user access and use CPU, memory and disk I/O performance counters to work out resources consumption ratio per user and per MB of stored data to establish what load on the hardware you should expect when you go live so you can you’re your hardware appropriately.
User environment requirements
When deciding what technology to use in your bespoke database design you should include all the possible usage scenarios. Do users need to access your data from tablets and smart phones? Does the database application need to be cross-platform? Will it need to run on MACs as well as PCs? Will everybody use the same functionality, or will there be a desktop application for office users, and a web application for customers? Be careful what you wish for. If you don’t really need a tablet or web application there is no point in overdesigning the database simply because it would be ‘nice to have it’. This will make your project more expensive and more complex to maintain. Remember, you are designing a business tool not a social networking website. It has to do the job, no more, no less.
The main cost with a bespoke database will be the development time. However, there are likely to be additional costs that you will need to take into account such as database server licenses, commercial add-ons and components, terminal services licenses, MS Office licences.
As your business grows so will your business requirements. Whatever technology you choose it should be flexible and scalable to allow for future growth. Choose a vendor that is likely to be around in the next 10 or 20 years and one that is actively investing in their products. Microsoft and Oracle are good examples.
Support and maintenance requirements
A successful database project does not end when you go live that is just the beginning. Your database will start growing and evolving with your business. User ‘buy in’ is very important. It is crucial that the database is reliable, easy to use and, most importantly, it helps users with their daily tasks from the get-go.
You should be prepared to address any new requirements and bug reports quickly otherwise users may lose faith with your new system and may be reluctant to come back to it.
Make sure that you set up a staging environment as part of your database design plan. So you can test database changes and improvements without affecting production servers.
Appoint a member of staff who will be responsible for communication with users, and proactive in documenting their problems, approving, and driving through change requests.