Sql Server Programming :: Hierarchical Data and 5th Normal Form.


	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.