Enterprise applications would require that the password field be encrypted for security purposes using a unidirectional algorithm. Passwords should never be stored in plain text, and it should never be possible to view the password in the database (as we can currently do). It is beyond the scope of this book to cover password security strategies, but a very good explanation of the core principles can be found at http://www.jasypt.org/howtoencryptuserpasswords.html.

MySQL provides a number of password encryption functions that could be used for this purpose. We suggest you browse the documentation at https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html to understand the different options available.

Many enterprises use LDAP (Lightweight Directory Access Protocol) for maintaining users within their organization. LDAP is most commonly used to provide single sign-on, where one password for a user is shared between many services. The password column in the user table would, hence, not be required for such scenarios. If an organization spans many geographical locations, there may be several LDAP realms spread across different continents. Such a scenario may require a new table to store LDAP authorization servers. Each user may then be assigned an authorization LDAP server to process their logon.

Enterprise systems often require extensive audit trails (when and why an action took place and who committed it). This is especially the case for large organizations that are publicly held. The Sarbanes-Oxley Act (SOX), for example, requires that all publicly held companies based in the United States must establish internal controls and procedures to reduce the possibility of corporate fraud. Such processes include identifying authorized and unauthorized changes or potentially suspicious activity over any period of time.

The questions "Who, When, and Why" are the basis of audit trails that need to be considered when designing an enterprise database. Simply adding a few additional columns to all tables is a very good start:

who_created varchar(10) not null
who_updated varchar(10) not null
when_created datetime default current_timestamp
when_updated datetime on update current_timestamp

Note that this syntax is for MySQL, but similar functionality will be available for most databases. The who_created and who_updated columns will need to be updated programmatically. The developer will need to ensure that these fields are set correctly during processing of the relevant action. The when_created and when_updated columns do not need to be considered by the developer. They are automatically maintained by MySQL. The when_created field will be automatically set to the current_timestamp MySQL function that represents the query start time to establish the exact moment in time that the record is inserted into the database. The when_updated field will auto update each time the record itself is updated. Adding these four additional columns will ensure that a basic level of audit tracking is available. We now have the ability to view who created the record and when in addition to who performed the last update and when. The ttt_company table, for example, could be redesigned as follows:

create table ttt_company(
  id_company      int unsigned not null auto_increment,
  company_name    varchar(200) not null,
  who_created varchar(10) not null,
  who_updated varchar(10) not null,
  when_created datetime default current_timestamp,
  when_updated datetime on update current_timestamp,
  primary key(id_company)
);

create table ttt_user_log(
  id_user_log int unsigned not null auto_increment,
  username varchar(10) not null,
  ip_address varchar(20) not null,
  status char not null,
  log_date datetime default current_timestamp,
  primary key(id_user_log)
);