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

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!
> Jacksql

Data Retrieval From Online Database - Paid Consultant Required

Hi,

I wonder if anyone can help with the following on a fee paying basis
for the the design or development of some type of script or utility or
piece of code to do the following work.

I wish to retrieve some data from an online database that is in the
public domain. The online database has a search facility that matches
the entry (name) in a search box then returns a screen stating that a
match has been found or not found.

If a match is found there is a button to click that proceeds to the
screen containing the data which is simply two names. It is these two
names that I wish to retrieve and store them in something like a text
file where they are associated with the original entry (name) used in
the search box.

I have a list of the entries for the search box that can be supplied
in sample format for testing as a columnar text file or comma
delimited file or a spreadsheet.. I would need the procedure to
process the list of search entries, retrieve the data then move on to
the next one in the list. Obviously, if a match was not found then the
process would need to move on the the next entry in the list with
perhaps a message saying "No Match" until the entire list was
processed.

The PC I hope to run the process on is a Windows XP machine and if I
need to purchase any particular software that is necessary for the job
then I am quite willing to do so. Also, as I pointed out above I would
pay for the work to be done.

I hope that I have explained the above OK and that I have posted it to
the correct newsgroup(s). If it is not in the correct newsgroup I
would be grateful if anyone could point me in the right direction.

Regards
Dave GibsonReplied in microsoft.public.sqlserver.programming.

--
David Portas
SQL Server MVP
--sql

Data import - foreign key question

This is a rather abstract question about data design, but I ask it here
because a) the database is SQL Server, and b) you're such a learned
bunch!

Let's assume the classic relation of Customers and Orders, where an
Order may reference a single Customer. If I was designing such a
relation from scratch, I would create the Customer table with an
Identity column and call it CustomerID. The Order table would contain
a column called CustomerID, a foreign key to the Customer table.

So far, so unexceptional. However, in my current project I have to
work with legacy data that comes from a number of old Access systems
where the data was not normalised. I wish to normalise it.

The main table in this new system contains reports on parts. Each
report may reference a single part. However, the old data which I have
to import allowed the user to type in the part number. This has led to
dirty data (for example, '40-7889-9098' appears, as does '40-7889-
9098') so I will clean this data up. In the application, the part
number will be selected from a drop down list, though the administrator
will have access to a builder to add, amend or delete part numbers.

So, my report table needs to store a reference to a part. When I
import the data into my SQL Report table, I will initially bring across
the part number. I will then populate the Part Numbers table with all
discrete, distinct part numbers from the Report table. My question is
should I then create a PartNumberID column in both tables, and "back
populate" the Report table with the PartNumberID which corresponds with
the matching PartNumber - e.g.

UPDATE
R
SET
R.fldPartNumberID = PN.fldPartNumberID
FROM
tblReports R
INNER JOIN tblPartNumbers RN
ON R.fldPartNumber = RN.fldPartNumber

I could then drop the fldPartNumber from the tblReports table.

My question is - should I bother? Or can I just leave the actual
PartNumber in the Reports table, and leave the tblPartNumbers table
with a single column which is both Primary key and Foreign key?

Sorry if this is poorly expressed - I had a tough weekend!

Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukI'm not sure why you say you would leave the PartNumbers table with a
single column. Would it not be better to have a Parts table, whose
primary key is PartNumber? Presumably you have things like names,
descriptions, flags etc. for each part (and if you don't, then you
probably will at some point), so you would need a Parts table anyway.

And what would the difference be between PartNumberID and PartNumber? I
would guess that PartNumber must be unique (after you clean them up,
that is), so why would you want to invent your own ID?

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<1111406898.871386.17820@.g14g2000cwa.googlegroups.c om>...
> I'm not sure why you say you would leave the PartNumbers table with a
> single column. Would it not be better to have a Parts table, whose
> primary key is PartNumber? Presumably you have things like names,
> descriptions, flags etc. for each part (and if you don't, then you
> probably will at some point), so you would need a Parts table anyway.
> And what would the difference be between PartNumberID and PartNumber? I
> would guess that PartNumber must be unique (after you clean them up,
> that is), so why would you want to invent your own ID?

This is exactly my point. The trouble is that I am so used to
creating tables from scratch:

fldPartNumberID
fldPartNumber
fldDescription
fldCurrent
fldOEMID
etc.

that I find myself itching to add the redundant Primary Key, even
though the PartNumber is itself unique or, if it is not, it is unique
in combination with one or more other fields.

Ah, that's the point. I knew I'd get there in the end. If the part
number is NOT unique, but in combination with, say, fldOEMID, then in
order to store a reference to a row in the parts table the Reports
table would need to have TWO foreign keys on the Parts table. So, my
rule of thumb is:

If the PartNumber is unique, it's the ID. If it isn't, it isn't the
ID.

Thanks for your help in clarifying my thoughts.

Edward|||"Edward" <teddysnips@.hotmail.com> wrote in message
news:25080b60.0503211101.73c404b8@.posting.google.c om...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:<1111406898.871386.17820@.g14g2000cwa.googlegroups.c om>...
>> I'm not sure why you say you would leave the PartNumbers table with a
>> single column. Would it not be better to have a Parts table, whose
>> primary key is PartNumber? Presumably you have things like names,
>> descriptions, flags etc. for each part (and if you don't, then you
>> probably will at some point), so you would need a Parts table anyway.
>>
>> And what would the difference be between PartNumberID and PartNumber? I
>> would guess that PartNumber must be unique (after you clean them up,
>> that is), so why would you want to invent your own ID?
> This is exactly my point. The trouble is that I am so used to
> creating tables from scratch:
> fldPartNumberID
> fldPartNumber
> fldDescription
> fldCurrent
> fldOEMID
> etc.
> that I find myself itching to add the redundant Primary Key, even
> though the PartNumber is itself unique or, if it is not, it is unique
> in combination with one or more other fields.
> Ah, that's the point. I knew I'd get there in the end. If the part
> number is NOT unique, but in combination with, say, fldOEMID, then in
> order to store a reference to a row in the parts table the Reports
> table would need to have TWO foreign keys on the Parts table. So, my
> rule of thumb is:
> If the PartNumber is unique, it's the ID. If it isn't, it isn't the
> ID.
> Thanks for your help in clarifying my thoughts.
> Edward

Not exactly - in the case you describe, you would have a composite primary
key on (PartNumber, OEMID):

create table dbo.Parts (
PartNumber char(12) not null,
OEMID int not null,
...
constraint PK_Parts primary key (PartNumber, OEMID)
)

It would be then referenced by one foreign key made up of two columns:

create table dbo.Reports (
ReportID int not null,
PartNumber char(12) not null,
OEMID int not null,
...
constraint PK_Reports primary key (ReportID),
constraint FK_Reports_Parts foreign key (PartNumber, OEMID) references
dbo.Parts (PartNumber, OEMID)
)

This isn't unusual, and in general, you should always use the table's
natural primary key, even if it's made up of several columns, and only
introduce your own when you have a good reason - there's no rule to say that
a primary key must be a single column.

For example, a certain table's natural key might be made up of 10 columns,
including wide varchar columns - that would probably cause performance
problems at some point. So in a situation like that, it's not unreasonable
to introduce your own artificial key, but you would also put a UNIQUE
constraint on the natural key anyway, to make sure the data is valid.

Simon|||teddysnips@.hotmail.com wrote:
>...
> So far, so unexceptional. However, in my current project I have to
> work with legacy data that comes from a number of old Access systems
> where the data was not normalised. I wish to normalise it.
> The main table in this new system contains reports on parts. Each
> report may reference a single part. However, the old data which I
have
> to import allowed the user to type in the part number. This has led
to
> dirty data (for example, '40-7889-9098' appears, as does '40-7889-
> 9098') so I will clean this data up.

>From my perspective, this is where your biggest potential problem lies,
although hidden. I once had to do a similar project where data from
Access tables had to be translated into a different format that could
be imported into a different system. Almost all this was done in code
since it involved complex decisions based on interrelationships of
existing data. The part I didn't like was that one field had to be put
into different categories that didn't exist in the original. The other
fields had nice concrete rules for the conversion. So I created a form
and suggested that their regular employees choose the category from a
combobox for each record similar to the way your application will
select part numbers. All the data coverted smoothly into the new
system except for one of the categories that had been miscatalogued.
The customer wanted me to reduce my invoice based on the grief caused
by having the wrong category. I pointed out that I had selected none
of the categories. Showing which computer was used for the improper
selections would have been even better. Avoid cleaning data yourself
if possible. If not, keep a backup and get the cleanup rules approved.
'Replace' can also do some pretty unexpected things if you're not
careful. Insist that they have someone else check the "clean" data for
final approval.

James A. Fortune