Choosing the Right Database Solution

The purpose of this page is to help users decide on a database solution. If you already know how you want to proceed and just need to start using a database go to DatabaseSupport.

Four Scenarios

Choosing a database solution is not necessarily a straight-forward process. What seems simple can quickly become complicated as the data starts coming together and new requirements become apparent. That said, there are basically four scenarios requiring a database.

1. Tracking simple data in a central location
2. Tracking simple data in a database
3. Tracking complex data with an application created by a user
4. Tracking complex data with an application created by CLASSE-IT

Tracking Simple Data in a Central Location

Some data does not actually need a database, it just needs a central location. Other example is a spreadsheet that doesn't use calculations, just used as tables.

Requirements

If the data set is small enough and doesn't require advanced searching and filtering, using the CLASSE Wiki (where you are reading this) is a great solution. Anyone you choose could work on and view the wiki page. Wiki Tables are simple to edit and easy to create. Web browsers can search the list quickly, and multiple wiki pages can be searched via the search box above.

Examples of this kind of data set would be a contact list that is not expected to grow to hundreds of records or a place to keep notes, ideas, manuals, or instructions for a project.

Solution

You are already using the wiki while reading this. You can immediately start by creating a wiki page of your own where others can view and contribute. See WikiIntroduction for more details and consider Wiki Tables as well.

Tracking Simple Data in a Database

In this scenario there is simple data scattered in isolated locations such as spreadsheets, text files, and maybe even paper that need to be consolidated into a centralized and backed up location.

Requirements

• The structure of the data is in flux, because users are still gathering data.
• One or more simply structured tables can capture all the data.
• No or very few relationships between tables required.
• One or only a few people will enter data at a time.
• No requirements for people who cannot edit the database to view its data (e.g. needing to publish data on a web site).

Do I need a relational database?

If you answer yes to most of these questions, you probably need a relational database:

• Do you require more than a handful of tables?
• Do you find yourself repeating data within tables?
• Are there obvious connections between the tables?
  • Authors and Publications
  • Equipment and Locations

Solution

Users who come close to matching the requirements above who also have experience with FileMaker or Microsoft Access, will find LibreOffice Base is probably the best choice. We do not recommend Access or FileMaker, both of which lock your data into their own database format among other issues. Base, however, can connect to a MariaDB database, which is open source and widely supported by programming and scripting languages.

Tracking complex data with an application created by a user

Some users just want to build their own application that requires a back end.

Requirements

The user has the following:

• time
• technical ability (proficient in a programming language)
• desire

Solution

Users falling into this scenario know what they need and just require CLASSE IT to create the database instance for them. Although any programming language can be used to create database applications, multiple groups within CLASSE have standardized on Java web applications developed using netbeans and running in a GlassFish application server. Please see GlassFish for help getting started.

Tracking complex data with an application created by CLASSE-IT

In the case of a complex database CLASSE-IT can also create an application working with users who do not want to create their own application or who only are able to work on specific parts of it.

Requirements

• Willingness to work with CLASSE-IT as they develop the application.
  • forms and reports would be created by CLASSE-IT
• Users have roughly determined the data structure
  • what will be tracked is more or less known
  • how the data is related is more or less known

Solution

In this situation CLASSE IT can assist in building an application. We have built several Java applications with database back ends already, for example.

For users familiar with FileMaker, Access or Base, this will be a different experience. CLASSE IT would work with you to create the forms and views of data that you would have created yourself. If the data is still in the early stages of organization, using Base to determine a baseline of structure might be the best way for the user to begin. Since those solutions would be creating structure and adding data into a MySQL database, a custom solution could easily pick up where they leave off.

The end-product would have the following features:

• A web application that can be publicly accessible or restricted to the lab network
• Multiple users can edit or view data at once.
• No user set up required; just log in to the web page.
• Security in place to restrict access to all or only portions of the site.
• Further custom solutions available as your requirements change
• Open source - all source code would be available on our GitLab site to view, download, copy, fork, etc.
• Scalable - a relational database designed to scale up as new data requirements arise