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

None aggregation function

I have created a cube where I simply want to store non-aggregatable values. I have used the none aggregation function setting but can't seem to browse the leaf level and see any values after processing. If I set the aggregation function to sum , max or min, all works as expected. I assume that None should be used when you want to load non-aggreatable data into a cube. I would have expected to be able to see data at the leaf level, is this assumption incorrect? I'm using SQL 2005 SP1.

None aggregation function works, you probably simply are not browsing at the real leaves. It is not that simply to get to real leaves, since you need to position every single attribute included in the measure group to get there. For testing you can use Leaves() function (few more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx), but with just a browser you will have hard time navigating to real leaves.|||

Mosha-

Thanks for the rapid response! You are correct I had a tough time getting to the leaves. Problem was... I used the upper filter area in the VS browser, rather that the filters directly on the top of the grid, and each view gave a differing response. I have put screen shots into a word doc to demonstrate but can't find a way to attach them to this post. At least my question is anwsered but I'm slighly concernerd that I got 2 different results based on the way I filtered the view.

Thanks Again

Chris