NYCPHP Meetup

[Phil Duffy]

> -----Original Message-----
> From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org]
> On Behalf Of Cliff Hirsch
> Sent: Friday, October 07, 2005 1:02 PM
> To: 'NYPHP Talk'
> Subject: Re: [nycphp-talk] data modelling vs. db design (was: ER
> Diagramtoolfor MySQL/OS X)
> 
> Breaking up People into separate tables makes me think of a problem I am
> struggling with and that's probably all too common. A product has a
> price, right -- simple. Maybe not. The price could be fixed. It might be
> x dollars per pound or some other unit. Maybe the price is even
> non-linear.
> 
> So it seems like each price model should be a separate object and thus a
> separate table. Right? And if so, it seems like it would be impossible
> to get all product info in one query efficiently. Do I query the product
> table to see what price model that product uses and then perform a 2nd
> query? Do I join everything and see what price model "pops up?"

Perhaps I have misunderstood this question, but to me a price is normally
not an entity but an attribute of an entity such as a product/service.  The
latter would have a table established, and the price might represent one
column in the table.

"Might" is the keyword for the price attribute, because pricing can get
quite complex.  In the simplest case, price would be applied to a constant
unit over the entire product/service table.  It gets a bit more complicated
when different pricing units apply against different products/services,
e.g., units for some products, pounds for others.  It then becomes necessary
to store the pricing unit as an attribute as well as the price.  Another
dimension of complexity is a pricing schedule, in which the price is
dependent upon the amount, for example, or upon the location of the buyer.
Then a reference to the price schedule might be stored in the
product/service table as well as information about the pricing unit.
Obviously the above does not exhaust pricing schemes, which are limited only
by the imagination of marketing people.

It seems to me that pricing schedules, as opposed to prices, would be
considered entities since they have their own attributes.

It would then depend upon the goal of the query which route was taken, but
for applying prices to products/services sold, the first table access would
be to the product/service table and the subsequent access to the specific
price schedule, and perhaps to the specific row in that schedule that
applies to the product/service sold.

Incidentally, be careful about the identification of the entity.  Are you
dealing with pure products, pure services or a hybrid (and does it really
matter)?  Software vendors, for example, sell licenses (theoretically a
service?) and support (a pure service).  Automobile dealers sell a physical
product, but also maintenance service.  Do you mix products and services in
the same table (generally my preference although I would distinguish the
product from the service) or create separate tables?  Typically the pricing
algorithms can cover both so there is a simplification reason for combining
them into one table.

HTH.

Phil