Every application that stores information depends on how that information is structured. When working with relational databases, I've developed a set of conventions that I consistently follow, particularly when it comes to table relationships, naming conventions, and organizing data.
These aren't necessarily rules that apply to every situation. There are cases where you have to approach things differently depending on the application. However, following these principles makes my database structures easier to understand, work with, and maintain.
Let's look at some of the things I consider when designing a relational database.
This is one of the first things I consider when designing a relational database.
I don't implement many-to-many relationships directly. Instead, I resolve them using junction tables, also known as associative tables, bridge tables, or link tables.
Now, what exactly does this mean?
Imagine you're building a school management system. You have two entities: students and courses.
A student can enrol in multiple courses. Similarly, a course can have multiple students enrolled in it.
This creates a many-to-many relationship.
Rather than trying to represent this relationship directly, I introduce another table to connect the two entities. Let's call this table enrollment.
Now we have three tables:
studentcourseenrollment
The enrollment table contains foreign keys referencing both the student and course tables.
What we've essentially done is break down the many-to-many relationship into two one-to-many relationships.
A student can have multiple enrollment records, and a course can also have multiple enrollment records.
That's the idea.
This approach also gives us somewhere to store additional information about the relationship itself. For instance, we could have an enrollment date, enrollment status, or even the student's grade for a particular course.
I prefer this approach because it makes the relationships clearer and gives me more control over how the entities interact.
Another principle I follow is combining tables that have a one-to-one relationship.
Think about it this way.
If you have two tables where each record in one table corresponds to exactly one record in the other, do you really need to keep them separate?
Let's consider a practical example.
Suppose you have a user table and a userProfile table. Every user has exactly one profile, and every profile belongs to exactly one user.
If both tables contain information that is always used together, and they have the same lifecycle, it might make more sense to combine them into one table.
Why introduce another table and another relationship when you can have the information in one place?
Now, this isn't something I'd enforce blindly.
There are situations where separating the tables makes sense. Perhaps one contains sensitive information that requires different access controls. Maybe the information is optional, or the two sets of data have different lifecycles.
But where there's no compelling reason to keep them separate, I prefer combining them.
The idea is to avoid introducing unnecessary complexity into the database.
This is another thing I pay close attention to.
As much as possible, I prefer avoiding unnecessarily interconnected tables. I like having clearly defined groups of related tables rather than a database where everything seems to connect to everything else.
Ideally, I prefer something that resembles a star structure.
Think about a star. You have a central point, and other points connect to it through different arms.
The same concept can be applied when organizing database tables.
You can have a central table with several related tables connected to it. You can also have groups within larger groups, where individual sections of the database have their own central entities and related tables.
Now, this doesn't mean that every database must follow a strict star structure. I'm talking about how I prefer organizing relationships, not imposing a particular database modelling methodology on every application.
Even when dealing with a relatively large database, you can still have individual groups of related tables that are connected where necessary.
However, there are situations where avoiding interconnected relationships becomes difficult, sometimes virtually impossible.
Take a messaging application, for example.
You have conversations, users, conversation participants, and messages. A user can participate in multiple conversations, and a conversation can have multiple participants. Messages belong to conversations, and one message might reference another message as a reply.
You can see how the relationships naturally become more interconnected.
Trying to force such a structure into a simplistic arrangement would be counterproductive.
The point isn't to eliminate every complex relationship. It's to avoid creating unnecessary ones.
I prefer having well-organized groups of related tables, with connections that have a clear purpose.
This is a naming convention I consistently follow.
I prefer using singular names when naming my tables.
For instance:
userinstead ofusersproductinstead ofproductsorderinstead oforderspaymentinstead ofpaymentstransactioninstead oftransactions
Why?
Consistency.
A table represents an entity. When defining a model, I prefer referring to that entity using its singular name.
This also makes things more predictable when working with ORMs, where you typically define a model representing a particular entity.
There are developers who prefer plural table names, and that's perfectly fine. This isn't about saying one convention is universally better than the other.
It's simply what I prefer, and I make sure I apply it consistently throughout the database.
Another personal preference is using camelCase when naming columns.
For example:
firstNamelastNamemiddleNamecreatedAtupdatedAtpaymentStatuscustomerId
Rather than mixing different naming conventions, I prefer keeping things consistent.
This is particularly useful when working with application models and writing queries. I find it easier to maintain a consistent naming structure across the different parts of an application.
Now, this isn't necessarily the convention everyone uses. Snake case, for instance, is also very common when working with relational databases.
However, I prefer camelCase, and I stick to it.
The important thing is consistency. Choose a convention and apply it throughout your application.
Another thing I consider important is how relationships are defined.
It's not enough to simply create foreign keys and assume everything else will fall into place. I want the relationships between my entities to be clear.
For instance, if an order belongs to a customer, I want that relationship to be properly defined.
But there's another aspect I pay attention to: back-population.
When working with an ORM, I prefer being able to navigate relationships from both directions.
For example, when accessing an order, I should be able to access the customer associated with it. Similarly, when accessing a customer, I should be able to retrieve their orders.
In SQLAlchemy, this can be achieved using back_populates.
You define the relationship on both models, making the association accessible from either side.
This makes working with related entities considerably easier, especially when dealing with applications that have multiple interconnected models.
I prefer defining these relationships explicitly rather than leaving them ambiguous.
This is another small consideration that can save you unnecessary problems later.
When defining columns, I prefer allowing some reasonable room for growth rather than restricting everything to the absolute minimum length I expect.
For instance, consider a password field.
You don't store the actual password in a relational database. Instead, you store a password hash generated by a password-hashing algorithm.
Now, depending on the algorithm you're using, the resulting encoded hash can have a particular length.
When defining the column, you need to make sure it can accommodate the complete hash.
You don't want to discover later that your column is too small to accommodate the value you're trying to store.
The same consideration applies to other columns. Where appropriate, I prefer leaving some headroom rather than imposing unnecessarily restrictive lengths.
Of course, this doesn't mean making every column excessively large. You still need to consider the nature of the data you're storing and the requirements of the application.
The idea is to avoid unnecessary limitations.
These are some of the principles I personally follow when designing relational databases.
To recap, I prefer resolving many-to-many relationships through junction tables, combining one-to-one relationships where appropriate, and avoiding unnecessary mesh relationships.
I also prefer singular table names, camelCase column names, clearly defined relationships, and allowing reasonable room for column lengths.
Of course, these aren't the only considerations when designing relational databases. There are other important aspects, such as indexing, constraints, and query optimization, which are equally important.
However, the focus here isn't to cover every aspect of database design. These are simply the conventions and practices I've developed and consistently apply in my work.
Ultimately, I want a database structure that is easy to understand, predictable, and straightforward to maintain.
No comments yet
Be the first to share your thoughts on this article!