In relational database management systems (RDBMS), understanding keys is crucial for designing efficient and effective database schemas. Keys help ensure data integrity and enable efficient data retrieval. Below, we discuss three types of keys: Business Key, Natural Key, and Candidate Key, using PostgreSQL table definitions for banking, customers, accounts, and transactions as examples.

1. Business Key

A Business Key is a unique identifier used within a specific business context. It’s often an existing attribute that has real-world meaning and is used to identify records in a business domain.

Example: In a banking system, a business key could be the AccountNumber for an account or CustomerID for a customer.

PostgreSQL Table Definition:

CREATE TABLE customers (

    CustomerID VARCHAR(20) PRIMARY KEY,

    Name VARCHAR(100),

    Email VARCHAR(100) UNIQUE

);

2. Natural Key

A Natural Key is a type of business key that derives its uniqueness from attributes that naturally exist in the real world. These keys are not artificially created but are based on actual data elements.

Example: In the same banking system, the AccountNumber could be a natural key for the accounts table since it is inherently unique to each account.

PostgreSQL Table Definition:

CREATE TABLE accounts (

    AccountNumber CHAR(10) PRIMARY KEY,

    CustomerID VARCHAR(20) REFERENCES customers(CustomerID),

    Balance DECIMAL(10, 2)

);

3. Candidate Key

A Candidate Key is a set of one or more attributes that can uniquely identify a record within a table. There can be multiple candidate keys in a table, and one of them is chosen to be the primary key.

Example: In the transactions table, both TransactionID and TransactionRefNumber could serve as candidate keys.

PostgreSQL Table Definition:

CREATE TABLE transactions (

    TransactionID SERIAL PRIMARY KEY,

    TransactionRefNumber CHAR(12) UNIQUE,

    AccountNumber CHAR(10) REFERENCES accounts(AccountNumber),

    TransactionDate TIMESTAMP,

    Amount DECIMAL(10, 2)

);

Summary

1. Business Key
   • Represents real-world identifiers.
   • Example: CustomerID, AccountNumber.
2. Natural Key
   • A type of business key derived from real-world attributes.
   • Example: AccountNumber.
3. Candidate Key
   • Set of attributes that can uniquely identify a record.
   • Example: TransactionID, TransactionRefNumber.

Feel free to adjust the examples and code to better fit your specific needs or extend them as required.

Business Key

A Business Key (also known as a Domain Key) is a data attribute or set of attributes that have a meaningful value in the business domain and are used to identify or reference an entity uniquely within a business context. Business keys are chosen based on business logic and are often visible and understandable by end-users.

Characteristics

• Business Relevance: Directly related to the business domain, making them intuitively understandable to business users.
• Uniqueness: Ideally, a business key should uniquely identify a record without ambiguity.
• Stability: Should be relatively stable over time, although not as strictly invariant as surrogate keys.

Business keys are commonly used in scenarios where data needs to be integrated or matched across different systems or databases, relying on business concepts. For example, a customer's social security number (SSN) or a product's SKU number can serve as a business key.

Natural Key

A Natural Key (also known as a Natural Business Key) is a type of business key that naturally occurs in the real world and is used within databases to uniquely identify a piece of data. It is derived from the existing attributes of the data that are inherent to the entity being represented.

• Inherent:Derived from the data's natural attributes without artificial generation.
• Uniqueness:Uniquely identifies data entities in a natural manner.
• Understandability:Often immediately recognizable and meaningful to users.

Natural keys are used when the uniqueness of a record is defined by real-world attributes. Examples include email addresses for user accounts or vehicle identification numbers (VINs) for cars.

Candidate Key

Candidate Keys are a set of attributes (or a single attribute) in a table that can qualify as a unique key for identifying rows in the table. A table can have multiple candidate keys, each capable of uniquely identifying a row.

• Uniqueness:Every candidate key must uniquely identify each row in a database table.
• Non-Redundancy:No two rows can have the same value for candidate keys.
• Minimality:A candidate key must be minimal, meaning that removing any attribute from it would result in losing its property of uniqueness.

Candidate keys are considered when designing databases to ensure data integrity and uniqueness. From the set of candidate keys, one is chosen as the primary key for the table, while others can serve as alternate keys or be used to enforce uniqueness through unique constraints.

Key Differences and Relations

Business vs. Natural Key:While both are meaningful within the business domain, natural keys are a subset of business keys that are inherently found in the data. Business keys might not always be natural; they could be imposed by business processes.

Candidate Keys vs. Natural/Business Keys:Candidate keys are a broader concept that includes any potential key that can uniquely identify table rows, including both natural/business keys and surrogate keys. The primary key is a special case of a candidate key that is chosen to serve as the main unique identifier for table rows.

Conclusion

The selection of keys in database design is a critical decision that affects data integrity, performance, and usability. Business keys and natural keys are closely tied to the business domain, offering meaningful ways to identify records. Candidate keys offer a more technical perspective, highlighting all possible unique identifiers from which the primary key is chosen. Understanding these concepts is essential for effective database schema design, ensuring that data remains accurate, consistent, and accessible.

SAMPLE DATA FOR BUSINESS KEY, NATURAL KEY AND CNDIDATE KEY

Department Table

department: This could be a Business Key as it represents a meaningful value in the business domain and is used to uniquely identify a department within the business context.

There isn't a clear Natural Key in the Department Table as presented. However, if the department names are unique and not subject to change, then department could also be considered a Natural Key.

• department_id:This is a strong candidate key because it is unique for each department.
• department:This could also be a candidate key if the department names are guaranteed to be unique.

Employee Table

• employee_number: This might be a Business Key since it appears to be a unique identifier used within the business to identify employees.
• email: Could also serve as a Business Key because emails are unique identifiers in a business context.

email: Assuming that each employee has a unique email address, this could be a Natural Key because it is a real-world unique identifier that naturally occurs.

Candidate Keys:

• employee_id: Clearly a candidate key as it is a unique attribute for each employee.
• employee_number: If this is unique across the organization, it also qualifies as a candidate key.
• email: If unique, this is another candidate key.

Note that for the department_id in the Employee Table, it's a foreign key that relates to the department_id in the Department Table, establishing a relationship between the two tables.

Key Distinctions and Relations

• Business Keys are defined by business need and may not always be the primary key in database design.
• Natural Keys are keys that naturally and obviously identify an entity, such as an email or social security number.
• Candidate Keys are any keys which could serve as the primary key. In practice, one of them is chosen to be the primary key based on factors like stability and simplicity, and the others may be used for alternate indices or constraints.