As I’m sure you are aware, our favorite programming system, REALbasic, has built-in database capabilities. These capabilites are frequently misunderstood and underutilized. My goal while writing these articles is to help you improve your database knowledge so that you can determine when a database is appropriate for your application.

What is a Database?

There are many examples of things that can be considered a database. Some are:

• An e-mail program
• Address Book
• Recipes stored on index cards
• TV listings
• Baseball Schedule

What is it that all these things have in common? To use the formal definition of database: They are each a “collection of persistent information”. Persistent is relative of course, but generally think of it as “sticking around long enough to be useful”.

A database can be stored in many ways. In its simplest form, you could simply use a text file to maintain your database. Or, you could instead use a REALbasic binary file for more flexibility. Each of these types are considered “flat-file” databases. Flat-file databases have their uses, but they are not what we will covering here.

Relational Databases

REALbasic’s REAL SQL Database is a relational database. This is an extremely powerful mechanism for storing and retrieving data. It actually has its roots in mathemetical set theory, but we’ll try to stay away from that! Simply, relational databases allow you to create tables containing various types of data (columns). These tables can then be related to each other in order to store and use all kinds of information. Because of its power and flexibility, just about any information can be efficiently captured in a relational database.

For the first few articles, I will be focusing on general database concepts and topics that can be applied to any relational database. After we have covered those sufficiently, then we see how to put that knowledge to use with REALbasic.

Database Basics

The fastest way to get started is with an example, so let’s say we want to store the schedule for our local Softball League. This league consists of 8 teams, each with a coach, and each team plays 10 games. We’ll call the teams the Ducks, Penguins, Bears, Tigers, Lions, Wolves, Dolphins, and Marlins. There is a lot of information in these two sentences, so let’s review:

1. We need to store the 8 teams, so we’ll make that a table, called team. We also need to store the team name and the team coach, so we’ll add two columns: name, coach.
2. We need to store the games, so let’s also make that a table, called game. For the game, we need to capture the date, time and location as columns.
3. Lastly, we need to create relationship between the teams and the games. Since each game needs two teams, we can add two relationships to the game table: one for the home team name and one for the away team name. These are columns called hometeam and awayteam.

That’s it. With this table structure (also known as a “schema”), we can now store our Softball League information. Keep this example in mind. We will be referring to it often as our skills progress.

Tables

A table is a container of information. This is stored as rows and columns. The rows contain the information and the columns describe what the information is. In our Softball League example, we have 2 tables: team and game. This brings us to our first Database Standard: All table names are singular.
When working with databases, it is important to have standards. Feel free to alter the specifics of my standards in your own work, but do use your standards consistently.

Columns

The information in every table is described using columns. When you describe a column, you also need to give it a datatype. Datatypes vary depending on the database being used, but all databases have at least a number, string, date and time. In each table, one or more of the columns must be designated as the “Primary” or unique identifier for a row. Review Figures 1 and 2 to see how this all looks.
A key way to verify your table design is by using sample data. Figures 3 and 4 describe some sample data for team and game. After reviewing the sample data, you may discover additional information to capture. In the case of game, it seems like we might want to save the game score as well. I’ll leave that as an exercise for the reader.

(1 votes, average: 4 out of 5)

 Loading ...