Data is King, is KING!

Good Data makes life easy; If we model all our the business logic in the database then the application layer is trivial.

Case in point:  I’m implementing a User Access Module.   First I define a list of Access terms:  Things like dashboard (allow view dashboard),  orders (allow view orders report), download (allow CSV downloads), etc.

Then I come up with a list of Roles: CS Agent, and Executive, will do for now.

Then I create a role_access many-to-many join table with just id, role_id, and access_id.

Then I add a role_id to the users table.

Then I painfully craft all the static data in access, roles, and access_roles tables in csv files that gets loaded when the tables are created.

When that’s done it looks like this.


From there the work in the application layer is easy.    The caller simply looks like this

if self.user.has_access('download'):
    output += self._getDownloadCSVButton()

Implementation of this functionality is also trival.  We add the following two lightweight methods to the user model class:

class User(object):
    def access(self):
        '''Return users access list'''
        sql = '''
           select a.code as access
           from   users u
                  join role_access ra on ra.role_id = u.role_id
                  join access a on ra.access_id =
           where = %s'''
       results = self.db.query(sql, params=(,))
       return [r['access'] for r in results]

   def has_access(self, access_name):
       '''Return True or False if this user has the given access'''

       if access_name in self.access:
           return True
       return False

And that’s all there is to, it.  Express your business logic in the database and the rest follows easy.