2
\$\begingroup\$

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")
\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$
    – radarbob
    Oct 4, 2017 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.