Attribute Spliting? Design Question

I need to store address information in the database for validating
insurance risks. The problem with storing is that the information is
hierarchical and is also dependent on the territory. UK Postcode system
has four levels of hierarchy (Area, District, Sector and Unit), French
postcode system has two levels and so on. A territory can support more
than one address system ( like UK can also have a standard street
address comprising county, city and street name or the postcode system
which just consists one alphanumeric postcode).
1) To store the address information I am planning to restrict the
number of levels in the hierarchy to four and have four different
lookup tables that hold hierarchical data.
2) The other possibility is to hold all the information in just one
table with a self-referencing key called parent that will provide the
hierarchy. This table can become huge as we are planning to support a
number of territories.
Which approach is better? Having different tables for levels or one
table with all the levels? Also does method 1 constitute attribute
splitting?
ThanksHi S Chapman,
From my own background is more common to have that kind of information in a
separate tables. (Spain)
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"S Chapman" wrote:

> I need to store address information in the database for validating
> insurance risks. The problem with storing is that the information is
> hierarchical and is also dependent on the territory. UK Postcode system
> has four levels of hierarchy (Area, District, Sector and Unit), French
> postcode system has two levels and so on. A territory can support more
> than one address system ( like UK can also have a standard street
> address comprising county, city and street name or the postcode system
> which just consists one alphanumeric postcode).
> 1) To store the address information I am planning to restrict the
> number of levels in the hierarchy to four and have four different
> lookup tables that hold hierarchical data.
> 2) The other possibility is to hold all the information in just one
> table with a self-referencing key called parent that will provide the
> hierarchy. This table can become huge as we are planning to support a
> number of territories.
> Which approach is better? Having different tables for levels or one
> table with all the levels? Also does method 1 constitute attribute
> splitting?
> Thanks
>|||On 24 Apr 2006 01:19:22 -0700, S Chapman wrote:

>I need to store address information in the database for validating
>insurance risks. The problem with storing is that the information is
>hierarchical and is also dependent on the territory. UK Postcode system
>has four levels of hierarchy (Area, District, Sector and Unit), French
>postcode system has two levels and so on.
Hi S,
Here, you need to ask yourself if the hierarchy levels in the postcode
systems are relevant for your application. What attributes that are
specific to e.g. the District or Unit level do you need to store? Why
are these same attributes not relevant for an address in France?
Maybe yoou are better off just storing the postcode as one column and
forget about the levels encoded in the postcode - they are designed for
the mail sorting centres, not for insurance risk assessment.

> A territory can support more
>than one address system ( like UK can also have a standard street
>address comprising county, city and street name or the postcode system
>which just consists one alphanumeric postcode).
>1) To store the address information I am planning to restrict the
>number of levels in the hierarchy to four and have four different
>lookup tables that hold hierarchical data.
>2) The other possibility is to hold all the information in just one
>table with a self-referencing key called parent that will provide the
>hierarchy. This table can become huge as we are planning to support a
>number of territories.
>Which approach is better? Having different tables for levels or one
>table with all the levels?
This is impossible to answer. I would need to know lots more about the
data you are dealing with - and even then, a gooo advice might not be
possible. Newsgroups are not a good channel for design advise.

>Also does method 1 constitute attribute
>splitting?
No. *IF* Area, District, Sector and Unit are all relevant entities in
your database, each with its own set of attributes, then you MUST have a
table for each of those entities. Even if they are not encoded in the
post code.
Hugo Kornelis, SQL Server MVP|||If you are doing this for bulk mailings, I would put each country in a
table and add different constraints to those tables postal codes. Then
create a UNION-ed VIEW if needed.