Laravel 9: 10 database-building tips

A comprehensive list of best database-building tips for your scalable Laravel applications

The first and most critical component of developing an application, in my opinion, is knowing what to save and how to preserve it. SQL databases are the most widely used, battle-tested, and mature of all web and embedded device solutions.

For that reason alone, I wanted to share my top ten tips for starting a database schema, which I have kept in my head for a long time. It also allows me to archive and refer to it when someone needs assistance from the beginning.

1. Begin with a pen and paper

If you’re planning an MVC-style application, always try to create a schematic of your database tables and Models. Draw boxes and lines to show which tables are linked to which columns.

This may appear to be a kindergarten assignment, or it may appear that you are wasting time if it is already “in your head,” but having a clear representation of how the tables in your database relate to each other before any code hits a computer will make it easy to make changes rather than refactoring multiple files.

To be clear, refactoring a database that is already linked to the application will take more time, and subsequent changes will be more expensive than simply redrawing a line with an eraser.

2. Make your intentions clear

Don’t save keystrokes on your tables or columns just because you know what data type they are. If a column contains an API token, the name should be api token.

Going for mysterious names, such as tkn, or names that are too short, such as token, just ruins your capacity to manage the app in the long run.

Large names, such as api token string for service in cloud, will only cause your editor to display a nice ellipsis after a few characters. You will only confuse your partners in the age of software collaboration.

This is not to say that you should ignore the table’s context entirely. A column named article title is acceptable, but if it is in the articles table, it is preferable to name it simply title.

3. Maintain a vertical position

This may bring back memories of your first database management class in university, but not everyone has prior experience with software development. There are still people who make the mistake of horizontally expanding data.

Never, ever try to add more columns to expand information. Even if the records are in the same table, always try to create child records that reference the parent.

Adding columns like child 1, child 2, and so on, for example, should be avoided at all costs. Instead, create a children table and use parent id to refer to the parents for each record that it belongs to.

This not only allows a given record to scale back and forth in the number of children, but it also eliminates the burden of retrieving the children using the SELECT * from parents query.

4. Stick to the rules

There are a few important rules to follow when naming columns in a database. While these are not “standard,” they are what I use most of the time to get a good idea of what data type a column represents based on its name.

• Whatever the data type, primary keys are always named id.
• Timestamps are in the past tense and are suffixed ...at
• Date, Time, and Datetime are not timestamps; name them as you wish.
• Booleans are always positive and begin with the letter is_...
• Numeric columns, such as guesses or failures, should be plural.
• Lists in JSON columns are singular and may end with ..._list
• Complex JSON trees can be named however they want, but they may end with ... _tree
• Foreign columns are always concluded with ..._id, similar to author_id
• Non-id columns in foreign columns Foreign columns can be prefixed with ... _as id
• Multiple text columns could end with ..._body
• For binary-encoded data, multiple text columns may end with ..._data
• Binary columns are always terminated with ..._blob or ..._binary
• Columns containing encoded data always end with ..._encoded
• Columns containing encrypted data always end with ..._encrypted

Again, these are not required, but when collaborating, explicitly naming these columns with their purpose can help a lot of coworkers deal with the column data correctly without having to ask someone to pick up the phone.

5. Don’t go crazy with data sizes

If you intend to insert large columns (such as large walls of text or raw binary data) into frequently accessed tables, these can usually be moved into their own tables to avoid accidentally removing them with the classic SELECT * FROM my table.

Create a user’s table with a column called biography, for example. Unless you have complete control over the query at all times, which is often not the case, retrieving multiple users from the database can quickly consume a lot of memory in your app, as a SELECT * FROM users will also retrieve each user's biography.

Perhaps a user biographies table will assist you in keeping both separate, along with a left-join to explicitly incorporate the large column.

6. Always consider SQL independence

“Marry or live free, there is no in-between,” someone once told me. The same is true for database engines.

You may wish to understand the distinctions between SQLite, MySQL, MariaDB, PostgreSQL, and Microsoft SQL Server in order to select the best option for your application. The truth is that you will know when you need to.

If your project does not require any exclusive features from these databases, you can create a database-agnostic application. Otherwise, you’ll know which features are useful to you and will be able to marry your application to a vendor. For your Windows ecosystem, you might want to use MariaDB with Galera Cluster or SQL Server.

There is no guarantee that you will ever need to change your database engine. Migrating from one cloud provider to another may necessitate switching versions of the same database engine. Sometimes what you’re migrating from is no longer available, supported, or simply more expensive to operate. When this happens and you need to migrate as soon as possible, having an agnostic-SQL app comes in handy.

Starting with SQLite is faster, but always consider spinning up a real testing database to ensure your application works in production, as SQLite has many limitations that are not reproducible with a real database.

Read 4 more tips at Quid Experience to support the creator. Thanks in advance.