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

Noob question regarding "Data Warehouse"

Hi

i'm a total noob with the term "data warehouse"

i have 1 db with 1 big table in it.

i was asked from a guy who has no experience either with data warehousing to build a "data warehouse table - no redundancy"

i haven't got a clue how to do it or does this sentence even has a sence to it.

lots of tools on sql server 2005 yet, i don't know where to go in order to move this thing forward.

help would be appreciated.

Avi. :)

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx about DW operations guide on SQL Server 2000 which can give you more information, for SQL 2005 as of now you can depend upon books online in this case, also this is an question with open answers and unless you have specific need it is hard to pinpoint the solution.

Non-English Characters in Table/Stored Procedure Names

Could you tell me what are the limitations/concerns regarding Non-English characters being used for Table/Views/Stored Procedure Names?

I have been studying that very topic, my web site is built on 12 languages at present, and storing documents with UTF-8 encoding in SQL text fields works well. Its a bit tricky though with HTML tags embedded.|||

See the topic below for more details:

http://msdn2.microsoft.com/en-us/library/ms175874(SQL.90).aspx

|||Thanks!