Best Practices for Designing Databases and Tables
6 minutes to readTables are the foundation of your database applications. Well-structured tables are easy to understand, perform fast, and require little or no maintenance. Above all, they translate into robust and high-performing apps.
See the below best practices for designing high-quality tables:
Choose the right data types and the appropriate number of fields in a table
For optimal application performance, create tables that contain less than 150 fields. Caspio does not limit the number of fields per table. However, we suggest testing your application performance when you exceed the recommended number.
When designing a table for your application:
- Enter meaningful names for the table fields to understand the purpose of the data. For example, Title_Short, Title_Long, and Description instead of Field_1, Field_2, and Field_3.
- Choose the right data type for the field to control the type of data that you can enter in a field, the field validation method, and the sorting method.
For more information, see Data Types. - Add a field that you set to Unique and select one of the following data types: Autonumber, Prefixed Autonumber, Random ID, or GUID to uniquely identify all records in a table and relate them to data in other tables.
- Enter text in the Description field to comment on a field or describe how to use it.
- Add a field and set it to Timestamp with the Stamp On Insert option to identify when a record was inserted into the table.
- Add a field and set it to Timestamp with the Stamp On Update option to identify when a record was updated in the table.
- Add a field and set it to Text (255) to store numeric values such as ZIP codes, phone numbers, or Social Security Numbers.
For more information, see Creating Tables.
Normalize data in tables
Data normalization can improve the database performance and eliminate redundant data. Searching and sorting of data become faster, and it is easier for database administrators to maintain and modify data in the tables. To normalize data in a table, identify fields that might contain recurring values, put the fields in a child table, and refer to the table from the parent table.
For example, you can create an application that uses data about customers and their contacts. If you design one table to store all the information, the fields might contain recurring values.
Instead, design multiple tables to store customer information and contact information, respectively. Add a primary key per table to identify each record in a table and relate these records to data in the other one.
A correctly normalized table needs to have a primary key. A primary key is a field in a table that uniquely identifies each record and relates these records to additional data stored in other tables. The values in primary keys must be unique, compact, and stable. In other words, they should contain the fewest possible fields and should not be changed.
When assigning primary keys to a table within a relational database, you can use a natural key or a surrogate key. To help you decide which one is more appropriate for your database, consider this outline:
- A natural key is a key that consists of attributes that exist and are used by people in the real world. For example, each employee inside a company usually has an email address that is unique to them.
- A surrogate key is an artificial value with no business meaning that is automatically generated by the system and used to identify a record in a table. For example, every order has an order ID. However, the most frequently used type of surrogate key is an incrementing integer. Surrogate keys are useful your records do not have natural keys or when natural keys might not be unique (for example, dates of birth).
By separating customer information and contact information, you remove data redundancy.
Define relationships between tables
If your data is relational, create formal relationships to benefit from automatic join in Views and if desired, from referential integrity. By configuring relationships between the related tables, you can work with them as if they are a single table. For example, you can perform cross-table queries or create Views. When you configure a relationship, you can choose a field from the parent table and display it in the associated field of the related table.
To protect the records from accidental deletion or update, you can enforce referential integrity for the tables that are related.
For more information, see Database Relationships and Relationships Settings.
Use Views to filter or combine data
Views are virtual tables. They don’t have their own data, but they are used to retrieve data from underlying table(s). You can use Views to filter data from a table or combine related data from multiple tables. A single View can reference up to 16 tables. However, you might want to limit this number for optimal application performance.
When you create Views, you can keep confidential data secure by authorizing groups of users to access only specific data. You can also create a single query against the View instead of several queries in multiple tables.
For more information, see Views.
Use lookup tables as a data source for dropdowns, listboxes, and radio buttons
When you have lists of records that you want to display as options in a Dropdown, Listbox, or Radio Buttons form element, create a lookup table to be the source of the form element. Lookup tables help you to maintain data integrity in your database and you can reference them in multiple DataPages. Examples of common lookup tables are the list of countries, states, provinces, or priorities.
For more information, see Lookup Tables.
Use Triggered Actions
Triggered Actions allow data manipulation, calculations and sending notifications by constructing application logic with a visual interface. They are executed on specific events (inserting, modifying and deleting the records) to perform actions on data in your tables.
For example, a customer buys a product online via a Caspio-built application. A Triggered Action inserts a record about the product into a data table with orders and deletes the record from an inventory data table. At the same time, Trigger Action sends an email to an order picker who can prepare the product for shipping.
For more information, see Triggered Actions.
