NYCPHP Meetup

[Dan Cech]

Kenneth Downs wrote:
>> 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.

Yes, this combined with the idea of multiple tables below would allow 
you to put together almost any pricing structure you wanted.

Recurring products are a whole 'nother kettle of fish though.

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

Good idea, though shouldn't the SQL be something more like:

SELECT coalesce(special.price,item.price,cat.price)
FROM CATEGORIES cat
LEFT JOIN ITEMS item ON item.cat_id=cat.cat_id
LEFT JOIN SPECIALS special ON special.item_id=item.item_id
WHERE blah blah blah

Dan

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