Data Structure Advice

Hello,
I am in the process of redesigning a database for client. The advice I need
is how to design a set of tables to reflect what they current use a single
table for.
They have a table that has records that reflect hierarchal data. For
example, they have fields called Level 1, Level 2, Level 3, and Level 4. The
data in Level 1 is repeated many many times. As is Level 2, but not as often,
and so on. Here are examples of types of data found in this table:
- record 1 will have "building" in Level 1, and nothing in the other fields.
- record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
and nothing in the rest of the fields.
- record 3 will have the same in Level 1 and Level 2 but include "office" in
Level 3.
I would like to design a data structure that will not only normalize the
data, but also allow for a dynamic number of levels. Currently, because the
structure is flat, the maximum number of levels is 4.
Thanks in advance!
Jack
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
Jack,
Depending on your definition of "dynamic", that may not be possible.
A normalized version of what you described would have one table for
Buildings, then a table for Floors (or whatever the level 2 is) with a
foreign key referencing the PK of the Buildings table, then a table for
Offices, with a FK referencing the PK of the Floors table, etc.
More levels could be added to the hierarchy at any time by simply adding
more tables (and, possibly, an addition FK column to the top level table if,
for instance, you had clusters of buildings at different locations).
If, on the other hand, your definition of "dynamic" is that the number of
levels could change at any time by someone inserting or deleting a row,
that's not possible with a normalized structure. Any structure that does
attempt to implement that kind of dynamism for different types will violate
1NF, as you'll be forced to represent differing types (e.g. a Building key
and a Floor key) in the same column. In addition, there's no real win here
because different entities require different attributes. If someone needs
to add, for instance, a Locations level to the hierarchy, that level will
not share the same attributes as Buildings, Floors, or Offices, and
therefore you will still be required to add a new table or new columns.
|||There is a topic in BOL titled "Expanding Hierarchies" that may be of
interest. Searching the NGs will also yield many discussions of
hierarchical data (and the issues with representing and querying the data).
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> Hello,
> I am in the process of redesigning a database for client. The advice I
need
> is how to design a set of tables to reflect what they current use a single
> table for.
> They have a table that has records that reflect hierarchal data. For
> example, they have fields called Level 1, Level 2, Level 3, and Level 4.
The
> data in Level 1 is repeated many many times. As is Level 2, but not as
often,
> and so on. Here are examples of types of data found in this table:
> - record 1 will have "building" in Level 1, and nothing in the other
fields.
> - record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
> and nothing in the rest of the fields.
> - record 3 will have the same in Level 1 and Level 2 but include "office"
in
> Level 3.
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
> Thanks in advance!
> Jack