Re: [sqlalchemy] Composite foreign key constraints
On Mar 13, 2010, at 7:26 AM, sqlalch...@lists.fastmail.net wrote:
>
> class Period(Base):
>__tablename__ = "period"
>
>id = Column(Integer, primary_key=True)
>date = Column(Date, nullable=False)
>hour = Column(SmallInteger, nullable=False)
>
>__table_args__ = (
>UniqueConstraint("date", "hour"),
>{}
>)
>
> class Result(Base):
>__tablename__ = "result"
>
>id = Column(Integer, primary_key=True)
>
>period_id = Column(Integer, ForeignKey("period.id"))
>period = relation(Period, uselist=False, backref=backref("result"))
>
>[...Actual data...]
>
> The example in the 'Essential Alchemy' has something similar with
> products and SKUs, but solved using composite foreign keys. The previous
> code become something like this:
>
> class Period(Base):
>__tablename__ = "period"
>
>date = Column(Date, primary_key=True)
>hour = Column(SmallInteger, primary_key=True)
>
> class Result(Base):
>__tablename__ = "result"
>
>id = Column(Integer, primary_key=True)
>
>date = Column(Date, primary_key=True)
>hour = Column(SmallInteger, primary_key=True)
>
>[...Actual data...]
>
>__table_args__ = (
>ForeignKeyConstraint(["date", "hour"], ["period.date",
>"period.hour"]),
>{}
>)
>
> Is the second the preferred option? If so, what is the added value of
> the separate period table? Or maybe in more general; if my efforts in
> trying to explain the issue have been successful could someone indicate
> what would be the better solution for this?
It reminds me a little of a star schema. The "periods" being in a separate
table would allow you to join your "fact" tables along common periods, using
only the surrogate identifier "period.id". My impression is that the
surrogate primary key approach is more useful here. Having the period table
with a composite primary key which doesn't reference anything (i.e. its not an
association table) isn't of much use if Period doesn't store anything else
meaningful.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
[sqlalchemy] Composite foreign key constraints
First of all I realise this question might be more of a design choice
question than a technical issue. The issue I am facing is creating a
model reflecting a situation where multiple tables all contain data for
a particular period. This period consists of both a date and an hour,
these two can not be combined to one datetime column due to the nature
of the data (hourly indices on energy markets in which each hours
represents a different product/instrument, in other words there is no
direct link between different hours on the same day).
For proper normalisation I thought I would be better to not use both
date and hour column in the three separate tables with data, but define
a period table (with id, date and hour columns) and have the tables
refer to an period id (foreign key). This is what I currently have using
declarative base:
class Period(Base):
__tablename__ = "period"
id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)
hour = Column(SmallInteger, nullable=False)
__table_args__ = (
UniqueConstraint("date", "hour"),
{}
)
class Result(Base):
__tablename__ = "result"
id = Column(Integer, primary_key=True)
period_id = Column(Integer, ForeignKey("period.id"))
period = relation(Period, uselist=False, backref=backref("result"))
[...Actual data...]
The example in the 'Essential Alchemy' has something similar with
products and SKUs, but solved using composite foreign keys. The previous
code become something like this:
class Period(Base):
__tablename__ = "period"
date = Column(Date, primary_key=True)
hour = Column(SmallInteger, primary_key=True)
class Result(Base):
__tablename__ = "result"
id = Column(Integer, primary_key=True)
date = Column(Date, primary_key=True)
hour = Column(SmallInteger, primary_key=True)
[...Actual data...]
__table_args__ = (
ForeignKeyConstraint(["date", "hour"], ["period.date",
"period.hour"]),
{}
)
Is the second the preferred option? If so, what is the added value of
the separate period table? Or maybe in more general; if my efforts in
trying to explain the issue have been successful could someone indicate
what would be the better solution for this?
Thijs
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
