Type, sub-type, and sub-sub-type database design 
Author Message
 Type, sub-type, and sub-sub-type database design

I am trying to figure out the best way to design part of a database that
includes a type of material, a sub-type of that material, and a sub-type of
the sub-type of material.  For example one type of material is Hardwood,
which could have Oak as a sub-type, which could then have Red Oak, White
Oak, etc. as sub-types of the subtype.  I am struggling to figure out the
best way to go about this.

The way it will be used is through a web interface where users will first
select the main material type and based on that selection will then select
the sub-material type and then finally the detailed material type.

One option would be to have a Material table and then for each material
have a Sub-Material table, i.e. Sub-Hardwood, Sub-Softwood, etc.  This
wouldn't be bad at two levels but because of the third level requirements,
I could end up needed hundreds of tables to describe each material.  This
seems to be way too massive and a maintenance nightmare.

Other than that I am at a lose as to a simple solution to this problem.  I
am sure this has been done many times as I have seen applications like this
on the Internet before.  Anybody have any ideas on easy way to go about
this?

Thank you for any responses,
Jeremy Olmstead



Mon, 07 Feb 2005 03:53:17 GMT
 Type, sub-type, and sub-sub-type database design

Quote:

> I am trying to figure out the best way to design part of a database that
> includes a type of material, a sub-type of that material, and a sub-type
> of the sub-type of material.  For example one type of material is
> Hardwood, which could have Oak as a sub-type, which could then have Red
> Oak, White Oak, etc. as sub-types of the subtype.  I am struggling to
> figure out the best way to go about this.

> The way it will be used is through a web interface where users will first
> select the main material type and based on that selection will then select
> the sub-material type and then finally the detailed material type.

> One option would be to have a Material table and then for each material
> have a Sub-Material table, i.e. Sub-Hardwood, Sub-Softwood, etc.  This
> wouldn't be bad at two levels but because of the third level requirements,
> I could end up needed hundreds of tables to describe each material.  This
> seems to be way too massive and a maintenance nightmare.

> Other than that I am at a lose as to a simple solution to this problem.
> I am sure this has been done many times as I have seen applications like
> this on the Internet before.  Anybody have any ideas on easy way to go
> about this?

Sure this has been done before, but I doubt that there is a patented
solution to take off the shelf. Rather, it requires knowledge about
the particular field you are about to make a data model of.

My own area is trading with financial instruments, and one import concept
is of course the instruments themselves. These can be divided into
main groups: stocks, bonds, options, futures etc. Then they can be further
sub-divided into bonds with coupons, real-interest bonds, zero-coupon
bonds etc.

How does this look in our database? Of legacy reasons there is one big
table called "instruments" and many columns there are nullable, because
they only apply to one instrument groups. There are also subtables for
some instrument groups: bonds, lottery bonds, futures. Options and
warrants share subtable. One sub-category of bonds have sub-table to
the bonds table. The ambition is to cut down the big table, as some
piece of information is both in the big table and in the sub-tables.
But so much is clear that some columns that are in the main table will
never move out, despite that they do not apply to all groups.

The driving force behind the design is basically pragmatics. I don't
want to have a giant table with 100 columns, because it is difficult
to the developers to grasp. But neither can we have ump{*filter*} nitty-gritty
tables, because then the joins might be horrendeous. In the end, it is
all a compromise.

The simplest would in your case would of course to have only table,
with one column indicating the level in the hierarchy. Another would
be to have three tables, one for each level. I don't know why you think
you may need hundreds of tables, but then again I know if these
various kinds of sub-material have vastly different attributes.

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.***.com/



Mon, 07 Feb 2005 05:58:25 GMT
 Type, sub-type, and sub-sub-type database design
On Wed, 21 Aug 2002 19:53:17 GMT, Jeremy Olmstead

Quote:

>I am trying to figure out the best way to design part of a database that
>includes a type of material, a sub-type of that material, and a sub-type of
>the sub-type of material.  For example one type of material is Hardwood,
>which could have Oak as a sub-type, which could then have Red Oak, White
>Oak, etc. as sub-types of the subtype.  I am struggling to figure out the
>best way to go about this.

"Type" (or "supertype") and "subtype" have a special meaning in
logical design.  I can see how hardwoods have different attributes
than softwoods, and how oak has different attributes than walnut.  But
do those attributes really matter to you?

In your application, do you think the hierarchy "hardwood -- oak --
red oak" really expresses a supertype/subtype, or does it really
express something to do with navigation or categorizing?

--
Mike Sherrill
Information Management Systems



Mon, 07 Feb 2005 21:47:16 GMT
 Type, sub-type, and sub-sub-type database design


Quote:
> On Wed, 21 Aug 2002 19:53:17 GMT, Jeremy Olmstead

>>I am trying to figure out the best way to design part of a database
>>that includes a type of material, a sub-type of that material, and a
>>sub-type of the sub-type of material.  For example one type of
>>material is Hardwood, which could have Oak as a sub-type, which could
>>then have Red Oak, White Oak, etc. as sub-types of the subtype.  I am
>>struggling to figure out the best way to go about this.

> "Type" (or "supertype") and "subtype" have a special meaning in
> logical design.  I can see how hardwoods have different attributes
> than softwoods, and how oak has different attributes than walnut.  But
> do those attributes really matter to you?

> In your application, do you think the hierarchy "hardwood -- oak --
> red oak" really expresses a supertype/subtype, or does it really
> express something to do with navigation or categorizing?

You are right, I didn't mean "supertype" and "subtype" in the traditional
sense.  I meant it as a categorizing mechanism.  Sorry for the confusion,
but I couldn't think of any other way to explain it.  So with that being
the case is there an easy way to categorize the materials?

Thanks,
Jeremy



Mon, 07 Feb 2005 22:55:31 GMT
 Type, sub-type, and sub-sub-type database design

Quote:

> I am trying to figure out the best way to design part of a database that
> includes a type of material, a sub-type of that material, and a sub-type of
> the sub-type of material.  For example one type of material is Hardwood,
> which could have Oak as a sub-type, which could then have Red Oak, White
> Oak, etc. as sub-types of the subtype.  I am struggling to figure out the
> best way to go about this.

> The way it will be used is through a web interface where users will first
> select the main material type and based on that selection will then select
> the sub-material type and then finally the detailed material type.

> One option would be to have a Material table and then for each material
> have a Sub-Material table, i.e. Sub-Hardwood, Sub-Softwood, etc.  This
> wouldn't be bad at two levels but because of the third level requirements,
> I could end up needed hundreds of tables to describe each material.  This
> seems to be way too massive and a maintenance nightmare.

> Other than that I am at a lose as to a simple solution to this problem.  I
> am sure this has been done many times as I have seen applications like this
> on the Internet before.  Anybody have any ideas on easy way to go about
> this?

> Thank you for any responses,
> Jeremy Olmstead

There is a pretty good article at
vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

Hope this helps,

Mike



Tue, 08 Feb 2005 01:30:53 GMT
 Type, sub-type, and sub-sub-type database design

Quote:

> I am trying to figure out the best way to design part of a database that
> includes a type of material, a sub-type of that material, and a sub-type of
> the sub-type of material.  For example one type of material is Hardwood,
> which could have Oak as a sub-type, which could then have Red Oak, White
> Oak, etc. as sub-types of the subtype.  I am struggling to figure out the
> best way to go about this.

3 tables. indexes on anything with "uniqueid" in the column name.

Material:  MaterialUniqueID(identity), fullname, Description
Submaterial: SubUniqueID (identity), MaterialdUniqueID,
subDescription, otherattributes
Sub2Material: Sub2Uniqueid(identity), SubUniqueID, Sub2Description,
Other2Attributes

Let the user pick the material, and stuff the MaterialUniqueID into a

Then

it into a drop down box or whatever.
then,


into an drop down box, and let the user pick.

kinda makes sense?
-doug miller



Tue, 08 Feb 2005 03:55:43 GMT
 Type, sub-type, and sub-sub-type database design


Quote:


>> I am trying to figure out the best way to design part of a database
>> that includes a type of material, a sub-type of that material, and a
>> sub-type of the sub-type of material.  For example one type of
>> material is Hardwood, which could have Oak as a sub-type, which could
>> then have Red Oak, White Oak, etc. as sub-types of the subtype.  I am
>> struggling to figure out the best way to go about this.

> 3 tables. indexes on anything with "uniqueid" in the column name.

> Material:  MaterialUniqueID(identity), fullname, Description
> Submaterial: SubUniqueID (identity), MaterialdUniqueID,
> subDescription, otherattributes
> Sub2Material: Sub2Uniqueid(identity), SubUniqueID, Sub2Description,
> Other2Attributes

> Let the user pick the material, and stuff the MaterialUniqueID into a

> Then

> it into a drop down box or whatever.
> then,


> into an drop down box, and let the user pick.

> kinda makes sense?
> -doug miller

This makes perfect sense.  This is exactly what I was looking for, quick,
easy, and simple to maintain.  Thank you very much.


Tue, 08 Feb 2005 05:40:17 GMT
 Type, sub-type, and sub-sub-type database design
On Thu, 22 Aug 2002 14:55:31 GMT, Jeremy Olmstead

Quote:

>You are right, I didn't mean "supertype" and "subtype" in the traditional
>sense.  I meant it as a categorizing mechanism.  

I'd think you could just use a table of four columns: one for the
product, one for the type, one for the subtype, one for the
sub-subtype.  If you use a compound key of all four columns, you can
categorize each part in more than one way.  Some retail businesse do a
lot of that.

--
Mike Sherrill
Information Management Systems



Sun, 13 Feb 2005 18:29:50 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. super type & sub type

2. sub type / super type

3. Master Parts/Sub Parts/Sub-Sub Parts

4. System Generated OID in typed sub-tables

5. Using character data types in a sub-query

6. Type Mismatch when calling Sub -- WHY?

7. *** How to define a sub-type relationship ***

8. Sub queries <> sub tablesl

9. How can I extract sub-total from a sub-report into the main report

10. How can I extract sub-total from a sub-report into the main report

11. Need Help! Sub-totals within Sub-Totals

12. Pick Archive - PAR.SUB source code - par.sub (0/1)


 
Powered by phpBB® Forum Software