Creating a Database Structure
Creating a Database Structure
Database structure and design
-
A database is a structured collection of related data.
-
Database structure refers to the organisation of this data.
-
It involves designing how the data will be stored, accessed, altered, and managed in a database system.
Tables and Records
-
In a database, data is stored in tables.
-
Each table is made up of rows, also known as records, and columns.
-
A record is a single set of related data, such as all the details about one customer.
-
A column, also known as a field, holds a specific piece of data about each record, such as a customer’s name.
Creating Tables
-
When creating a database, start by designing and creating the tables.
-
The table structure cannot be changed easily once the database is in use, so it’s important to get it right.
-
Decide which fields will be needed for each record. All records in a table must have the same fields.
-
Assign each field a specific data type, such as text, number, or date/time.
Primary Key
-
Each table should have a primary key field - a field with a unique value for each record.
-
The primary key makes each record unique and helps to speed up database searches.
Setting up Relationships
-
Databases can contain numerous tables which need to be linked together.
-
This setup can be achieved by using primary and foreign keys to create relationships between tables.
-
A foreign key in one table is a primary key in another table. The foreign key creates a link that lets the database draw information from one table to another.
-
The design of relationships should limit data redundancy and improve data integrity.
Normalization
-
Normalization is a process used to minimise data redundancy and improve data integrity.
-
It involves organising the tables and relationships within a database to eliminate redundancy and conflicting information.
-
The main goal of normalization is to ensure data is stored in only one place.
-
Following a set of normalization patterns, known as normal forms, can help to ensure optimal performance of a database system.
Indexes
-
Indexes are a special kind of database object that can be created to improve retrieval speed of data from a table.
-
They work in a similar way to indexes in a book, pointing to the location of data rather than containing the data itself.