Learn about tables, fields (or attributes), records, keys and table relationships.

What is database structure?

A database structure is the blueprint that defines how data is arranged ,organized, stored, accessed, and managed within a database. It’s the underlying framework that ensures efficient data handling, minimizes redundancy, and promotes data integrity. Within a database, data are grouped into tables, each of which consists of rows and columns, like in a spreadsheet.

The structure of a database consists of a set of key components:

• Tables: The core building blocks, each representing a distinct entity or category of data (e.g., customers, products, orders). A table contains all the fields, attributes and records for a type of entity. A database will most probably contain more than one table.
• Columns: Attributes or characteristics within a table, holding specific data types like text, numbers, dates, or even images (e.g., customer_id, product_name, order_date). Column headings are known as fields. Each field contains a different attribute. For every table, a unit of data is entered into each field. It’s also known as a column value. Each column has a data type. For example, the “agent_name” column has a data type of text, and the “commission” column has a numeric data type.
• Data Types: Data classifications indicating the format and values allowed in a column (e.g., int, varchar, date, blob).
• Constraints: Rules that govern data integrity and consistency, commonly including:
  • Primary Key: A unique identifier for each table row, ensuring no duplicates (e.g., customer_id).
  • Foreign Key: A column referencing a primary key in another table, establishing relationships (e.g., order_id referencing customer_id in the Orders table).
  • NOT NULL: Enforces a value in a column (e.g., customer_name cannot be null).
  • UNIQUE: Prevents duplicate values (except for primary keys) within a column.
• Relationships: Connections between tables, often modeled using foreign keys. They define how entities are linked (e.g., an Order belongs to a specific Customer).

This image shows the basic structural elements of a database table.

Data types are also a way of classifying data values or column values. Different kinds of data values or column values require different amounts of memory to store them. Different operations can be performed on those column values based on their datatypes.

Some common data types used in databases are:

• Numeric data types such as INT, TINYINT, BIGINT, FLOAT and REAL. 
• Date and time data types such as DATE, TIME and DATETIME. 
• Character and string data types such as CHAR and VARCHAR.
• Binary data types such as BINARY and VARBINARY. 
• And miscellaneous data types such as:
  • Character Large Object (CLOB), for storing a large block of text in some form of text encoding.  
  • and Binary Large Object (BLOB), for storing a collection of binary data such as images. 

Logical database structure

The logical database structure refers to how data is organized and represented within a database system at a conceptual level. It focuses on the logical relationships between data elements, without concern for the physical implementation details such as storage mechanisms or indexing strategies. The logical structure defines the database schema, which outlines the structure of the database and the relationships between its components.

Key components of the logical database structure include:

1. Entities and Attributes:
   • Entities represent the main data objects or concepts in the database, such as customers, orders, products, etc.
   • Attributes define the characteristics or properties of entities. Each attribute represents a specific piece of information about an entity.
   • Entities and attributes are defined in the database schema using entity-relationship diagrams (ERDs) or similar modeling techniques.
2. Relationships:
   • Relationships define how entities are related to each other within the database.
   • Relationships are represented by lines connecting entities in ERDs, with labels indicating the nature of the relationship (e.g., one-to-many, many-to-many).
   • Relationships enforce data integrity and define the rules for data manipulation and navigation within the database.
3. Keys:
   • Keys are used to uniquely identify instances of entities within the database.
   • Primary keys uniquely identify each record or row in a table and serve as the main identifier for the entity.
   • Foreign keys establish relationships between tables by referencing the primary key of another table.
4. Constraints:
   • Constraints define rules and conditions that data must adhere to within the database.
   • Common constraints include primary key constraints (ensuring uniqueness), foreign key constraints (enforcing referential integrity), and check constraints (validating data values).
5. Normalization:
   • Normalization is the process of organizing data in a database to minimize redundancy and dependency.
   • It involves decomposing larger tables into smaller, related tables to reduce data duplication and improve data integrity.
6. Views:
   • Views are virtual tables that are based on the results of SQL queries.
   • They provide a way to present data from one or more tables in a customized format without altering the underlying data.

Overall, the logical database structure provides a conceptual framework for understanding the organization and relationships of data within the database. It serves as the foundation for designing and implementing the physical database structure, which determines how data is stored and accessed on disk.