Type, sub-type, and sub-sub-type database design
| Author |
Message |
|
Jeremy Olmstea
#1 / 8
|
 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 |
|
 |
|
Erland Sommarsko
#2 / 8
|
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 |
|
|
|
MSherr..
#3 / 8
|
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 |
|
|
|
Jeremy Olmstea
#4 / 8
|
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 |
|
|
|
Mike Forsma
#5 / 8
|
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 |
|
|
|
Doug Mill
#6 / 8
|
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 |
|
|
|
Jeremy Olmstea
#7 / 8
|
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 |
|
|
|
Mike Sherril
#8 / 8
|
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 |
|
| |
|
