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.
