Optimize SqlAlchemy ORM DB models

23 Views Asked by At

I'm studying ORM, and currently trying to build SQLAlchemy ORM model for my prject. My project is keep doctor informations, patient informations and patient information.

Patients can have more than type of treatments so I build tables for each type of examination. For example patient can got treatment for left elbow and left hip, or different patient can have left hip left shoulder and right shoulder treatment.

I want to improve my model. I want to ask can I put these examination results into patient table without build tables for each type of treatment? If yes how can I implement?

Also, is there anything that can I improve in my db model?

I'm fully open for your all advice. I'd be happy any tips to improve my db model

Here is my db model:

from sqlalchemy import create_engine, Column, ForeignKey, Integer, Float, String, DateTime
from sqlalchemy.orm import relationship, declarative_base
import datetime

Base = declarative_base()

class Company(Base):
    __tablename__ = "companies"
    
    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)

class User(Base):
    __tablename__ ="users"
    
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True, nullable=False)
    firstname = Column(String, nullable=False)
    lastname = Column(String, nullable=False)
    gender = Column(String, nullable=False)
    birth_year = Column(Integer, nullable=False)
    company_id = Column(Integer, ForeignKey("companies.id"))
    company = relationship("Company", backref="users")

class Patient(Base):
    __tablename__ = "patients"
    
    id = Column(Integer, primary_key=True)
    firstname = Column(String, nullable=False)
    lastname = Column(String, nullable=False)
    gender = Column(String, nullable=False)
    birth_year = Column(Integer, nullable=False)
    advisor_id = Column(Integer, ForeignKey("users.id"))
    advisor = relationship("User", backref="patients")
    
class BodyPartMeasurement(Base):
    __tablename__ = "body_part_measurements"
    
    id = Column(Integer, primary_key=True)
    patient_id = Column(Integer, ForeignKey("patients.id"))
    patient = relationship("Patient", backref="measurements")
    date = Column(DateTime, nullable=False, default=datetime.datetime.now)
    max_degree = Column(Float, nullable=False)
    min_degree = Column(Float, nullable=False)
    mean_degree = Column(Float, nullable=False)
    
class LeftElbow(BodyPartMeasurement):
    __tablename__ = "left_elbow"

class LeftHip(BodyPartMeasurement):
    __tablename__ = "left_hip"

class LeftShoulder(BodyPartMeasurement):
    __tablename__ = "left_shoulder"

class LeftKnee(BodyPartMeasurement):
    __tablename__ = "left_knee"

class RightElbow(BodyPartMeasurement):
    __tablename__ = "right_elbow"

class RightHip(BodyPartMeasurement):
    __tablename__ = "right_hip"

class RightShoulder(BodyPartMeasurement):
    __tablename__ = "right_shoulder"

class RightKnee(BodyPartMeasurement):
    __tablename__ = "right_knee"

# Define relationships
User.patients = relationship("Patient", backref="advisor")
Patient.measurements = relationship("BodyPartMeasurement", backref="patient")

Firstly, I changed my ForeignKey() with relationship(). Secondly, I've added a BodyPartMeasurement base class to avoid repetition in the definitions of the body part measurements

0

There are 0 best solutions below