Is the database model adequate?

Question

Python

Hello everyone, my friends and I are new to python and decided to write our own catalog of places in Moscow. We wrote a file with models for this. I would like, if someone can, then see if the database is normally compiled at all?)

The code

from sqlalchemy.dialects.postgresql import ARRAY, DATERANGE, JSONB
from sqlalchemy.sql import text, select
from sqlalchemy_utils import DateRangeType
from sqlalchemy import create_engine, Column, Integer, String, Text, ForeignKey, TIMESTAMP
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
import config as cfg
import utils

from marshmallow_sqlalchemy import ModelSchema
from marshmallow import fields

SQLALCHEMY_DATABASE_URI = "postgresql+psycopg2://kell:[email protected]%s/kell" % cfg.DOMAIN

db = create_engine(SQLALCHEMY_DATABASE_URI)
connection = db.connect()

Session = sessionmaker(bind=db)

db_session = Session()

Base = declarative_base()


class Company(Base):

    __tablename__ = "companies"
    company_id = Column(Text, primary_key=True)
    name = Column(Text)
    address = Column(Text, default="Moscow")
    about = Column(Text)
    status = Column(Text, default="enable")
    data = Column(JSONB)
    emblem = Column(Text, default="")
    city = Column(Text, ForeignKey("cities.code"), default="177.MSK")

    COMPANY_DETAIL_TEMPLATE = {
        "1": {"companyMenu": {"categories": [], "dishs": []}},
        "2": {},
        "3": {},
        "4": {},
        "5": {}
    }

    def compose(self: Company, params: dict, create_user: bool = True):
        # Делаем уникальный суффикс компании
        company_suffix = utils.crc32(params)

        self.company_id = "-".join(
            [params["city"], params["category"], company_suffix])  # Делаем id компании

        self.name = params["name"]
        self.about = params["about"]

        self.data = self.COMPANY_DETAIL_TEMPLATE[params["category"]]
        self.address = params["address"]

        self.city = params["city"]

        db_session.add(self)

        if create_user:
            user_params = {
                "role": 0,
                "login": self.company_suffix,
                "password": utils.generate_password(8)
            }
            company_user = User(
                company_id=self.company_id).compose(params=user_params)

            db_session.add(company_user)


class User(Base):
    __tablename__ = "users"

    user_id = Column(Integer, primary_key=True)
    role = Column(Integer, default=-1)
    login = Column(Text)
    password = Column(Text)
    company_id = Column(Text, ForeignKey("companies.company_id"))
    company = relationship(Company)

    def compose(self: User, params: dict):
        self.role = params["role"]
        self.login = params["login"]
        self.password = params["password"]


class Post(Base):
    __tablename__ = "posts"

    post_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.user_id"))
    company_id = Column(Text, ForeignKey("companies.company_id"))
    title = Column(Text)
    data = Column(Text)
    release_date = Column(ARRAY(TIMESTAMP))
    address = Column(Text)
    img_url = Column(Text)
    second_img_url = Column(Text)
    tags = Column(ARRAY(Text))
    priority = Column(Integer, default=0)
    cost = Column(Integer, default=0)

    company = relationship("Company", lazy="joined")


class Tag(Base):
    __tablename__ = "tags"
    tag_id = Column(Integer, primary_key=True)
    tag = Column(Text, primary_key=True)


class File(Base):
    __tablename__ = "files"
    file_id = Column(Integer, primary_key=True)
    company_id = Column(Text, ForeignKey("companies.company_id"))
    name = Column(Text)
    filename = Column(Text)
    url = Column(Text)


class City(Base):
    __tablename__ = "cities"
    code = Column(Text, primary_key=True)
    name = Column(Text)


class Category(Base):
    __tablename__ = "categories"
    id = Column(Text, primary_key=True)
    name = Column(Text)
    short_name = Column(Text)


class Route(Base):
    __tablename__ = "routes"
    id = Column(Integer, primary_key=True)
    name = Column(Text, default="Маршрут")
    mapid = Column(Text)
    about = Column(Text)
    img_url = Column(Text)


class Place(Base):
    __tablename__ = "places"
    id = Column(Integer, primary_key=True)
    name = Column(Text, default="Место")
    address = Column(Text)
    about = Column(Text)
    img_url = Column(Text)
    company_id = Column(Text, ForeignKey("companies.company_id"))
    category_id = Column(Text, ForeignKey("categories.id"))
    tags = Column(ARRAY(Text))
    priority = Column(Integer, default=0)


# Marshmallow Schemas


class CompanySchema(ModelSchema):
    class Meta:
        model = Company

        fields = ("name", "company_id", "address", "about", "status", "data",
                  "emblem")


class UserSchema(ModelSchema):
    class Meta:
        model = User

        fields = ("user_id", "company_id", "role", "login", "password")


class PostSchema(ModelSchema):
    company_name = fields.Str()

    class Meta:
        model = Post

        fields = ("post_id", "company_id", "user_id", "title", "data",
                  "release_date", "address", "img_url", "second_img_url",
                  "tags", "priority", "cost")


class TagSchema(ModelSchema):
    class Meta:
        model = Tag


class CitySchema(ModelSchema):
    class Meta:
        model = City


class FileSchema(ModelSchema):
    class Meta:
        model = File

        fields = ("file_id", "company_id", "name", "filename", "url")


class CategorySchema(ModelSchema):
    class Meta:
        model = Category


class RouteSchema(ModelSchema):
    class Meta:
        model = Route


class PlaceSchema(ModelSchema):
    class Meta:
        model = Place

        fields = ("img_url", "id", "name", "address",
                  "about", "company_id", "tags", "priority")


def result_to_json(result, schema):
    return schema().dump(result).data

Answer 1

Once again I will express my idea more specifically if they mentioned it :-) The
base is designed for typical requests, and not just like that. To speed them up, normalization is used. In other words, first a description of the business model, then queries, and only then tables, indexes, and so on. Of course, for 1000-100000 records, none of this can be felt, since the computers are not the same anymore. But when going beyond six zeros, the result is felt, and how.
So we draw a diagram and write queries in words. As an example: the user should see the warehouses of firms within a radius of 50 km from his location, sorted by distance and filtered by the relevance of goods from. 1 to 5.