Note: This was originally written as an example of explaining a technical concept in simple terms. The form it was submitted to did not allow for graphics to be embedded. Placeholders have been added to the document, as an example of where graphics would be placed.
Behind every computer program, software package or mobile app, exists a database in one shape or form.
A database is storage place for pieces of information the software can draw upon to display back to the user. Databases can be written to, read from or even updated.
How you design the database will determine the ease in which the software can retrieve the pieces of information and whether or not the data is stored correctly.
What’s Under the Hood
Databases are made up of things called tables. A table holds rows of information. Each row holds a set of information for only one person or item. Every row runs into columns. Each column contains individual pieces of information for that person or item.
EXAMPLE: A table of names and mailing addresses will be made up of rows, with each row containing the address of each person. Each row will run into columns that contains a first name, last name, house number, street name, city, state, and zip code.
<screen shot or infographic here>
Simple vs. Complex – Examples
Some databases are simple and some very complex.
If you’re using a simple computer game, like “Solitaire” then the database behind it may be very simple. The only pieces of information it may be storing are the:
- Card Value: (number or letter)
- Card Suit: (Spade, Club, Heart, Diamond)
It may store one or two more variables and it will likely only have one table of information to draw from.
If you’re using something like an online banking software, then you’re dealing with a complex database design. You have several tables of information. You will most likely have a table for:
- Customer Addresses: First Name, Last Name, Street Address, House Number, City, State, Zip, Phone Number, Foreign Number (Yes or No), Email address
- Customer Log In Information: Username, Password, Last Login Date, Last Login Time, Failed Login Count, Last Failed Attempt
- Checking Accounts: Type (Personal or Business), Interest Rates, Minimum Balance Required Amount, Monthly Fee Amount
- Loan Accounts: Type (Personal or Business), Interest Rates, Approval Code, Date Opened, Past Due (Yes or No), Past Due amount, Fees, Minimum Payment, Date Closed, Payoff Amount
- e-Statements: Enrollment Date, Disclosure Date, Number of Statements Available
This is not all of the tables of information you would have for this type of software. As you see, the more functions that are performed within a software, the more tables you have to have. The more tables you have to have, the more important it becomes to make sure the database is designed so that each table can be linked together in logical ways.
How Databases are Linked
Databases are linked together with something called a “Primary Key.”
A primary key is a specific piece of information that is unique to every person or item in the table.
Example keys could be an account number or a random number assigned by the database. Databases can have “Secondary Keys”, but the primary key is the absolute unique identifier of the person or item in question.
Linking databases together properly, allows the software to retrieve information related to a person or item by referencing the key we choose in the design stages from more than one table at a time.
In the next chapter we’ll take a look at Keys and how to decide which ones to use when.
Database design should be done before any actual coding takes place. All of the relevant factors should be taken into consideration and the information organized into logical groups that could be placed into tables. Each individual piece of information should have it’s own column. Databases can be simple or complex and should contain unique identifiers to each person or item being referenced.
<Optional: A call to action or an offer from help from the company promoting this content could be placed here.>
Table: a space that holds all of the information logically grouped together that has a common theme like an address table, color table or a login information table.
Primary Key: a unique identifier to the person or item being referenced. Primary keys cannot be duplicated or changed once assigned in order to work properly. Keys are used to link tables together.
Secondary Key: another key used in addition to the Primary Key in order to retrieve information on a person or item.