A good (and fast) software depends in good part on a good database design. This is why it is important to understand the basics.
Database
A database is meant to organize your data in a way that is easy to search and minimize duplicates. All software uses some type of database to store either the configurations or the data itself. If you have used Excel, you created databases in the simplest way possible!
Databases contain Tables which in term contain Column (or fields*).
For example, a online store database will have these tables (in the database) to take orders from the customers:
- customer
- order
- order_item
These tables will contain columns and relations.
To start designing a database, you need to divide your data into many subject-based tables so that each fact is represented only once.
You can use a visual database designer like MySQL Workbench to help you!
Tables
A tables is a group of column representing a concept (model). For example, a customer table is a group of the columns name, address, phone, etc..
Again, if you use a spreadsheet like Excel before, you did conceptualize your idea into columns.
The difference between most spreadsheets APIgoat, is that you need to take advantage of relationships to build a interesting App.
The APIgoat table definition can be found here.
Relationships
Table relationships are what makes your data connected to each other. It also helps remove data redundancy (duplicate data).
It also extends your concept (model) to more than one table.
For example, in our customer, order and order_item table, if the order table doesn’t relate to customer, order would have no customer…
How to do it
To relate two table, you need to have a key. A key is a usually unique column present in both tables. In most cases, these matching fields are the primary key.
This will enable the database to “connect” data with the same key value once you define the relationship which foreign key.
In APIgoat, defining a foreign key is easy.
Columns (or fields)
Columns are the smallest part of data. The real nomenclature would be fields.
For why we use column when we should use field, see the terminology.
A column define the type of data that will be put in it. It is important to decide on the right type of data. For example, a decimal type colunm will refuse to save a text in it.
The more accurate your column typing is, the faster your database will run!
Here is APIgoat supported types.
How to define a Column in APIgoat’s Schema.
Records
Records are actual data. We have been talking so far only about defining a structure to put data in.
In a spreadsheet like Excel, or LibreOffice Calc, a record would be a row.