There's a logical difference between a business and its current location.
When you're talking about inspections, it usually makes sense to store both. The inspection history of an address is meaningful, regardless of who occupies that address. And the inspection history of a business is meaningful, too, regardless of the location that business operates in.
Referenced tables not shown, but should be obvious.
create table inspections (
address_id integer not null references addresses (address_id),
inspection_date date not null default current_date,
-- Instead of business name, you could (should?) store the licensed business
-- identifier, if the local government issues one.
business_name varchar(35) not null default 'Unoccupied',
inspector_id integer not null references inspectors (inspector_id),
primary key (address_id, inspection_date)
);
I'd probably want to revoke permissions on this table, and allow inserts only through a stored procedure. The goal would be to make sure that application code didn't accidentally omit the business name (or the licensed business identifier, whichever).
You probably can't have a foreign key based on business name; business names aren't guaranteed unique over time, and foreign keys have to reference columns that have a unique constraint. (There are ways to work around that--adding date columns and additional, overlapping unique constraints, but that might be more trouble than it's worth.) In any case, you can't cascade updates. As a historical record, the data in this table has to express the facts at the time of the inspection.
A long time ago, in 2000, I had a similar question, and posted on Google Groups. I received an awesome response from a guy called Joe Celko, and it will fit right into your problem. I've been using this methodology ever since, and it has great performance, and works really well
https://groups.google.com/forum/#!search/pascaljr/microsoft.public.sqlserver.programming/knQ5WXIDfeo/E0vfeC2kbTsJ
It uses two integer values, a left and a right value, to store the hierarchy. I've made some adaption to the table and procs over the years, but the essence remains the same.
Best Answer
Logical data is how your applications see the data, and how they query data.
In the case of a relational database, the design of tables, along with their columns and relationships, is the logical model. In other database models the logical level could be queues, collections, or any type of data structure used by applications.
But how are those data structures written to disks (and to memory)? That is the physical layer. It is the set of files written to disk to contain data, and the format used to represent data in such files.
So what is the independence between physical and logical layers? It is the principle that states that programs which query a database don't need to know how it stores data physically. For example, an SQL query mentions table and columns, not files and bytes from that file.