0
\$\begingroup\$

The use case of an application is there will be two kinds of user. One normal user and another company user. The requirement is

  1. Normal user can follow company and see their posts

  2. A company has multiple types of user. They can be owner(While creating a user account if user selects for company account then the user is an owner of the company), Manager and Editor for now.

  3. Owner can do everything. Manager can create company user other than owner and what editor can do. Editor can only create post, reply to the comments. For such requirements, this is how I have roughly designed the tables and relationship. I am not sure whether Profile table seems relevant in this case. I would like help of other's expertise for scalable database design.

For such requirements, this is how I have roughly designed the tables and relationship. I would like other's expertise for scalable database design.

class User(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    full_name: str = Column(String, index=True)
    email: str = Column(String, unique=True, index=True, nullable=False)
    username: str = Column(String(32), nullable=True, unique=True, index=True)
    hashed_password: str = Column(String, nullable=False)
    profile = relationship('Profile', back_populates='user', uselist=False, cascade="all, delete")
    is_active: bool = Column(Boolean(), default=True)
    is_superuser: bool = Column(Boolean(), default=False)
    is_company_account: bool = Column(Boolean(), default=False)
    comments: List[Comment] = relationship(
        "Comment", back_populates="user", lazy="dynamic"
    )


class Profile(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    avatar = Column(String(255), nullable=True, default="default.jpg")
    bio = Column(String(500))
    user_id = Column(UUID(as_uuid=True), ForeignKey('user.id', ondelete='CASCADE'))
    user = relationship("User", back_populates='profile')
    addresses = relationship("Address", backref="profile")

'''
    A company can have many branches and users. Owner can have many companies. 
'''

class Company(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    name: str = Column(String(200), nullable=False, index=True)
    description: str = Column(String(500))
    mission: str = Column(String(500))
    vision: str = Column(String(500))
    ownership: str = Column(String(100))
    business_type: str = Column(String(100))
    established_date: str = Column(DateTime())
    branches: int = Column(Integer)
    transactions: int = Column(Integer)
    is_active: bool = Column(Boolean, default=False)
    posts: List[Post] = relationship('Post', back_populates='company') 

class CompanyUser(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    user_id = Column(UUID(as_uuid=True), ForeignKey('user.id', ondelete='CASCADE'))
    company_id = Column(UUID(as_uuid=True), ForeignKey('company.id', ondelete='CASCADE'))
    leave_date = Column(DateTime())

class Owner(Base):
    '''
    Owner can do everything
    '''
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    company_user_id: uuid.UUID = Column(UUID(as_uuid=True), ForeignKey('company.id', ondelete='CASCADE'))
    # Owner related information

class Manager(Base):
    '''
    Manager can add company user with specific role.
    '''
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    company_user_id: uuid.UUID = Column(UUID(as_uuid=True), ForeignKey('company.id', ondelete='CASCADE'))
    # Owner related information

class Editor(Base):
    '''
        Editor can only create/edit post
    '''
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    company_user_id: uuid.UUID = Column(UUID(as_uuid=True), ForeignKey('company.id', ondelete='CASCADE'))
    # Owner related information

class Post(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    title: str = Column(String(150))
    text: Optional[str] = Column(String(1024), default=None)
    votes: int = Column(Integer, default=1)
    comments: List[Comment] = relationship(
        "Comment", back_populates="post", lazy="dynamic"
    )
    company_id: uuid.UUID = Column(UUID(as_uuid=True), ForeignKey('company.id'))
    company = relationship('Company', back_populates='posts')

UPDATE

Company is a separate entity. I will try to explain with an example. Suppose, I am a founder of my company called "ABC Ltd". Now, "ABC Ltd" is a company which I registered in an app so I will be a owner(company user). I might now create some manager and editor or manager can create editor for that company for creating a post on behalf of company. So, Owner, Manager, Editor, and Staff are the company user associated with particular Company.

There is another user i.e a normal user or say customer who can see the post of companies that they follow. Normal user if follows "ABC Ltd" company then he/she can see its post and also can chat with staff of that company.

Another way I am designing is

class User(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    full_name: str = Column(String, index=True)
    email: str = Column(String, unique=True, index=True, nullable=False)
    username: str = Column(String(32), nullable=True, unique=True, index=True)
    hashed_password: str = Column(String, nullable=False)
    is_active: bool = Column(Boolean(), default=True)
    is_superuser: bool = Column(Boolean(), default=False)
    is_company_account: bool = Column(Boolean(), default=False)
    comments: List[Comment] = relationship(
        "Comment", back_populates="user", lazy="dynamic"
    )


class Group(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name: str = Column(String(100), index=True)


class Company(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    name: str = Column(String(200), nullable=False, index=True)
    description: str = Column(String(500))
    business_type: str = Column(String(100))
    established_date: str = Column(DateTime())
    branches: int = Column(Integer)
    transactions: int = Column(Integer)
    is_active: bool = Column(Boolean, default=False)
    addresses: List[Address] = relationship('Address', back_populates='company')
    posts: List[Post] = relationship('Post', back_populates='company')


class CompanyGroup(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    user_id = Column(UUID(as_uuid=True), ForeignKey('user.id', ondelete='CASCADE'))
    company_id = Column(UUID(as_uuid=True), ForeignKey('company.id', ondelete='CASCADE'))
    group_id = Column(UUID(as_uuid=True), ForeignKey('group.id', ondelete='CASCADE'))


class Post(Base):
    id: uuid.UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4())
    title: str = Column(String(150))
    text: Optional[str] = Column(String(1024), default=None)
    votes: int = Column(Integer, default=1)
    comments: List[Comment] = relationship(
        "Comment", back_populates="post", lazy="dynamic"
    )
    company_id: uuid.UUID = Column(UUID(as_uuid=True), ForeignKey('company.id'))
    company = relationship('Company', back_populates='posts')
\$\endgroup\$
1
  • \$\begingroup\$ I have updated my question. I hope the requirement is clear now. \$\endgroup\$
    – Serenity
    Commented Feb 24, 2022 at 15:09

1 Answer 1

2
\$\begingroup\$

Magic numbers

The sizes of String columns seem arbitrary. Also some SQL-ish DBMSs don't support VARCHAR fields > 255, so 500 might not be a good choice. If you need to store arbitrary text, you should use Text fields.

Missing relationships

I see no relationship between users and companies. If a user can optionally be member of a company, there should either be a CompanyUser table that maps users to companies (many-to-many) or User should have a nullable, i.e. optional, foreign key to Company (one-to-many).

Useless mapping classes

Instead of creating mapping tables for each role, I'd use an enum field on User to assign them a role. If you require multiple roles, use one UserRole table to map users to roles.

General advice

I advise to separate data into different tables where needed, but only where needed, and try to keep the overall amount of tables low. Otherwise your code will be a JOIN-hell.

\$\endgroup\$
1
  • \$\begingroup\$ First of all, thank you very much for your time and help. As per your answer, mainly related to UserRole, I have updated my question to have Groups which admin will create and then company will pick any group/role they want to assign to their employees which happens through CompanyGroup table. Is that what you are saying when you said use one UserRole table to map users to roles? \$\endgroup\$
    – Serenity
    Commented Feb 25, 2022 at 1:44

Your Answer

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

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