Thoughts on the database

[TomHAnderson]

Having explored the entire database in #30 I have a few thoughts I'd like to share.

• Audit fields - there are many pseudo foreign keys to the vh_users table

  • vh_lang_categories
  • vh_lang_languages
  • vh_lang_strings
  • vh_mediable
  • vh_medias
  • vh_modules
  • vh_notification_contents
  • vh_notifications
  • vh_permissions
  • vh_registrations
  • vh_role_permissions
  • vh_roles
  • vh_themes
  • vh_user_authorizations
  • vh_user_roles
  • vh_user I think the project would be better served by keeping the Laravel-maintained audit timestamps (for soft deletes) and moving the auditing of records into a new table vh_audit. The vh_audit table would have

• table

• table primary key

• action [insert, update, delete]

• vh_user_id [foreign key to vh_user]

• timestamp

This would simplify the schema immensely.

---

Next, the vh_user_roles table is named wrong were it an actual many to many pivot table. But it is not and there is not a unique index on vh_user_id + vh_role_id and those columns are not not null (see next comment).

---

Not Null columns. Nowhere in the schema except for primary keys are columns marked as not null. This is a flaw in the database design and should be addressed directly.

[themodernpk]

- pseudo foreign keys : Are you referring to:

$table->integer('created_by')->nullable()->index();
$table->integer('updated_by')->nullable()->index();
$table->integer('deleted_by')->nullable()->index();

We have those relationships to serve some purpose in future development.

- vh_audit We do have plans to introduce vh_activities but not a priority at this point.

- vh_user_roles You did not suggest the name, did you mean it should be vh_user_role?

- Not Null columns nullable() columns have helped us in handling multiple scenarios at the code level. If we make this change in migration, we may have to test the entire application and review the code. If you have an explanation for this, I would like to learn.