Yannick Gingras wrote:
> Greetings Alchemists,
> this is more of a general data modeling question but maybe Alchemy has
> a neat trick to resolve this issue.
>
> It happens quite often that I want to use instances of a class as
> attributes of unrelated objects. One example is Addresses. Both
> Companies and Persons have addresses and it would be somewhat awkward
> to derive both from a common ancestor, even though mixins would do the
> trick. However, the concept of mixins is not straightforward to
> transpose to data mapping. The Address example could be implemented
> as follow:
>
> class Address(DeclarativeBase):
> __tablename__ = 'address'
> id = Column(Integer, primary_key=True)
> city = Column(Unicode(255))
> postal_code = Column(Unicode(15))
> # ...
>
> class Company(DeclarativeBase):
> __tablename__ = 'company'
> address_id = Column(Integer, ForeignKey('address.id'))
> address = relation(Address)
>
> class Person(DeclarativeBase):
> __tablename__ = 'person'
> address_id = Column(Integer, ForeignKey('address.id'))
> address = relation(Address)
>
> One problem with that is that it's hard to prevent orphan addresses.
> Another thing that may or may not be a problem is that you could have
> the same address being used for more than one company or person, which
> could lead to a funny situation if one of them moves.
>
> To allow for more than one address per entity, one could do:
>
> class Company(DeclarativeBase):
> __tablename__ = 'company'
> shipping_address_id = Column(Integer, ForeignKey('address.id'))
> shipping_address = relation(Address, primary_join=...)
> billing_address_id = Column(Integer, ForeignKey('address.id'))
> billing_address = relation(Address, primary_join=...)
>
> Similarly, an open ended number of addresses can be implemented with a
> join table:
>
> class CompanyAddress(DeclarativeBase):
> __tablename__ = 'company_address'
> address_id = Column(Integer, ForeignKey('address.id'))
> address = relation(Address, primary_join=...)
> company_id = Column(Integer, ForeignKey('company.id'))
> company_address = relation(Company, primary_join=...)
>
> class PersonAddress(DeclarativeBase):
> __tablename__ = 'preson_address'
> address_id = Column(Integer, ForeignKey('address.id'))
> address = relation(Address, primary_join=...)
> person_id = Column(Integer, ForeignKey('person.id'))
> person_address = relation(Person, primary_join=...)
>
> But we still have the problem of orphan addresses and it get somewhat
> tricky to prevent addresses re-use.
>
> It's easier to check for address re-use with a multi-slot join table:
>
> class AddressMap(DeclarativeBase):
> __tablename__ = 'address_map'
> address_id = Column(Integer, ForeignKey('address.id'))
> address = relation(Address, primary_join=...)
> company_id = Column(Integer, ForeignKey('company.id'))
> company_address = relation(Company, primary_join=...)
> person_id = Column(Integer, ForeignKey('person.id'))
> person_address = relation(Person, primary_join=...)
>
> However, it's hard to extend when you need a new kind of objects that
> has addresses.
>
> Is there a better solution?
>
>
AFAIK the only part of SQLAlchemy that looks similar to your use case is
concrete inheritance, but I believe even that will not address your
concerns about orphan addresses and shared addresses.
I think your best solution is similar to your AddressMap idea above, but
just make it part of Address instead with check and unique constraints
on your FK columns:
class Address(DeclarativeBase):
__tablename__ = 'address'
__table_args__ = (
CheckConstraint("CASE WHEN company_id IS NULL THEN 0 ELSE 1 END"
" + CASE WHEN person_id IS NULL THEN 0 ELSE 1 END = 1"),
UniqueConstraint("company_id"),
UniqueConstraint("person_id"))
# original columns go here...
company_id = Column(Integer, ForeignKey('company.id', ondelete='CASCADE'))
person_id = Column(Integer, ForeignKey('person.id', ondelete='CASCADE'))
The check constraint above is a bit overkill for just 2 FK columns (you
could just use "(company_id IS NULL) <> (person_id IS NULL)", but it is
easy to extend to >2 FK columns.
Hope it helps,
-Conor
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
