Re: [SQLObject] Composed Primary key

[Brad B. <br...@bb...>]

On Sunday, August 10, 2003, at 06:57 AM, Magnus Lyck=E5 wrote:

> At 19:13 2003-08-05 -0400, Brad Bollenbach wrote:
>
>> On Tuesday, August 5, 2003, at 06:59 PM, Magnus Lyck=E5 wrote:
>>
>>> At 14:18 2003-07-24 -0400, Brad Bollenbach wrote:
>>>> > Well, if there's going to be composite keys, that might as well =
be
>>>> > generalized to all the columns (i.e., any attribute could be a=20
>>>> composite
>>>> > of several columns).
>>>>
>>>> What's the use case for this?
>>>
>>> For instance money objects that consist of an amount and a currency.
>>> You could make them full objects with an identifier and do:
>>>
>>> order_line
>>> |id |order_id|spec          |money_id|
>>> +---+--------+--------------+--------+
>>> |  1|     123|Dog collar    |       1|
>>> |  2|     123|Dog food      |       2|
>>>
>>> money
>>> |id |amt    |currency|
>>> +---+-------+--------+
>>> |  1|12.50  |EUR     |
>>> |  2|0.34   |USD     |
>>
>> This is bad database design. amt and currency belong in the=20
>> order_line table.
>
> I think so too! That's why the propsed feature is good.
>
>>> But is seems simpler to just do:
>>>
>>> order_line
>>> |id |order_id|spec          |price_amt|price_currency|
>>> +---+--------+--------------+---------+--------------+
>>> |  1|     123|Dog collar    |12.50    |EUR           |
>>> |  2|     123|Dog food      |0.34     |USD           |
>>>
>>>
>>> In this case you could say that the price is a value from a
>>> design point of view, but an object from a programming point
>>> of view. Martin Fowler calls such objects "Value Objects" in
>>> the book "Patterns of Enterprise Application Architechture".
>>
>> I don't follow your point. At what point do Python properties stop=20
>> being useable here, and composite columns (for something other than=20=

>> the PK) become necessary?
>
> If I have a Money class, my Python class OrderLine would have
> an attribute "price", which was an instance of the Money class.
> It should *not* have a priceAmount attribute and a separate
> priceCurrency attribute. The internal composition of the price
> is not the concern of the OrderLine class!
>
> Thus, we get a mismatch between Python classes and RDBMS tables.

To reiterate, this can already be solved using properties.

> =46rom an OO Design point of view, you separate your data into
> two categories: Objects and values. An object is something that
> has an identity on its own, like a person, a window, or an
> order line. A value is some data which just describes some
> property of another object, but doesn't have its own identity,
> such as a length, a name, or a monetary amount.
>
> In Python, it's very common that design objects are instances
> of a class, and design values have another type than instance,
> such as long, float or string, but that's not always the case.
>
> So, while the idea that a class in Python corresponds to a
> table in SQL fits the bill in most cases, this is not always
> the case. There can be mismatches in both directions.

Not in SQLObject there can't :) Each SQLObject-derived class=20
corresponds to one database table, though, naturally not necessarily=20
every property of your SQLObject need represent exactly one column.

> If you have simple non-scalar objects like dates with time-zones,
> money objects with a currency, measurements with a unit and a
> given precision etc, you would probably want to "inline" these
> values into your main table, rather than to assign some kind
> of made up identity to these values and store them in a separate
> table. You still implement these "value objects" as instances of
> their own class in Python though.

I disagree here. This is a use case for properties, not for "composite=20=

columns".

> The other way around, you can have a list of integers as a simple
> attribute in a Python class, and then you want to go the other
> way around, and split your Python class into more than one table.
>
> It would be nice if SQLObject handles both these deviations from
> the typical 1 class =3D=3D 1 table situation in a convenient way.
>
> E.g. Python classes that have attributes like below...
>
> class Money
>     amt =3D int()
>     currency =3D str()
>
> class OrderLine
>     id =3D oid()
>     order_id =3D oid from Order()
>     spec =3D str()
>     price =3D Money()
>
> ...should be stored in *one* (not two) tables as we seem to
> agree on above, with as little tweaking as possible.

This design seems rather odd to me. It would probably make more sense=20
to do it like:

class Currency(SQLObject):
     currency_code =3D StringCol(length =3D 3, notNone =3D True)
     order_lines =3D MultipleJoin('OrderLine')

class OrderLine(SQLObject):
     ...
     amount =3D CurrencyCol(notNone =3D True)
     currency =3D ForiegnKey('Currency')

At which point the need for composite columns in this case evaporates.=20=

I've handled a multi-currency credit card payment processing system in=20=

precisely this way with SQLObject.

But -- to give another example (in addition to one I posted earlier in=20=

this thread) -- let's say you had a burning desire to have a "composite=20=

column" for transaction fees on credit cards.

So:

class TransactionFee(SQLObject):
     card_type =3D ForeignKey('CardType')
     tx_fee =3D CurrencyCol()
     commission_percentage =3D FloatCol()

Now, each purchase transaction *has* to record the tx fee and=20
commission percentage in its row, because the fees are subject to=20
change anytime, but we want to ensure we're only charging the customer=20=

exactly the rate they were quoted at the time the sale was made. In=20
this case, it's a bit tedious to manually assign to tx_fee and=20
commission_percentage in PurchaseTransaction every time, so we'll=20
create a composite by using Python properties:

class PurchaseTransaction(SQLObject):
     ...
     card_type =3D ForeignKey('CardType')
     tx_fee =3D CurrencyCol()
     commission_percentage =3D FloatCol()

     def _set_transaction_charge(self, value):
         self.tx_fee =3D value.tx_fee
         self.commission_percentage =3D value.commission_percentage

     transaction_charge =3D property(None, _set_transaction_charge)

In this particular case, I've left out the getter, because at any given=20=

moment in time the tx_fee/com % combo in a purchase transaction doesn't=20=

necessarily currently exist in the transaction_fee table, because the=20
admin may have since adjusted the rates for that card.

The nice thing about using Python properties instead of trying to build=20=

something into SQLObject here is that:

1. We use a feature of Python for one of its intended use cases,=20
instead of reinventing the wheel in SQLObject.

2. We document the class better, because I don't have to read the=20
implementation of another class to figure out what columns I have (e.g.=20=

when I read my class definition, and I want to know  "how do I sum up=20
all the transaction fees for this month for all of the transactions?" I=20=

wouldn't be able to answer this if I had wrapped tx_fee and=20
commission_percentage up into some external class and assigned that to=20=

a "composite column").

Explicit is better than implicit, as is using a feature that Python=20
already has, rather than reinventing it in SQLObject.

--
Brad Bollenbach
BBnet.ca=