Database design modal

[steen]

I am designing a back end database that will handle registration and logins with access to different website areas.

I need to be able to deal with different account and authentication types.

Imagine a user registers their company online (on the website), the company details go into my “company table”,

and the user details go into the “user table”.

They then get the “account type” as “company administrator” (account type table).

That user (company administrator) can now log into their company account online,
and register multiple users that can acces different company pages (in their company account).

User roles/account type:

Site System Admin - access to the whole system.

company administrator - can create other company admins,team staff,customer access
company team staff - can view work related stuff,rapports,calenders
company customers - can view rapports,calendars
customer administrator - can create their own customers staff, view rapports
customer staff - can create customers, view rapports
customer customers - can view rapports

Can anyone point me in the right direction how to design the database tables ?
I`m new to wappler, so am not sure, if you can make this in wappler.

Best regards
bison

[TomD]

There are some great tutorials covering these topics - eg in the Wappler documentation, and by @psweb and @Hyperbytes for example.

However the most up date tutorials are those by @mebeingken here. If you’re new to Wappler, you will learn a great deal from these videos - in general and specifically about the topic you’ve asked about. There are some free videos on this site, but the courses are not free - but worth every penny/cent (IMO).

[ben]

Just a rough sketch:

[Hyperbytes]

Actually @ben I don’t believe that is the optimal structure.

The role is best linked to the userID rather than a RoleID
This allows the a user to be in two groups simultaneously if required
In the below, RoleID is simply a primary key (used to manage/ delete only)
See Two table approach to user security permissions

[Dave]

Just to chirp in, another approach, we use a permissions table set-up using *nix permissions (same as chmod for example). Keeps it simple and anyone down the line working on our Projects understands the structure adopted with regards to the users permissions.

[steen]

I kinda like the idea @Hyperbytes , that you are able to be in multiple groups for a user. I saw the post and the video, and I guess if doing it that way, it will need to be hardcoded, as you explained.

[Hyperbytes]

Yes, just create the user types you need in the security provider then you can manage the roles programmatically from there

[steen]

How can I extend permissions/features throughout the site, for a single user , simply by clicking on/off checkboxes.

From a list of users, or a search, I want to go to that users permission / feature page, where I can select the permissions for that user.
Maybe the answer is right infront of me, but I cant see how to make that happen.
Anyone knows the answer ?