SQLite - Designing Database

This article at OpenGenus outlines the process of developing a database utilizing an external file, while also showcasing the tables' data structure and type. It is intended to provide a clear and concise approach to database design.

Table of Contents:

1. Introduction
2. Understanding Data Types and Type Affinity
3. Table Constraints
   • PRIMARY KEY
   • FOREIGN KEY
4. Column Constraints
   • NOT NULL
   • UNIQUE
   • PRIMARY KEY
   • CHECK
   • FOREIGN KEY
5. Altering Tables with ALTER TABLE
6. Table Creation with Schema File
7. Conclusion

First open your dababase and check with .schema for tables. It will show nothing because we have not created any tables and this is what we will be doing in this article at OpenGenus.

sqlite3 database.db
.schema

To create a table use command CREATE TABLE.

CREATE TABLE "ridres" (
   "id", 
   "name"
   );

---

First let's learn about the data, that we are about to handle.

DATA TYPES : All data is labeled with a type for simplification and categorization, which enables computers to handle data more efficiently. By identifying the data types provided by the user, the computer can quickly search through them. Proper labeling of data improves the efficiency of data transfer and reception.

• NULL
• INTEGER (0 to 8 bytes integer)
• REAL
• TEXT
• BLOB : Binary large object, which stored images, audio files, videos, or other binary data files. It is used to handle data that cannot be labbeled with a data type as text or numbers.

Type affinity (storage of columns)

Type affinity is used to define data types of columns in a table. It ensures that the data stored in a columns stays of the same type.

• TEXT - for character strings, including JSON, XML and other text-based data
• NUMERIC - for numeric values, including integer and floating-point data
• INTEGER - for integer values
• REAL - for floating-point values
• BLOB - for binary data, such as images, audio or video files.

Delete a table

DROP TABLE to delete a table

DROP TABLE tablename;

Table constraints - tells the table to have a specific data types in the column.

• PRIMARY KEY
• FOREIGN KEY

PRIMARY KEY("id") : A primary key is assigned to each row in a table to uniquely identify it and allow easy access to its content. By using a primary key in combination with foreign keys, all tables in a database can be connected. This creates a reliable and efficient way to access and manage the data.
FOREIGN KEY("rider_id") REFERENCES "rider"("id") : It is a field or many fields that is used to refernce the primary key of tables in a data. It basically only contains refernces to all the primary key in other tables to form a connection between tables.

Columns Constraints

It defines the rules, regulations and restrictions on data that can be stored in particular column of a table.

• NOT NULL - specifies that the column cannot contain NULL values.
• UNIQUE - specifies that all values in the column must be unique.
• PRIMARY KEY - specifies that the column is the primary key for the table.
• CHECK - specifies a condition that must be true for all values in the column.
• FOREIGN KEY - specifies that the column is a foreign key that references the primary key of another table.

Altering the tables

ALTER TABLE keyword is used to update or alter the columns of table.

ALTER TABLE "visits" RENAME TO "swipes"
ALTER TABLE "swipes" ADD COLUMN "type" TEXT;
ALTER TABLE "swipes" RENAME COLUMN "ttpe" TO "type"

Table creation through schema file

Now let's create or design tables. We have seen above that we need to add or modify tables with ALTER TABLE commands but that is quite tiresome, not-efficient and cannot be done on large scale. So the solution is to create a Schema file that contains the table and datatypes that we need to define in a single file. remember to have .sql as a extension of this file, but you can have different names.

.read schema.sql

Use this command to read the schema file and create the tables you desired with all the command in one place. Your tables are ready then start using all the filtering or editing commands as usual.
You can change the name of schema.sql to any name but it should have .sql extension.
Type .tables or .schema to check if the table is made or not.

This is what it looks like before, without any data or table.

And after using the command it should create you the desired tables.

With this article at OpenGenus, you must have the complete idea of how to design a table in SQLite.