This article walks you through the stages of working with tables using an example database for tracking customer orders. These examples will give you a solid understanding of how to perform the actions you need to be effective at building and modifying tables.
This article shows you how to:
- Plan your table design
- Create a table
- Edit a table
- Delete a table
Before you begin:
You must already be registered on MicroDB.co sign up for a free account
Plan your table design
In this section we’ll provide guidelines for planning your table design. You will learn about planning for your database that allows you to create tables and relationships between tables for better organization of your data. It’s ok to start with something small and add more tables later.
MicroDB is a very flexible platform for creating databases and tables that accommodate your needs on demand. Whether you choose to plan your solution upfront or create tables at a moment’s notice, rest assured that you can build what you need.
We believe there are many ways to build a great database application which is why our platform supports various approaches to building for any skill level.
While not required, planning for your database is a good practice which can help save you time later after your application is in use. We encourage you to start with a prototype database as a way to learn the tools and plan your production level database.
It is helpful to first think about the purpose of the application and what data will be put into the application based on your business needs. It’s ok to start with something small and add more tables later.
Example database
The objective of this example is to develop a simple system for tracking customer orders. Start by identifying the business entities involved and their relationships.
For this simple example we are using the following business entities to capture some basic information for a customer order.
- Customer
- Name
- Phone
- Shipping Address
- Shipping City
- Shipping State
- Shipping Postal Code
- Shipping Country
- Product
- Name
- Product Type
- Description
- SKU
- Price
- Product Type
- Label
- Order
- Customer
- Date
- Invoice number
- Tax
- Total
- Order Line Item
- Order Id
- Product SKU
- Quantity
These entities and respective attributes form our basic plan.
Create a table
In this section we’ll show you how to create the Customer table based on our simple example for tracking customer orders.
From the Data Dashboard, select an existing database, or create a blank database for this example. Now inside the database, click the Table List drop down to see your tables. On the Table List, click the Add Table (+) button on the right.
After clicking on the Add Table button, you are navigated to the new Table screen.
Next, give the table a name in the box at the top of the screen.
Add columns using the Add Column button, name the columns as needed. Specify a Data Type for the column, the default Data Type is set to Text which is the most common.
Pro Tip – think about the added benefits of data types during your planning.
During the build process of adding columns to a table, you can choose a data type that best suits the type of data stored in the column such as numbers, dates or simple text. During the planning stage, it helps to think about the types of data you need to store. Choosing data types offer the added benefit of auto generated user controls for data entry.
Keep in mind that once a table is created and data is entered into the record column, some column data types cannot be changed due to potential conflicts.
For example, if a column is a text field and contains data, then you attempt to change the column’s data type to a date field, the system will not allow it. This is a safety feature to prevent data loss. You can however, remove the data from the column and then change the data type and re-enter data.
When done adding columns click the ‘Save Changes’ button. The table is now ready for adding data.
Edit a table
In this section we’ll show you how to edit a table.
In your database, click the Table List to see all of the available tables. From the list of tables, click the icon in the Edit Table column to open the table in edit mode.
Here, you can edit the Table name, a column name or a column’s data type. Click the Save Changes button to submit or click Discard Changes to abort changes.
Note:
If a table contains data, some columns will not allow changing the data type. This rule prevents any inadvertent data loss. If the data type must be changed, it is recommended that the table column be free of data. An alternative approach is to create a new column with the desired data type. Then you can delete the previous column when appropriate.
If a column is linked to another table it cannot be changed until the tables are unlinked.
Delete a table
In this section we’ll show you how to delete a table.
It is important to consider that deleting a table cannot be undone and all data in the table will be deleted.
Please use caution when deleting tables.
In your database, click the Table List to see all of the available tables. From the list of tables, click the icon in the Edit Table column and open the table in edit mode.
Click Admin toolbar dropdown and select the ‘Delete Table’ menu option to remove the table from the database.
On the ‘Delete Table’ confirmation screen, click ‘Ok’ to drop the table or ‘Cancel’ to abort changes.
Note: Removing a linked table
If another table is linked to this table and the table to be removed has data, the data will need to be removed first. Then you must remove any linked columns before deleting the table.
To learn more see the article on Linking Tables.
Conclusion
This article is one in a series about tables. To continue learning please visit:
- Linking Tables
- Upload CSV File