Here is some vanilla SQL:
CREATE TABLE RoomTypes
(
RoomType VARCHAR(12) NOT NULL,
UNIQUE (RoomType)
);
CREATE TABLE Zones
(
Zone VARCHAR(10) NOT NULL,
UNIQUE (Zone)
);
CREATE TABLE Rooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);
CREATE TABLE NumberedRooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
CHECK (RoomType = 'Numbered'),
UNIQUE (Name),
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType, Zone, Name)
REFERENCES Rooms (RoomType, Zone, Name)
);
CREATE TABLE AncillaryRooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
CHECK (RoomType = 'Ancillary'),
UNIQUE (Zone, Name),
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType, Zone, Name)
REFERENCES Rooms (RoomType, Zone, Name)
);
The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.
The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.
Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.
For practical data access reasons, you should use the structure from your first option, but instead keep all versions of your tracked column values including the current version in your history table.
The reason for this is that in general, when you want to look at history, you want to include the present and all past versions. When you don't want to look at history, you want it out of the way. In many cases this means going so far as to segregate the history into a separate schema or database altogether. Even if you keep your history in the same schema as your current data, any queries that look at historical data (including the current values) will be much more complex since they have to essentially union two sources.
Best Answer
Sales taxes are almost infinitely complicated, so you might want to consider using a rules engine like Drools for this.
Sales Tax is a rate or an amount that is charged on the value of an item, or by unit (per day, per litre, per each). An item can have zero, one or many sales taxes applicable to it.
Warning: Some sales taxes are recursive! Ex: there is GST on top of the Vehicle Rental Tax in BC.
The sales tax rates on an item is a function of the following:
I think what you ultimately want is a large look-up table (generated by a materialized view) like the below. It would include default tax rates as well as exemptions (represented by null) or zero-rated (with a rate of zero)
This is only a sample of taxation rules for hotel accommodation in British Columbia:
Then you'd have a crazy query that would figure out the default rates, replacing them with the most appropriate exemptions or zero-rates.