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.