Normalization Question Regarding Column Combinations

We need to store land title information about properties in various
Australian states, but each state maintains it's own land title
registry and use different columns (well actually different
combinations of the same columns). For example:

Victoria store:

TorrensUnit
TorrensVolume
TorrensFolio

Queensland store:

TorrensCounty
TorrensLot
TorrensPlan
TorrensParish
TorrensUnit
TorrensVolume
TorrensTitleRef

There are 11 different columns and they are used in 8 different
combinations depending on the state.

Since we need to store information about land in different states I see
two possible solutions:

1. A sparse table containing the 11 columns with a CHECK constraint to
enforce the valid combinations.

2. A table for each state containing only the columns relevant to the
state with a foreign key relationship to the table containing the
common columns.

I'm not sure if the data type and length is consistent between states
yet (waiting to find this out) but assuming that it is which of these
approaches is going to be the most rigorous? I'm leaning towards (2)
but I don't like the feel of a table per state.>From a design standpoint I try to stay away from your first choice if
possible, seems like it'd be difficult to maintain. I like your second
point, but if there is a common set of data that is shared, then
"normalize" that information.

CREATE TABLE Store(
StoreID int IDENTITY(1,1) NOT NULL,
TorrensUnit varchar(50) NOT NULL,
TorrensVolume varchar(50) NOT NULL
)

CREATE TABLE StoreVictoria(
StoreVictoriaID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
--Specific columns
)

CREATE TABLE StoreQueensLand
(
StoreQueensLandID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
TorrensParish varchar(50),
TorrensTitleRef varchar(50)
--Specific columns
)|||On one hand, property does not move from state to state so one table
per state would work and make sense.

But do you ever view the set of all parcels of land in the country as
your unit of work? I woudl go with one table per state and VIEW that
has the global summary information.|||90% of the time we will be dealing with individual properties, there
will be time where we need to show a list of properties that are in
different states so a VIEW would make sense here. Thanks Celko, when's
your SQL Coding Standards book coming out? Looking forward to it.|||>> Thanks Celko, when's your SQL Coding Standards book coming out? Looking forward to it. <<

It has been out for a several weeks now.

http://www.amazon.com/exec/obidos/t...=glance&s=bookssql