Splitting table with many field for convenience example

Question

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

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.