Pfthb

I'm trying to build a very basic management system website for a hypothetical insurance agency and i just cant wrap my head around how i should organize the database to make it so i can assign users to specific policies and have the ability to update/replace the user in case there are re-arrangements within the agency so policies can be reassigned to the proper agents. This would be used to display data based on login as well. There's 3 layers that i think i need. A User table for user data, a client data/policy table to store client and policy info, and then a table for tasks that would be assigned to policies. I need multiple users to have access to a policy and then the policy should have access to 1 row in the task table. Would it just be better to have a user table and large client table with the task columns inside rather than a separate table for the tasks? I've been banging my head with this for days so if anyone can help, i greatly appreciate it.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SECRET_KEY'] = ''
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db'

db = SQLAlchemy(app)


class User(db.Model):
 id = db.Column(db.Integer, primary_key=True)
 name = db.Column(db.String(50))
 username = db.Column(db.String(50), unique=True)
 password = db.Column(db.String(50))
 email = db.Column(db.String(50), unique=True)

#Multiple assigned users can access
class Client(db.Model):
 id = db.Column(db.Integer, primary_key=True)
 name = db.Column(db.String(50))
 policy_number = db.Column(db.String(50), unique = True)
 expiration_date = db.Column(db.Datetime)

#Single "client" assigned to single row of tasks based on policy number
class PolicyTasks(db.Model):
 id = db.Column(db.Integer, primary_key=True)
 step1 = db.Column(db.String(50))
 step1_completed = db.Column(db.Boolean)
 step2 = db.Column(db.String(50))
 step2_completed = db.Column(db.Boolean)
 step3 = db.Column(db.String(50))
 step3_completed = db.Column(db.Boolean)
 step4 = db.Column(db.String(50))
 step4_completed = db.Column(db.Boolean)
 step5 = db.Column(db.String(50))
 step5_completed = db.Column(db.Boolean)

I removed the code i used to attempt to create the relationships because it might honestly be more helpful to look at the base layout

To wrap your head around how you should organize the database to make it so you can assign users to specific policies, take a look at the docs on the Flask website. I think the second example under One-to-Many is very similar to what you're looking to do http://flask-sqlalchemy.pocoo.org/2.3/models/.

I would recommend keeping the tables separate and not jamming it all into one. Usually that leads to a slippy slope (in my experience) with tables with too many attributes to manage.

It can also mean slower query times because Client.query.filter_by(username='peter').first() would then always query the client data, policy data and whatever else you later on end up throwing in that table, when you may've only needed the policy data for that specific view/api route.

This other stackoverflow post might help too:
database design - when to split tables?