How can I establish a database relationship that allows different types of users to register in SqlAlchemy

Question

I have different users that will access different funcionality in the plataform and have different information associated with them. I implemented the tables according to the diagram bellow, where I have a User table with authentication data and I have two tables for two user types with their corresponding information.

Database diagram

I have a one to one relationship from "zpeedr <-> user" and the same for "merchant <-> user". I'm using flask_sqlalchemy with a postgresql database. It's not possible to instanciate a zpeedr or merchant type user without associating it with an existing user.id, as intended. My problem is that using this setup I'm able to assign the same user_id for both the zpeedr and the merchant. How I make sure that one user.id can only associated by either a zpeedr user or a merchant user?

My simplified code for table creation is:


class User(UserMixin, db.Model):
    ''' User properties '''
    id = db.Column(db.Integer, primary_key=True)
    role = db.Column(db.String(10), nullable=False)
    name = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(50), unique=True, nullable=False)
    password_hash = db.Column(db.String(250), nullable=False)
    status = db.Column(db.String(15), server_default="Pending")
    registration_date = db.Column(db.DateTime, server_default=db.func.current_timestamp())
    zpeedr = db.relationship('Zpeedr', backref='user', uselist=False)
    merchant = db.relationship('Merchant', backref='user', uselist=False)

def __init__(self, role, name, email, password_hash, status, registration_date, zpeedr, merchant):
    self.role = role
    self.name = name
    self.email = email
    self.password_hash = password_hash
    self.status = status
    self.registration_date = registration_date
    self.zpeedr = zpeedr
    self.merchant = merchant


class Zpeedr(db.Model):
    ''' Zpeedr Properties '''
    id = db.Column(db.Integer, primary_key=True)
    # Foreign key to User table
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, unique=True)
    address = db.Column(db.String(200), nullable=False)
    location = db.Column(db.String(50), nullable=False)
    zipcode = db.Column(db.String(10), nullable=False)
    contact = db.Column(db.String(15), nullable=False)
    vat = db.Column(db.String(20), nullable=False, unique=True)
    nin = db.Column(db.String(20), nullable=False, unique=True)

def __init__(self, user_id, address, location, zipcode, contact, vat, nin):
    self.user_id = user_id
    self.address = address
    self.location = location
    self.zipcode = zipcode
    self.contact = contact
    self.vat = vat
    self.nin = nin


class Merchant(db.Model):
    ''' Merchant Properties '''
    id = db.Column(db.Integer, primary_key=True)
    # ForeignKey to User table
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, unique=True)
    ticker = db.Column(db.String(4), nullable=False)
    category = db.Column(db.String(10), nullable=False)
    address = db.Column(db.String(200), nullable=False)
    location = db.Column(db.String(50), nullable=False)
    zipcode = db.Column(db.String(10), nullable=False)
    contact = db.Column(db.String(15), nullable=False)
    vat = db.Column(db.String(20), nullable=False, unique=True)
    nin = db.Column(db.String(20), nullable=False, unique=True)
    # Establish relationship with Support_merchant Table  (one-to-many)
    support_merchant = db.relationship('Support_merchant', backref='merchant')

def __init__(self, user_id, ticker, category, address, location, zipcode, contact, vat, nin):
    self.user_id = user_id
    self.ticker = ticker
    self.category = category
    self.address = address
    self.location = location
    self.zipcode = zipcode
    self.contact = contact
    self.vat = vat
    self.nin = nin

Renato Renato · Accepted Answer · 2020-01-23T03:38:08

If the purpose of table user is simply store common data you can use postgres inheritance.

In a very semplified model you can have: the parent table with all common data

create table user_(
  type varchar(1)
  );

and your children:

 create table zeepdr(
   id integer primary key,
   nin varchar
   ) inherits(user_);

 create table merchant(
   id integer primary key,
   ticker char(4)
  ) inherits(user_);

The corresponding code in SQLAlchemy should be something like this:

Table("zeepdr", metadata, ..., postgresql_inherits="user_")

Basic Usage

Insert

From what I think of goats you don't need of user that are neither merchant nor zeepdr so you will insert data as if the parent table did not exist:

insert into zeepdr(id, type, nin) values(1,'Z','a');
insert into merchant(id, type, ticker) values(1,'M','aaaa');

This means also that a user will be either a merchant or a zeepdr.

Select

select * from zeepdr;

behaves as you expect. Moreover if you want retrieve only common data from all user you can use keyword ONLY:

select * from ONLY user_;

or in SQLAlchemy

result = table.select().with_hint(table, 'ONLY', 'postgresql')

Some more information: constraint

Only check or not-null constaints are inherited, unless you use NO INHERIT. Other type are no inherited.

Last not least

Some more information about inheritance and SQLAlchemy here

How can I establish a database relationship that allows different types of users to register in SqlAlchemy

1 Answers