Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Hierarchical Data and 5th Normal Form.


I am working with HIPAA Taxonomy codes.

5 tables are involved as a base.

Before we really begin, this is an integration project, the original data
sits in a db2 database and the structure is untouchable. Since taxonomy data
is pretty static we are creating a SQL version in our application.

the structure of a taxonomy is
TaxonomyType>TaxonomyClassification>TaxonomySpecialization

The IDs for each one are governmentally mandated, and each ID makes up a
global ID.

20-Allopathic Physicians
    7P-Emergency Medicine
        S0010-Sports Medicine

thus the final code for this physician would be 207PS0010X.

Currently each level is kept in a separate table. However the issue arises
because of 00000 codes. If you do not use a subtier then the code has 00000s
instead.
So the above would become
20-Allopathic Physicians
    7P-Emergency Medicine
        00000-Emergency Medicine

thus the final code for this physician would be 207P00000X.

Currently I have the following table structures

TaxonomyType
----------------
ID(PK)
Description
~etc

TaxonomyClassification
------------------------
TaxonomyTypeID(pk)
ID(pk)
Description

TaxonomySpecialization
------------------------
TaxonomyClassificationID(pk)
ID(pk)
Description

DoctorTaxonomy
-----------------
DoctorID(PK)
TaxonomyTypeID(PK)
TaxonomyClassificationID(PK)
TaxonomySpecializationID(PK)

Originally I only had ID as the pk on the tables, but the ID has to consist
of both the parent table ID and Child Table ID. However the Join to
DoctorTaxonomy does not work this way. I have been reading about
treestructures and 5th normal form, but I am really lost about the whole
thing. And could really use some help.

Thanks

Hello Eric,

I think the table design might be the following:

TaxonomyType
----------------
ID(PK)
Description
~etc

TaxonomyClassification
------------------------
ID(pk)
TaxonomyTypeID(FK)  --Foregin key of TaxonomyType
Description

TaxonomySpecialization
------------------------
ID(pk)
TaxonomyClassificationID(FK) --Foregin key of TaxonomyClassification

Description

DoctorTaxonomy
-----------------
DoctorID(PK) (FK) --Foregin key of Doctor table
TaxonomySpecializationID(PK)(FK) -- Foreign key of TaxonomySpecialization

or

DoctorTaxonomy
--------------
ID (PK)
DoctorID(FK) --Foregin key of Doctor table
TaxonomySpecializationID(PK)(FK) -- Foreign key of TaxonomySpecialization

I assume the relationship of the Keys are:

TaxonomyTypeID 1-------M TaxonomyClassificationID

TaxonomyClassificationID 1-------M TaxonomySpecializationID

Since TaxonomyClassificationID depends on TaxonomySpecializationID and
TaxonomyTypeID depends on TaxonomyClassificationID, it shall not be put in
the same table DoctorTaxonomy unless you use other method to keep the
consistency of the relationships.

If you have a TaxonomySpecializationID, you could get the exact one
TaxonomyClassificationID and one TaxonomyTypeID. By using inner join, you
shall be able to get the result from above tables in a query.

Some issues may require a bit more in depth attention and may fall under
the umbrella of Advisory Services. Microsoft now offers short-term and
proactive assistance for specific planning, design, development or
assistance with installing, deploying, and general "how to" advice via
telephone.  For more information:

http://support.microsoft.com/default.aspx?scid=fh;en-us;advisoryservice

Hope this is helpful. Please let's know if you have any further comments or
questions.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.asp...
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc