Relational Database Design: My Approach to Structuring Tables and Relationships

Table of contents
When designing relational databases, I follow certain principles that influence how I structure tables, define relationships, and name columns. In this article, I'll share some of the conventions I use and why I prefer them.

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.

1. Resolve Many-to-Many Relationships Using Junction Tables

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:

  1. student
  2. course
  3. enrollment

The enrollment table contains foreign keys referencing both the student and course tables.

A junction table connects students and courses, replacing a direct many-to-many relationship with two one-to-many relationships.
Resolving many-to-many relationships

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.

2. Combine One-to-One Relationships Where Possible

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.

3. Avoid Unnecessary Mesh Relationships

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.

An e-commerce database organized around customers, orders, and products, with clearly defined relationships between the different entities. Each connection represents a specific relationship, predominantly one-to-many.
Organizing Relational Database Relationships

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.

4. Use Singular Table Names

This is a naming convention I consistently follow.

I prefer using singular names when naming my tables.

For instance:

  • user instead of users
  • product instead of products
  • order instead of orders
  • payment instead of payments
  • transaction instead of transactions

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.

5. Use camelCase for Column Names

Another personal preference is using camelCase when naming columns.

For example:

  • firstName
  • lastName
  • middleName
  • createdAt
  • updatedAt
  • paymentStatus
  • customerId

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.

6. Keep Relationships Clear and Properly Defined

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.

7. Allow Reasonable Room for Column Lengths

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.

Conclusion

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.

About the author

Barasa Michael Murunga

Barasa Murunga Author

Founder

Barasa Murunga Michael is a backend engineer and technical lead based in Nairobi, Kenya, and the founder of Jisort Ublow. He designs, builds and deploys Python applications and API-driven systems using Flask, FastAPI and Django, with particular depth in payment integrations such as M-PESA Daraja and Pesapal. He also works with retrieval-augmented generation (RAG) and LLM tooling to bring intelligent features into real products. Through Jisort Ublow, he mentors developers and writes practical, implementation-focused guides on Python, backend architecture and payment integration. He holds a BSc in Informatics and Computer Science (First Class Honours) from Strathmore University.

See author profile

Discussion (0)

Be the first to comment

Join the discussion

Share your thoughts and engage with the community

Your comment will appear immediately

No comments yet

Be the first to share your thoughts on this article!