SEO

Ok so lets say I have several types of purchasable commodities &
services - Products, Services, Packages (products & services grouped
together), and Other (unkown fees and such that need to be accounted
for)

So normally.. products, services, packages, and other all get their
own table.

On a purchase though I have to store what commodities have been
bought. Currently this means I have resource_id (the product, service,
etc primary key) and then resource_type (enum to point to table
holding commodity)

This feels ackward though. Should I be doing this? Do you think there
should be a big commodities table which holds all products, services,
etc.. which then points to the separate table (likely another
resource_id, type field)? Any other ideas on this.

(by the way) It's not just the order table which needs to point to the
"commodities", I also have invoices, packing slips, taxes and other
things which all will be referencing these commodities.

rtconner:
> Ok so lets say I have several types of purchasable commodities &
> services - Products, Services, Packages (products & services grouped
> together), and Other (unkown fees and such that need to be accounted
> for)
>
> So normally.. products, services, packages, and other all get their
> own table.
>
> On a purchase though I have to store what commodities have been
> bought. Currently this means I have resource_id (the product, service,
> etc primary key) and then resource_type (enum to point to table
> holding commodity)

I assume, you want something like this:
http://ardane.com/demo/ProdPurch.png

You have products, which have generic data, and specific data for each
type of product. The generic data are stored in entity Product_General
and the specific attributes in ProdA_specific.....ProdX_specific.

The relationships to Purchases, Invoices (etc) all go through
Product_General. This way, you have the flexibility to add and remove
products and to introduce new entities like "Tax doc", "Packing slip",
etc.

--
Erick

Thank you yes, this is what I am asking about.

Speed wise is it not adding too many layers to have the
Product_General table in there? That is a whole new table and indexes.
Would it not be simpler (and faster) to just add the Prod_type field
to the PurchasedProduct table? What advantages does that extra layer
give you? Is it just flexibility, because I don't need that. Not if
it's going to slow queries down a bit.


On Mar 14, 2:31 pm, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
wrote:
> rtconner:
>
> > Ok so lets say I have several types of purchasable commodities &
> > services - Products, Services, Packages (products & services grouped
> > together), and Other (unkown fees and such that need to be accounted
> > for)
>
> > So normally.. products, services, packages, and other all get their
> > own table.
>
> > On a purchase though I have to store what commodities have been
> > bought. Currently this means I have resource_id (the product, service,
> > etc primary key) and then resource_type (enum to point to table
> > holding commodity)
>
> I assume, you want something like this:http://ardane.com/demo/ProdPurch.png
>
> You have products, which have generic data, and specific data for each
> type of product. The generic data are stored in entity Product_General
> and the specific attributes in ProdA_specific.....ProdX_specific.
>
> The relationships to Purchases, Invoices (etc) all go through
> Product_General. This way, you have the flexibility to add and remove
> products and to introduce new entities like "Tax doc", "Packing slip",
> etc.
>
> --
> Erick

rtconner:

[Quoting order repaired for readability]

> On Mar 14, 2:31 pm, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
> wrote:
> > rtconner:
> >
> > > [...Needs several types of 1 entity, related to entities like Invoice...]
> >
> > I assume, you want something like this:http://ardane.com/demo/ProdPurch.png

> > You have products, which have generic data, and specific data for each
> > type of product. The generic data are stored in entity Product_General
> > and the specific attributes in ProdA_specific.....ProdX_specific.

> Speed wise is it not adding too many layers to have the
> Product_General table in there?

It adds one "layer". What is does to the performance is something you'll
find out soon enough. First, it's important not to think in terms of
'tables', but to work towards a logical ER Model, which offers both
flexibility and integrity.

> That is a whole new table and indexes.

Nope. It's just an entity.

> Would it not be simpler (and faster) to just add the Prod_type field
> to the PurchasedProduct table?

If I understand your situation correctly, that wouldn't be a proper model
of your reality.

> What advantages does that extra layer
> give you? Is it just flexibility, because I don't need that. Not if
> it's going to slow queries down a bit.

You have all types of related "products". I am assuming that certain
attributes are used generally (they are the same for each product, no
matter whether it's a downloadable item, a service, a document or what-
not).
Then there are attributes, that are specific to the type of "product".
One type of product will have attributes (color, weight and price per
ounce) while another may have (height, width, price per item and
available-items).

The way to model this is to have one general entity and several attached
specific entities.
The advantage is, that you can make changes to your model where and when
you need them, affecting exactly the part of your model that is involved
in the change.


Now, once you have finished the ER-Model, it's time for the technical
design. You'll assign entities to tables, partly based upon what
functions you need and which performance issues are of any concern.
Basically, you will either have multiple tables:
- ProductsGeneral
- ProdOfTypeA, ProdOfTypeB.....ProdOfTypeX
[You will probably make use of two separate queries: one to find the
ProductType-attribute and one to query the matching ProdOfTypeX-table.]

or you may have just one table Products, with all attributes combined,
leaving those empty that don't match with the current ProductType.


--
Erick