Some Database Design Ideas | Exchange of experts
Veteran of computer systems, malware removal and ransomware related topics. I have been working in the field since 1985.
I’m not a database expert and don’t know database design, but I’ve had some issues with poorly designed databases or using a platform that doesn’t meet the needs of the database. ‘final user. I would like to think of it as a summary of “lessons learned”.
Why do you need to adapt the database to the needs of the end user? A concrete example is the fact that I developed an MS Access database, initially for my own use, then for general use (everyone in the office was using a Windows computer). Unfortunately, my new boss uses a MAC and therefore cannot use the database directly on his computer, because MS Access is not made for MACs. It would be much easier if the database had been developed in such a way that it could be accessed via a browser.
There are certainly ways around this, but it gets much more complicated.
I believe some of the basic ideas in creating a database include:
The developer must decide on the best platform to use which will be both useful for current needs and will continue to grow and be useful in the future. Part of this is a guessing game (what does the future hold?), but sometimes you can find companies that publish end-of-life (EOL) information for their products, so you probably don’t want to use a software that will be EOL’d in the next few years. Next, you will need to determine, with the help of all end users, what they think the purpose of the database is. This will inform your next step, which is to determine what data needs to be incorporated for current needs and what might be needed for future needs (e.g. currently the end user may think they only need some basic demographic information, but you’ll probably want a little more detail in the future, so it’s much easier to collect this data at the start than to go back and try to collect it later.) On a more technical level, you will need to determine which data types make the most sense for each piece of data collected. The data types should probably reflect the reports that will likely be requested/generated and how the end user will want to access the data.
Once this task is completed, the next step is to code the back-end, or the underlying structure, of the database. Previously you have selected which database tool to use, one of the criteria should have been your proficiency with that software, the more the better. I can’t repeat it enough, but document, in the code itself and as a separate document, everything you do so that it’s immediately understandable. While coding, keep in mind that this should be something to be proud of because a messy database not only throws errors, but those errors can be very difficult to debug. Keep in mind that all data should be kept in manageable chunks that make logical sense. For example, a user’s name and demographic information need not be repeated. Any data that is referred to multiple times can be stored in a separate table (think postal codes – which are available in most online forms).
When developing the user part of the database, keep in mind that it will be most useful if it is easy to use, clean and easy to manipulate. Try to make it as easy as possible for the end user to ask questions that you may not have given much thought to. Lock this part tightly, so that the end user cannot accidentally (or otherwise) make changes. Although this makes coding more difficult, it is better for the user to have a choice as to how they want to view the data or enter new data.
You may want to create “predefined” reports. This will make the database ready to use “out of the box”. Make the majority of your reports query-based. This makes them more usable. Reports that aren’t static can be used in multiple ways, so fewer global reports. For example, two of my query-based reports are
- A research program membership report that initially queries the research program and uses it to produce a list of research program members.
- A report that asks for the start date and end date and generates a report based on that data in each database entry
These types of reports should still specify the parameters used to generate the report.
Do not skip this step. It is important to test everything before deploying your database. The test is really a subject in itself. That said, the database should be continuously tested during development. It means every time any change is made, you need to test it to make sure everything is still working. When you think it’s done, it’s time to test it on other devices using other credentials (this is NOT because it works on the development machine with the credentials used by the developer that it will work for someone else).
I’ll start by confessing that I’m obsessed with backup. My first question when talking to people about computers is “Do you do backups?” and my second question is “How many backups do you keep and where?” I encourage anyone using a MAC to use at least two Time Machine drives (minimum). With databases, this is even more important, especially if they are used by several people. For starters, during development it’s wise to back up often and use versioning (I even wrote a script to do this for my database back-end and it timestamps the backup with yyyymmdd_hhmmss_. When you are ready to deploy the front-end of the database, back it up first, then back up the back-end (structure and data) Once deployed, keep incremental backups of the back-end of the database. database using the 3-2-1 backup scheme (3: Create a primary backup and two copies of your data. 2: Save your backups on two different types of media. 1: Keep at least one backup file off site). Don’t forget to set a task to automatically back up the backend. Equally important, set up a method to track who is modifying what data (unless your database software is doing it for you).
Document EVERYTHING It is extremely important to keep this in mind throughout the process. The documentation should be complete and thorough enough that someone can go in and understand everything the database does and why.