I am not a database expert nor am I conversant with database design, but I have had a couple of run-ins with databases that are either badly designed or use a platform that doesn’t meet the needs of the end-user. I would like to think this is a "lessons-learned" synopsis.
Why do you need to make the database meet the needs of the end-user? A case in point is the fact that I developed an MS Access database, initially for my own use and then for general use (everyone in the office was using a windows based computer). Unfortunately, my new boss uses a MAC and is therefore unable to use the database directly on their computer, because MS Access is not made for MACs. It would be far easier if the database had been developed in a way in which it could be accessed through a browser.
There are certainly ways around this, but it just becomes a lot more complicated.
I believe that some of the basic ideas in creating a database include:
The developer needs to decide on the best platform to use that will both be useful for current purposes and will continue to grow and be useful in the future. Some of this is a guessing game (what does the future hold?), but sometimes you can find companies posting end-of-life (EOL) information for their products, so you probably don’t want to use software that will be EOL’d in the next few years. Next, you will need to determine, with the help of all of the end-users, what they feel is the purpose of the database. This will inform your next step, which is determining what data should be incorporated for current needs and what might be needed for future needs (for instance, currently the end-user may feel they need nothing more than some basic demographic information, but they will most likely want a little more detail in the future. So it is far easier to collect that data in the beginning than to go back and try to collect it later on.) On a more technical level, you will need to figure out which data types make the most sense for each piece of data collected. Data types should probably reflect what reports will likely be requested/generated and how the end-user will wish to access the data.
Once that task is done, the next step would be to code the back-end, or underlying structure, of the database. Previously you selected which database tool to use, one of the criteria should have been how conversant you are with said software, the more the better. I can’t repeat this enough, but document, in the code itself and as a separate document, everything you do so it is immediately understandable. During the coding, keep in mind that this should be something of which you can be proud because a messy database not only generates 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 instance, a user’s name and demographical information do not need to be repeated. Any data that is referred to multiple times can be stored in a separate table (think zip codes – which are available in most forms online).
While developing the user-facing portion 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 to which you may not have given much thought. Lock this portion down tight, so the end-user cannot accidentally (or otherwise) make any changes. Although this makes coding more difficult, it is better if the user has choices as to how they wish to either visualize the data or enter new data.
You may wish to create some “canned” reports. This will make the database ready to use “out-of-the-box." Make the majority of your reports based on queries. This makes them more usable. Reports that are not static can be used in multiple ways, so less overall reports. For instance, two of my reports based on queries are
A report on research program members that initially asks for the research program and uses that to output a list of members of that program.
A report that asks for the start date and the end date and generates a report based on that data in each entry in the database
These types of reports should always indicate the parameters used to generate the report.
Do NOT skip this step. It is important to test everything before deploying your database. Testing is really a topic unto itself. That said, the database needs to be tested constantly during development. This means every time any change is made, you need to test it to make everything is still working. When you think it is done, it is time to test it on other devices using other credentials (just because it works on the development machine with the credentials used by the developer, does NOT mean it will work for anyone else).
I will start off by confessing that I am obsessed with backup. My first question when I speak to people about computers is “Do you backup?” and my second question is “How many backups do you keep, and where?” I encourage everyone who uses a MAC to use at least two Time Machine drives (minimum). With databases, it is even more important, especially if they are used by multiple people. To start off, during development it is wise to backup often and use versioning (I even wrote a script to do this for my database back-end and it date stamps the backup with yyyymmdd_hhmmss_<database name>.<database extension>. When you are ready to roll out 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 database backend using the 3-2-1 backup scheme (3: Create one primary backup and two copies of your data. 2: Save your backups to two different types of media. 1: Keep at least one backup file offsite). Don’t forget to set a task to automatically backup the backend. Just as important, set up a method to track who changes what data (unless your database software does this for you).
Document EVERYTHING It is extremely important to keep this in mind during the entire process. The documentation should be extensive and thorough enough that someone could walk in and understand everything that the database does and why.