Intro to Databases¶
What is a Database¶
Tip
If you'd like a more detailed description of the things that distinguish databases from spreadsheets, you might want to check out this tutorial 🚀
The term database can be used to refer to any system to store and manage information in a structured and efficient way. For example, a library might be considered a database - storing information in both physical and electronic forms using a structure like the dewey decimal system, and providing efficient ways for library patrons to find resources they need.
But we'll talk specifically about computer databases here. You might create an informal database on your computer by storing your files in certain places and in ways that make them easy for you to find. If you're a language worker, you may already be quite familiar with software that includes a database someone else has designed and built. Some examples of important products built on databases that language workers might be familiar with are SIL's FieldWorks Language Explorer, also known as 'Flex'; the Max Planck Institute's Elan; the Indigenous Languages Digital Archive, or 'ILDA'; and the Mirama software suite.
Tip
It's very common to refer to computer databases as "DBs".
The database component of these applications is the system by which all of the material you enter into them is saved and stored; and it's also the part that allows you to query this material to create various kinds of reports. When you use these programs, you probably never see the database directly, because you are interacting with the database through a series of forms and page displays (these elements are sometimes referred to as a frontend) that make it extra easy for you to use the features of the database for what you need.
When you learn about databases, you're learning about the (usually hidden) internal workings of these and many other applications that let us store and retrieve information on our computers.
Why Use a Database¶
Designing and implementing a computer database is tricky and technical, and there are a lot of ways for you to store and retrieve information on your computer that aren't nearly so complicated. You could, for example:
-
put all of your information into a single file or set of files using applications like MS Word, Apple Pages, Apple Notes, or Google Docs. You can then use a combination of file names, folder organization, and built-in search functions on your computer (like 'find' in Windows explorer) to retrieve the information you want.
-
put information into spreadsheet form using applications like MS Excel, Google Sheets, or Apple Numbers.
Tip
No matter what, of course you always need to be sure that your files and folders are backed up and safely stored so that when your computer breaks or the internet is down you can still access your materials, Right?
Right?
Please reassure us that you're careful about this, OK?
These are easy ways to organize and store information, and may well serve all of your needs very well! If so, there's no need to create a database.
But what if you want to share these materials with other people - either just for them to view the materials or for them to be able to add or modify the materials - in a way that preserves the content and organization of the information?
...you might consider making a database!
And what if you want to make sure that any changes made to the information - by you or anyone - are made in a way that ensures that your information and its organization does not get messed up by those changes?
...you might consider making a database!
And what if you want to be able to use that information and its organization in order to build fancier language technology (even technology that will also need a frontend component)?
...you might consider starting with a database!
Info
Learn more about a very important benefit of databases, transactional integrity, here.
OK What Kinds of Databases Can I Create¶
There are many databases of many types, but we'll limit our discussion here to just a few important ones.
Relational Databases¶
The first relational databases were invented in the 1960s and 70s, along with the computer language SQL, structured query language, pronounced 'sequel'. Examples of commonly used freely available relational database applications are SQLite, MySQL, and Postgres (among many others).
Note
Our tutorials and workshops use SQLite, pronounced 'sequel lite', because it's probably the most beginner-friendly of these tools. Don't let that fool you, though - SQLite is a powerful and useful choice for a relational database designer!
Relational databases are constructed as sets of tables that can be joined to each other. Each table is comprised of a set of columns, like 'id', and 'originator' such that every row is a separate record, as in the example table below.
Note
Our example tables draw from material in the Coeur d'Alene Online Language Resource Center which we have permission to share. We've modified 'originator' information for the sake of the examples, and if we've introduced errors in the process of building these tables, they are our responsibility and not the fault of the Coeur d'Alene language programs!
| id | originator |
|---|---|
| 1 | Audra Vincent |
| 2 | Michelle Clark |
| 3 | Lawrence Nicodemus |
Because a relational database is a collection of tables, let's look at an example of another table from the same database - this one a table of words.
| id | root | word | english | originator_id |
|---|---|---|---|---|
| 1 | a | a | hello. | 1 |
| 2 | bc | buts | boots. | 2 |
| 3 | bs | stbiiso | dragonfly. | 3 |
| 4 | cč | tsech | thunder. | 3 |
The 'words' table contains a column called 'originator_id'. The value in that column is going to match the 'id' column from the originator table. That's how the database knows that the two tables are related.
Info
Actually, the joins in relational databases are built on a fundamental system of assigning every row in every table a primary key, (here represented as 'id') and then being able to refer to that value as a foreign key in another table (as we've shown the `originator_id' column in our second table above), We can tell you more about keys here. We discuss joins here.
Since these two tables are joined (they're related) using a column from each, we can easily query the database and get back selected information from each table, displayed in a way that makes sense.
We can ask for all of the records in the 'words' table, along with the names of the originators from the 'originators' table, based on the value of the 'originator_id' column.
| root | word | english | originator |
|---|---|---|---|
| a | a | hello. | Audra Vincent |
| bc | buts | boots. | Michelle Clark |
| bs | stbiiso | dragonfly. | Lawrence Nicodemus |
| cč | tsech | thunder. | Lawrence Nicodemus |
What's most important to remember here is that relational databases store information in sets of tables that are related via joins,which themselves use keys.
Relational Databases and Structured Query Language (SQL)¶
Relational databases have their own computer language. It's called Structured Query Language, abbreviated SQL (and usually pronounced 'ess-kyu-el', sometimes 'sequel'). This language is very useful and important to learn as you create and use your database. You can learn a little more about SQL here.
Nonrelational Databases¶
More recently, developers have created various other kinds of databases that don't use SQL and traditional table and join structures. Collectively, these are sometimes called NoSQL Databases.
NoSQL Databases covers a wide range of different sub-types, including key-value stores, document stores, graph databases, and more. Perhaps the most useful type of NoSQL Database systems available for use by language workers are document stores. These systems store information in large, structured, text blocks (sometimes called blobs), and so are well suited to store large numbers of whole documents, and allow efficient and easy search within the text of those documents.
The Indigenous Languages Digital Archive, or 'ILDA' application uses a very popular database product Mongo DB, for example.
We aren't (yet) developing training for NoSQL Database design, but if there's interest, we could!
How'd we do?¶
Do you feel like you understand waht databases are and some of their basic properties? If so, great! If not, we hope you'll ask more questions.
You can contact John, Gus, or Amy and we'll do the best we can to help!
At this point, you might go back to the index 🚀 or learn more about why you might want a custom database you build yourself 🚀