Java web application authentication - account design

Question

I am working on a web project, backend is Java & Mysql, the client include web(html5) and app(IOS/Android), I have some doubt in design the account of the system.

There are 3 different types of account:

• Shop, shop account will have its own website,
• Customer, customer access shop/commodity via app(IOS/Android),
• Admin, manage everything of the system.

My basic idea of authentication:

There will be account / role / permission table for sure, because both admin & customer will have quite complex user permission issue, customer also have different permission due to their history behavior.

I have kind decided to use Apache Shiro, due to its simplicity & distributed session.

My question is:

(1) Should I create a single account table or 3 individual account tables.

(2) Any advise on design of 3 tables: account / role / permission ?

Answer 1

If in your first question you're asking how to design a database schema for three very distinct entities (admin user, customer user and shop owner), I suggest you don't combine them into a single table, because they are different concepts and will likely have different features.
You kind of answered your own question, since "ease of programming" rarely trumps business rules/logic.

Your decision to use an existing security framework, or to roll your own, should be independent of the data model for your core business entities.

If you don't want to use a managed solution like Stormpath, and haven't settled on Shiro yet, check out OACC, an open-source permission-based security framework for Java with support for hierarchical security domains, super users, permission inheritance and impersonation.

It might be a good fit for your project because:

• you won't need to clutter your database design with authorization-related aspects
• OACC was designed for multi-tenancy application architectures (like your project's "shops")
• it allows for impersonation, which is a powerful feature if you need to support customer service representatives without giving them "admin" privileges

[Disclaimer: I am a maintainer and co-developer of OACC]