Entity Relational Model Design and Query Method

Question

Current Design: Users and Groups: Many-to-Many Table
Tools: SQLAlchemy 1.0.9, Python 2.7, Pyramid

The two questions I pose to you:

1. I am uncertain if I should have the group attribute under User or the user attribute under Group. This is a MANY-TO-MANY relationship; with a third table that relates the two different classes.

2. SQLAlchemy has a lot of great examples discussing .joins, but I'm finding the task is difficult with this DB design for querying based on the selected username. (see below)

I have designed a SQLAlchemy database and am trying to implement best practices for retrieving data from my table while eliminating redundancy. With that said, I also want this to be an effective design so that when I build a group_finder function using Pyramid's Authorization and Authentication System, so that I have no issue.

I am using a CRUD methodology. As of right now: To add a user to a Group, I update_group_add_user(...) to add a user or update_group_remove_users(...) to remove a user.

---

base.py

Commented out groups and added users to class Group

association_table = Table('group_user_link', Base.metadata,
    Column('group_id', Integer, ForeignKey('groups.id')),
    Column('user_id', Integer, ForeignKey('users.id')))

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(15), nullable=False, unique=True)
    email = Column(String(300))
    password = Column(String(300), nullable=False)

    #groups = relationship('Group', secondary='group_user_link', backref='users') 

    def __init__(self, username, password, firstname, lastname, email):
        self.username = username
        self.password = hashlib.sha224(password).hexdigest()
        self.email = email
        #self._groups = groups

    def __repr__(self): # code

class Group(Base):
    __tablename__ = 'groups'

    id = Column(Integer, primary_key=True)
    group_name = Column(String(50), unique=True)

    users = relationship('User', secondary='group_user_link', backref='groups')

    def __init__(self, group_name, user=None):
        if user is None: user = []
        self.group_name = group_name
        self._user = user # to group_add_user and group_remove_user

    def __repr__(self): # code

Query method (using CRUD):
This prints out ALL the relationships in tuples in a list. I want to only print out (and return) only the user being entered.

def retrieve_user_bygroup(self, username):
    query= self.session.query(User, Group).join(association).\
    filter(User.id == Group.id).\
    order_by(User.id).all()
    print "retrieve user by group:",  query
    return query

Answer 1

I discovered two similarly asked questions that clarified the use of backref, .join, and .query in SQLAlchemy. This helped clarify how the backref line in users = relationship('User', secondary='group_user_link', backref='groups') made groups accessible through User when later querying with a .join and .filter (shown at the bottom).

Discussing the purpose of backref:

backref means a 'poster' attribute will be added to UserPost. Actually I'd probably call that 'user'. But the point is that 'relation()' knows how to join between those two tables because of the previously defined ForeignKey.

Discussing .join and .query:

.join [works] according [to] the relations, and yields 3-tuples. The arguments to the query() call are essentially the select list in sqlalchemy.

I also found this Stacks answer to be helpful with using .any()

def retrieve_group_byuser(self, username):
    # retrieves group by username
    query = self.session.query(Group).\
    filter(Group.users.any(User.username == username)).all()
    print "retrieved by username:", query
    return query

And I removed groups from User which now looks like this:

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    username = Column(String(15), nullable=False, unique=True)
    email = Column(String(300))
    password = Column(String(300), nullable=False)

    def __init__(self, username, password, firstname, lastname, email):
        self.username = username
        self.password = hashlib.sha224(password).hexdigest()
        self.email = email

    def __repr__(self): 
        # code

Answer 2

1. I would say group should contain [say a set of] users in it. Not the other way round.

Now, you might have a need to know the group given the user, if you want to cache this mapping in memory (instead of getting it from DB) then I would recommend NOT to create a circular dependency between group and user classes. Instead promote the that dependency to a third class, which just defines the relation between group and users.

E.g.

class user:
    def __init__(self, name):
        self.name = name


class group:
    def __init__(self, name):
        self.name = name


class user_group_relation:
    def get_users(self, grp_name):
        # could get it from an internal map (like return g2u_mapping[grp_name]) or 
        # run a query on some DB table..
        pass

    def get_group(self, usr_name):
        # could get it from an internal map or run a query on some DB table..
        pass

    # group to user mapping
    u2g_mapping = {user('user1'): group('group1'), user('user2'): group('group1')}
    g2u_mapping = {group('group1'): [user('user1'), user('user2')]}

2. if this is e.g. a representation of normal user authentication mechanism then only users have passwords, groups don't. Groups just represent a way to organizing things. Hence the password field (and any related functionality should remain in user and not promoted to base.