SqlAlchemy model for users and timezones

Question

I have a following schema, I would like to query user and all timezones belongs to it including timezone name. Could you please validate if my schema constructed correctly and most efficient way?

class Timezone(Base):
 __tablename__='timezone'
 id = Column(Integer, primary_key=True)
 name = Column(String(40), unique=True)
class UserTimezone(Base):
 __tablename__='user_timezone'
 id = Column(Integer, primary_key=True)
 user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'))
 timezone_id = Column(Integer, ForeignKey('timezone.id', ondelete='CASCADE'))
 timezone = relationship(Timezone, backref="timezone")
class User(Base):
 __tablename__='users'
 id = Column(Integer, primary_key=True)
 first_name = Column(String(64), index=False, unique=False)
 last_name = Column(String(64), index=False, unique=False)
 email = Column(String(120), index=True, unique=True)
 password = Column(String(120), index=False, unique=False)
 timezones = relationship(UserTimezone, backref="user_timezone")

Answer 1

I wouldn't bother making a separate table for timezones. That just makes you do joins unnecessarily. Just put the timezone name directly as an attribute of the UserTimezone table.

• radarbob: Agreed. Indeed, why single out 'timezone' for it's own table? Why not do that for every user attribute? Now you can see it doesn't make sense to do that. Sure, calculating times & offsets between timezones is non-trivial but that's a different issue. I might have a timezone class in my Python code for said complex calculations but that a user is in a given zone is a simple fact; an attribute of a user. Finally, your DB engine already knows timezones (I assume!). No need to redefine them. Just have an enum in Python so you can set that user attribute.