NYCPHP Meetup

[Cliff Hirsch]

Coalesce is a great function and the solutions proposed are really
excellent. Many thanks.

I think Phil hit the nail on the head with the statement, "which are
limited only by the imagination of marketing people." It's not today's
imagination that causes me angst -- ok, a bit. Its what curveball I'll
be hit with in six months that my architecture didn't anticipate. 

-----Original Message-----
From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org]
On Behalf Of Phil Duffy
Sent: Friday, October 07, 2005 3:02 PM
To: 'NYPHP Talk'
Subject: Re: [nycphp-talk] data modelling vs. db design (was:
ERDiagramtoolfor MySQL/OS X)


> -----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
_____________________________________________
New York PHP Talk Mailing List
AMP Technology
Supporting Apache, MySQL and PHP
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org