• Adam Thurgar

Logical vs Physical - Part 1 ' The Logical'

Two of the courses that we used to teach at Digital on RDB, where Logical Database Design and Physical Database Design. What I see now, over 25 years later is that very few people understand the distinction between logical and physical, mix them up and have no idea about basic relational theory.

The biggest area I see this occur is in the creation of Primary Keys.

The definition of a Primary Key is that the column/s are unique and not null.

Many people create PK's, but at a physical implementation level, wouldn't a unique constraint not allowing NULLs accomplish the same thing?

What makes a PK special?

The Foreign Key makes the Primary Key special, this is declarative referential integrity at the core, ensuring data integrity.

No use in having all of these PK's without corresponding FK's.

An easy example would be a customers table with a PK on customer id. This same customer id exists in the customers orders table with a FK constraint to the PK on the customers table. This means that the customer id in the customers table is unique and NOT NULL. Because of the PK/FK relationship you cannot insert a row in the customers orders table for a customer that doesn't exist in the customers table, so you cannot have orphan customer orders. Now you cannot delete a customer from the customers table if it has rows in the customers orders table.

This can also be applied to look up tables for countries, states, area codes etc. You can control what data is in your database.

By using PK's and FK's correctly you are ensuring some data integrity in your database, enforced by the database not by the application.


Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t