On Wed, 23 Apr 2008 14:48:28 -0700 (PDT), nflacco wrote:
>Is there a convient way to do a supertype/subtype heirarchy in mysql
>and do queries on the supertype to return sets of subtypes?
>
>For example, suppose I have a table with several types of military
>hardware:
>
>Table:
>Id----Type--------Price
>1.....Mig-15.....$20
>1.....Mig-17.....$32
>1.....Su-27......$80
>1.....T-72........$20
>
>What I'd like to be able to do is say:
>SELECT FROM Table Where Type=Mig
>instead of
>SELECT FROM Table Where Type=Mig-15 or Type=Mig-17
Hi Nick,
In this specific case, you can use WHERE Type LIKE 'Mig%'. But I guess
that's sidestepping your actual question :)
>Of course, we have to assume we have a type heirarchy:
>
>Airplane
>--Mig
>----Mig-15
>----Mig-17
>--Su
>----Su-27
>Tank
>--T
>----T-72
>
>What's the best way to represent this type heirarchy in the database
>so as to require the minimum amount of queries to get a meaningful
>result like all subtypes of Mig?
There is no single best way. There are several methods; which one you
choose depends on the type of operations you often do. If you google the
terms below, you'll find a wealth of information:
* Adjacency list model
* Nested sets model
* Materialized path model
In SQL Server 2008, there will also be a new data type, HierarchyID,
which is basically an encoded and optimised materialized path. From what
I've seen so far, builtin sup****t for actually handling the hierarchy
apppears to be rather sparse though.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|
