Subclassing Unknown Product Types

I have been working on a database for an online store that carries a wide
variety of product types.

I was pointed to sub-classing to resolve the issue of two product types
having data that applied only to their type (ex pens need to store whether
they are a ballpoint or roller-ball and that obviously doesn’t apply to hats
which records hat size that doesn’t apply to pens).

But, how can I design a sub-classing structure to allow for products that
aren’t hats or pens, but some unknown 3rd product type to be added in the
future?

I would just add the tables in the future, but then I'd need to re-write
parts of the application that acts as the front end to the user to recognize
these new tables, and that’s what I'm trying to avoid.

My first inclination is to suggest that you simply use the product
description to distinguish the item 'traits'. However, in answer to your
question, you could try something like this:

tblItems
ItemID (PK)
ItemDesc

tblTraits
TraitID (PK)
TraitDesc

tblItemTraits
ItemID (PK) (FK)
TraitID (PK) (FK)

This will allow you to create any number of traits, per item, as well as
re-using traits, as some products have similar named traits, even if they
might be completely different types of items.

HTH

Sharkbyte

"Aaron" wrote:

I have been working on a database for an online store that carries a wide
variety of product types.

I was pointed to sub-classing to resolve the issue of two product types
having data that applied only to their type (ex pens need to store whether
they are a ballpoint or roller-ball and that obviously doesn’t apply to hats
which records hat size that doesn’t apply to pens).

But, how can I design a sub-classing structure to allow for products that
aren’t hats or pens, but some unknown 3rd product type to be added in the
future?

I would just add the tables in the future, but then I'd need to re-write
parts of the application that acts as the front end to the user to recognize
these new tables, and that’s what I'm trying to avoid.

Sharkbyte,

Very useful information. It looks like this concept may work for me. I have
one difficulty I can't seem to wrap my head around.

What if a trait for an item can have more than one value? For example the
item "hat-002" has a trait "color" value of "white" and a second value
"black". I think I need another junction table between tblItemTraits and
tblItems, right?

And then what table stores the actual trait value(s)?

Appreciate the help.

Aaron

"Sharkbyte" wrote:

My first inclination is to suggest that you simply use the product
description to distinguish the item 'traits'. However, in answer to your
question, you could try something like this:

tblItems
ItemID (PK)
ItemDesc

tblTraits
TraitID (PK)
TraitDesc

tblItemTraits
ItemID (PK) (FK)
TraitID (PK) (FK)

This will allow you to create any number of traits, per item, as well as
re-using traits, as some products have similar named traits, even if they
might be completely different types of items.

HTH

Sharkbyte

"Aaron" wrote:

I have been working on a database for an online store that carries a wide
variety of product types.

I was pointed to sub-classing to resolve the issue of two product types
having data that applied only to their type (ex pens need to store whether
they are a ballpoint or roller-ball and that obviously doesn’t apply to hats
which records hat size that doesn’t apply to pens).

But, how can I design a sub-classing structure to allow for products that
aren’t hats or pens, but some unknown 3rd product type to be added in the
future?

I would just add the tables in the future, but then I'd need to re-write
parts of the application that acts as the front end to the user to recognize
these new tables, and that’s what I'm trying to avoid.

Actually, have your Traits be:

TraitDesc: White
TraitDesc: Black

Then, again, you can use them on multiple items, and your don't need any
more tables.

Assuming you are wanting to track sales/stock purchases, by the color, it
may be easier to create Hat-002Wt and Hat-002Bk. If not, you will still need
to structure things in a way to make it easy to select a white hat vs. a
black hat. Which would most likely be as two separate records, anyway. It's
just a matter of what table you store the multiple records in.

HTH

Sharkbyte


"Aaron" wrote:

Sharkbyte,

Very useful information. It looks like this concept may work for me. I have
one difficulty I can't seem to wrap my head around.

What if a trait for an item can have more than one value? For example the
item "hat-002" has a trait "color" value of "white" and a second value
"black". I think I need another junction table between tblItemTraits and
tblItems, right?

And then what table stores the actual trait value(s)?

Appreciate the help.

Aaron

"Sharkbyte" wrote:

My first inclination is to suggest that you simply use the product
description to distinguish the item 'traits'. However, in answer to your
question, you could try something like this:

tblItems
ItemID (PK)
ItemDesc

tblTraits
TraitID (PK)
TraitDesc

tblItemTraits
ItemID (PK) (FK)
TraitID (PK) (FK)

This will allow you to create any number of traits, per item, as well as
re-using traits, as some products have similar named traits, even if they
might be completely different types of items.

HTH

Sharkbyte

"Aaron" wrote:

I have been working on a database for an online store that carries a wide
variety of product types.

I was pointed to sub-classing to resolve the issue of two product types
having data that applied only to their type (ex pens need to store whether
they are a ballpoint or roller-ball and that obviously doesn’t apply to hats
which records hat size that doesn’t apply to pens).

But, how can I design a sub-classing structure to allow for products that
aren’t hats or pens, but some unknown 3rd product type to be added in the
future?

I would just add the tables in the future, but then I'd need to re-write
parts of the application that acts as the front end to the user to recognize
these new tables, and that’s what I'm trying to avoid.

First, I have a very big limitation in that this system is for re-selling
products, which means that our suppliers dictate the item number. Even if we
were to change it for our customer's benefit, our purchase orders would still
need the supplier item number.

I think that listing traits this way could get very excessive. It sounds to
me like in the traits table I'd have a record for the color black, for the
color white, for the hat size small, the hat size large, the pen type
ballpoint, etc.... With this type of table structure, how would i know which
trait is a color trait and which trait is a size trait?

It makes sense to me to put the trait description as the genreic "color"
instead of the specific "black" and then store the value "black" in the
tblItemTraits as a seperate field. But, this is where I have a problem with
multiple colors for an item.

"Sharkbyte" wrote:

Actually, have your Traits be:

TraitDesc: White
TraitDesc: Black

Then, again, you can use them on multiple items, and your don't need any
more tables.

Assuming you are wanting to track sales/stock purchases, by the color, it
may be easier to create Hat-002Wt and Hat-002Bk. If not, you will still need
to structure things in a way to make it easy to select a white hat vs. a
black hat. Which would most likely be as two separate records, anyway. It's
just a matter of what table you store the multiple records in.

HTH

Sharkbyte


"Aaron" wrote:

Sharkbyte,

Very useful information. It looks like this concept may work for me. I have
one difficulty I can't seem to wrap my head around.

What if a trait for an item can have more than one value? For example the
item "hat-002" has a trait "color" value of "white" and a second value
"black". I think I need another junction table between tblItemTraits and
tblItems, right?

And then what table stores the actual trait value(s)?

Appreciate the help.

Aaron

"Sharkbyte" wrote:

My first inclination is to suggest that you simply use the product
description to distinguish the item 'traits'. However, in answer to your
question, you could try something like this:

tblItems
ItemID (PK)
ItemDesc

tblTraits
TraitID (PK)
TraitDesc

tblItemTraits
ItemID (PK) (FK)
TraitID (PK) (FK)

This will allow you to create any number of traits, per item, as well as
re-using traits, as some products have similar named traits, even if they
might be completely different types of items.

HTH

Sharkbyte

"Aaron" wrote:

I have been working on a database for an online store that carries a wide
variety of product types.

I was pointed to sub-classing to resolve the issue of two product types
having data that applied only to their type (ex pens need to store whether
they are a ballpoint or roller-ball and that obviously doesn’t apply to hats
which records hat size that doesn’t apply to pens).

But, how can I design a sub-classing structure to allow for products that
aren’t hats or pens, but some unknown 3rd product type to be added in the
future?

I would just add the tables in the future, but then I'd need to re-write
parts of the application that acts as the front end to the user to recognize
these new tables, and that’s what I'm trying to avoid.

This does make things more complicated... You can try a couple of different
methods:

* Add a TraitType field. The problem is you will end up with multiple,
small look-up tables to handle color, size, pen type, etc.

* If you can define a finite number of traits, you can assign an items
trait to its trait number. Example: Hat-Trait1 is Color, Trait2 is Size,
etc. Each item will need to be defined, but all traits will be specific to
the item.

* You may be able to do something similar to the last one, but actually
create templates. So that all hats have Trait1 = Color, Trait2 = Size. The
problem you may encounter would be that all colors would be available, to
select, but may not be available for that hat. Put the templates in their
own table, and a FK in tblItems.

HTH

Sharkbyte



"Aaron" wrote:

First, I have a very big limitation in that this system is for re-selling
products, which means that our suppliers dictate the item number. Even if we
were to change it for our customer's benefit, our purchase orders would still
need the supplier item number.

I think that listing traits this way could get very excessive. It sounds to
me like in the traits table I'd have a record for the color black, for the
color white, for the hat size small, the hat size large, the pen type
ballpoint, etc.... With this type of table structure, how would i know which
trait is a color trait and which trait is a size trait?

It makes sense to me to put the trait description as the genreic "color"
instead of the specific "black" and then store the value "black" in the
tblItemTraits as a seperate field. But, this is where I have a problem with
multiple colors for an item.

Hmm...... Maybe a bit more clarification might help.

All hats will have the same traits (ex every hat will have a material trait,
multiple color traits, multiple size traits), all pens will have the same
traits (ex every pen will have a material trait, multiple ink color traits,
multiple product color traits). BUT the traits for hats will never be the
same set of traits as the traits for pens. Some may overlap (such as
material), others will not (such as ink color).

And, some traits will have multiple values, such as ink color, hat color,
and pen product color.

It seems poor practice to me to have all the traits as fields in a product
table, so I have been looking for an alternative. And you can see now, this
is where I am stuck.

I believe I misunderstood your original concept, but I think that it may
work they way I *thought* you meant it.

If the tblTraits table were to store the trait class (ex "color", "size",
"material") in TraitDesc, then the junction table (tblItemTraits) could have
an additional field for the value, like so:

tblItemTraits
ItemID (PK) (FK)
TraitID (PK) (FK)
TraitValue

And TraitValue would store the specific material for that specific item (ex
"plastic").

I run across the problem, then, of how to assign multiple color values to a
specific trait/item.

Would it be poor practice to simply redefine the tblItemTraits to ....

tblItemTraits
ItemTraitsID (PK)
ItemID (FK)
TraitID (FK)
TraitValue

.... so that I can have more than one item/trait combination without having 2
records with the same primary key?

I greatly appreciate your help.

Aaron

"Sharkbyte" wrote:

This does make things more complicated... You can try a couple of different
methods:

* Add a TraitType field. The problem is you will end up with multiple,
small look-up tables to handle color, size, pen type, etc.

* If you can define a finite number of traits, you can assign an items
trait to its trait number. Example: Hat-Trait1 is Color, Trait2 is Size,
etc. Each item will need to be defined, but all traits will be specific to
the item.

* You may be able to do something similar to the last one, but actually
create templates. So that all hats have Trait1 = Color, Trait2 = Size. The
problem you may encounter would be that all colors would be available, to
select, but may not be available for that hat. Put the templates in their
own table, and a FK in tblItems.

HTH

Sharkbyte



"Aaron" wrote:

First, I have a very big limitation in that this system is for re-selling
products, which means that our suppliers dictate the item number. Even if we
were to change it for our customer's benefit, our purchase orders would still
need the supplier item number.

I think that listing traits this way could get very excessive. It sounds to
me like in the traits table I'd have a record for the color black, for the
color white, for the hat size small, the hat size large, the pen type
ballpoint, etc.... With this type of table structure, how would i know which
trait is a color trait and which trait is a size trait?

It makes sense to me to put the trait description as the genreic "color"
instead of the specific "black" and then store the value "black" in the
tblItemTraits as a seperate field. But, this is where I have a problem with
multiple colors for an item.