Is this good idea to split table with many field for on two parts for convenience?

Real example:

class UserData(Base):
    __tablename__ = "users_data"

        id = Column(Integer, primary_key=True, index=True)
        age = Column(Integer, index=True)
        first_name = Column(String, index=True)
        last_name = Column(String, index=True)
        country = Column(String, index=True)
        city = Column(String, index=True)
        comment = Column(String, index=True)
        users_meta = relationship("UserMeta", uselist=False, back_populates="users_data")
    class UserMeta(Base):
        __tablename__ = "users_meta"
        id = Column(Integer, primary_key=True)
        email = Column(String, unique=True, index=True)
        hashed_password = Column(String)
        created = Column(DateTime(timezone=True), server_default=func.now())
        last_visited = Column(DateTime(timezone=True))
        is_active = Column(Boolean, default=True)
        user_data_id = Column(Integer, ForeignKey('users_data.id'))
        users_data = relationship("UserData", back_populates="users_meta")

So in one table only id and data about user, the second table is a meta info. Currently I'm don't know how many times and which fields I will use more than other.

A one more bonus question it is, is I need to create third table with correlation between email and id or I can do it without it?

P.S. I hope the data is pretty understandable to no translate in standard SQL format

1 Answer 1


There are a couple of issues with your proposed design.

Starting with the simplest issue: The "age" column. Attributes like "age" are calculated values that depend on an underlying attribute. You must be storing the date somewhere else in the database. I recommend that you compute the age in an SQL statement or make it into a virtual column.

Second, the facts in the user_meta table seem to mash several concepts together: User authentication, account info, and contact information. Given that mix, I'm guessing you will have only one row per user--unless you allow users to create multiple login identities based on email address. If you have a one-to-one relationship, why are you splitting it into two tables? Absent more information about your use case, I actually think you need 5 tables: User identity, physical addresses, email addresses, account activity, and passwords.

Your proposed schema has poor information security. Passwords should be stored separately so you can limit access and implement other security measures. Similarly, you probably want to limit access to email addresses.

So my recommendation is that you look at ALL your user attributes and see what the logical groupings are. Consider which sets of information have a one-to-one or many-to-one relationships. Consider the security aspects when making the groupings.

  • There a one big problem. I'm don't know how to bind a user from users_meta and user from a users_data. There any build in relation that I can use, not by just getting back assigned id and applying it to another one table? Sep 1, 2020 at 9:11
  • If by "bind" you mean associate a row in users_meta with a row in users_data, that is accomplished by inserting the value of the primary key (id column) from the row in users_data into the users_data_id column. There is no need to put anything from users_meta into users_data. Think of users_data as the primary table that identifies the existence of a user. The users_meta table stores related information related to a user--it has no meaning without the row in users_data.
    – Jim D
    Sep 1, 2020 at 12:03

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.