[Discussion] Database structure MW 2.0

[Skywalker-11]

For MW 2.0 we neede a different data structure to support more granular permission management and other settings.

Symfony supports a permission model based on the path or functions (annotations/code). These permissions can be granted or denied based on single or combinations of so called roles.

To support the use cases which came in my mind my idea was a data structure like the following:

management related

table users (userid, name, username, password, session)
table domains (domainid, domain, authentication_method, domain_setting1, domain_setting2, ..., userdefault_setting1, userdefault_setting2, ...)
table user_mail_addresses (userid, localpart, domainid)
table user_permissions (userid, domainid, hasrole1, hasrole2, ...)
table user_settings (userid, language, report, reportaddress, gui1, gui2,...)

log related (haven't looked into these yet)

table maillog
table blacklist
table whitelist
table outq
table inq
table mtalog_ids
mcp_rules

other stuff

table audit_log

This would allow following features which aren't available in 1.2:

• multiple addresses for single user (or even duplicate users for single addresses aka. mailling list)
• multiple domains assinged to user
• domain management with:
  • default settings for each domain
  • domain specific settings
  • domain based user permissions
  • domain based authentication
• user settings for gui etc.

Any other ideas, pros/cons?

[shawniverson]

We need some database normalization and indexing enhancements as well so that large maillogs can be parsed much faster than in the prior version.

[Skywalker-11]

We need some database normalization and indexing enhancements as well so that large maillogs can be parsed much faster than in the prior version.

I could think of at least one improvement in form of an additional table table mail_recipients (mailid, recipient) which will contain a single entry for each mail and recipient. This would create a monolitic table in setups with many mailling list etc. But at least the filtering for single users should be much faster as we don't have to regex search the recipient field.
EDIT: maybe similar table for (mailid, recipient_domain) or adding the domain field to the above

[askadhum]

Regarding to database, I faced a problem with a large maillog table.
The query by use "Contains" filter take over 1000 seconds, and the disk utilization up more than 85% for all the time and the indexes was not efficient.
The best solution is by partitioning table (using range type) by date, which is the best solution for large tables.
For more information check MariaDB partitioning or Mysql partitioning.