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\$

migrated from stackoverflow.com Mar 8 '17 at 10:51

This question came from our site for professional and enthusiast programmers.

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\$
  • \$\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 '17 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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