NYCPHP Meetup

[Kenneth Downs]

> 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.

THis is not as hard as it looks.  Just use units-of-measure.  We don't
think about it most of the time because it is simply "item", like "5
widgets".  But the UOM could be pounds, acres, whatever you want.

>
> 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?"

The trick is to use a series of exceptions, and a "resolution" view.

You start out with prices in the PRODUCT_CATEGORIES table.  You override
at the ITEMS table level.  Then you override at the SPECIALS table for
that item during a particular period in time.   Make up any other levels
that are appropriate.

The resolution uses the magic of NULLs and COALESCE to join the tables
together and pull out the price.

select coalesce(special.price,item.price,cat.price)
  from SPECIALS special
  JOIN ITEMS item
  JOIN CATEGORIES cat  blah blah blah


>
> -----Original Message-----
> From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org]
> On Behalf Of Stephen Musgrave
> Sent: Friday, October 07, 2005 1:49 PM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] data modelling vs. db design (was: ER Diagram
> toolfor MySQL/OS X)
>
>
>
>> BTW, in the world of business objects, even the concept of a User
>> needs to
>> be rethought.  I would do away with Users as a fundamental business
>> object
>> and replace it with the more generic Person, and identify system Users
>
>> as a
>> subset of the Person universe.  Users then become Persons who play the
>
>> Role,
>> System User, in which additional sets of information are required such
>
>> as
>> username, password, permissions, et cetera.
>
> A-ha!  Even more normalization.  This is the way I have done it my
> system, btw.  There are "staff" users ... err PEOPLE".. and
> "participants" which have their own tables and then the USER table
> handles data that is required for those people to use the application.
>
> Thanks for all the contributions to the thread - it has been very
> helpful!
>
>
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
>
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
>


-- 
Kenneth Downs
Secure Data Software
631-379-0010
ken at secdat.com
PO Box 708
East Setauket, NY 11733