As part of my web app product strategy, I knew that I would need a flexible user/membership managament software. Prior to building one, I thought of leveraging wordpress for the same purpose but being GPL it was ruled out. I looked at other php based Access control framework but it was too generic. Coming from a database background, it was easy for me to create the schema and object relatiionship.

Now the next part was to create a nice and basic API that can be used from any of my future web apps.

This API is going to be work in progress until my web app is done but atleast a basic one is completed.

The API allows the following functions

  • Create custom privileges
  • Create custom roles
  • Assign one or many priviliges to roles
  • Create Groups
  • Create users
  • Assign users to groups
  • Create object types (e.g mockups, dashboards)
  • Create objects (for corresponding object types)

Schema Diagram
membership shema


Here is some logic and description on how each database object is used.

  • User belongs to one or more groups.
  • Groups is more of functional separation of users. The group is more relevant for the dashboard application or an ERP application. For e.g you could have dashboards that report your sales and revenue information, you could have dashboards that report manufacturing or inventory information. So the system enables to create custom groups and user belongs to these groups.
  • When dashboards are created these pages are assigned to one or many groups, so when a new user is created and when the user is assigned a finance group, then the user gets access to all the finance dashboards
  • Roles: Roles control actions of the users. For e.g the roles could be Dashboard_admin, mockup_admin, mockup_developer, mockup_viewer, dashboard viewer and so on. So a mockup_developer can create new mockups and the actions are controlled by the priviliges that are assigned to this role
  • Privileges: These are individual privilege names and each privilege belongs to one or many roles.
    • e.g save_mockup, share_mockup, upload_images etc
    • Privileges allow for fine grain control of user actions
  • w.r.t Mockups, groups don't play a significant role because a mockup is just a sketch and does not contain any data that needs any data governance. Roles and privileges can define the majority of mockup access and control
  • Objects: Objects could be anything. For e.g a single mockup page or a single dashboard page or just a blog post.
  • Object type: Object type classifies each object. In this case for mockups, we will simply have one object type=mockup page
  • categories: Categories serve multiple purposes. For e.g if you were to design a blogging application then blogs are categorized using this table information. If you were to design an ERP application then you could add categories such as "Account Payables", "Account Receivables", "General Ledger" etc. For mockups, categories will serve as project placeholder. I wanted to design a project's table but then it would be a waste and used only for mockups. The category table is quite generic and usable for mockups too.
  • Objects are assigned to categories and categories are assigned to groups so there is no direct assignment of objects to users. This is a desired feature and helps in object sharing
  • The membership plan and details are very basic right now and not much thought has been put on it. The idea is to control the user access to object types and max objects the user can create for a given membership plan.


Each database object has a "WHO" record. This tells us the initial creation and last update date. This is a concept I borrowed from Oracle Applications eBusiness suite.

No records will be ever deleted. All deleted records are just end dated or inactivated.

Do you see this as a good design, bad design, any concerns ?

Related posts:

  1. Membership API database schema

Posted in Internals, Pre-release