时间戳

首页 > 解决方案 > 不同列中的一对多关系

sql - 不同列中的一对多关系

问题描述

这是我很难找到明确答案的新蜜蜂 SQL 问题。所以请帮忙。我在数据库中有两个表，其字段如下所示：

建设项目

公司

所以每个项目只有一个开发商，一个建筑师和一个承包商，但是，它可能是同一家公司。任何公司都可能以任何角色参与尽可能多的项目。

有没有办法避免创建 3 个额外的关联表来建立多对多关系？而是建立3个一对多的关系？如果是这样，哪种做法更好？

*换句话说，我不了解关系（一对多和多对多）将（1）行与行或（2）行与“特定单元格”相关联？

我正在学习 Flask_alchemy 和 PostgreSQL。我遇到了问题，编写了这样的代码（表之间没有对特定列的引用）。所以这不行吗？

class Company(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    constr_projects_developed = db.relationship('ConstrProject', backref='developer') 
    constr_projects_main_contracts = db.relationship('ConstrProject', backref='main_contractor')
    constr_projects_architect = db.relationship('ConstrProject', backref='architect')

class ConstrProject(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    developer_id = db.Column(db.Integer, db.ForeignKey('company.id'))    
    main_contractor_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    architect_id = db.Column(db.Integer, db.ForeignKey('company.id'))

那么我的问题是，正确的做法是这样的（1）：

class Company(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    constr_projects_developed = db.relationship('ConstrProject', back_populates='developer') 
    constr_projects_main_contracts = db.relationship('ConstrProject', back_populates='main_contractor')
    constr_projects_architect = db.relationship('ConstrProject', back_populates='architect')

class ConstrProject(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    developer_id = db.Column(db.Integer, db.ForeignKey('company.id'))    
    developer = db.relationship('Company', back_populates='constr_projects_developed')
    main_contractor_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    main contractor = db.relationship('Company', back_populates='constr_projects_main_contracts')
    architect_id = db.Column(db.Integer, db.ForeignKey('company.id'))
    architect = db.relationship('Company', back_populates='constr_projects_architect')

或者像这样（2）？：

class Company(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    cp_developed = db.relationship('Company', secondary=cp_developer_company, back_populates='developer')
    cp_main_contracts = db.relationship('Company', secondary=cp_main_contractor_company, back_populates='main_contractor')
    cp_architects = db.relationship('Company', secondary=cp_architect_company, back_populates='architect')

class ConstrProject(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    developer = db.relationship('Company', secondary=cp_developer_company, back_populates='cp_developed')
    main_contractor = db.relationship('Company', secondary=cp_main_contractor_company, back_populates='cp_main_contracts')
    architect = db.relationship('Company', secondary=cp_architect_company, back_populates='cp_architects')

cp_developer_company = db.Table('cp_developer_company'
    db.Column('company_id', db.Integer, db.ForeignKey('company.id'))
    db.Column('constr_project_id', db.Integer, db.ForeignKey('constrproject.id'))
    )

cp_main_contractor_company = db.Table('cp_main_contractor_company'
    db.Column('company_id', db.Integer, db.ForeignKey('company.id'))
    db.Column('constr_project_id', db.Integer, db.ForeignKey('constrproject.id'))
    )

cp_architect_company = db.Table('cp_architect_company'
    db.Column('company_id', db.Integer, db.ForeignKey('company.id'))
    db.Column('constr_project_id', db.Integer, db.ForeignKey('constrproject.id'))

标签： sqlpostgresqlflasksqlalchemy

解决方案

推荐阅读