An application has types Game (and other business units) and Message.

(Message is not a simple class with text, but a composition of a variable number of blocks like texts, images, and videos).

These types are also part of relational database, with tables named Games and Messages. The user should get a message when the game is started, and another one when the game is over. The user also gets messages related to other objects in different contexts.

How to design the data structure of messages and their relationships with the rest of the application/database?

I've already tried out some approaches, but haven't found one I'm really happy with. One approach makes the application/database very "message-centric" (where nearly all the tables reference the messages or even vice versa: the table messages references everything).

Another approach decouples messages from the rest of the database, but then I cannot use a CONSTRAINT.

A further dilemma: Many Message tables vs. an endless list of contexts in a context (or status, or event, or whatever) property/column in the Message class / messages table.

Some possible solutions:

The messages references the business unit tables:


So the Message has to know something about its context. But what if we have more than one data type that needs a Message? Not only messages for the game itself, other objects, and also for its levels (on every level the user can get a message, when the level is started, when it is over, when event X occurs...). The amount of types that need messages can grow, and the MessageType list would need to be filled with more and more values.

A further disadvantage: The messages table will have to reference every table of all types that need a Message.

enter image description here

Maybe types should reference the Message for every context?

enter image description here

OK, the messages table (or the Message class) is now free from the knowledge about all possible tables/classes, that might need it. And it's even free from the context (the property type became obsolete and was removed). But now the other tables reference the messages multiple times.

Maybe with inheritance?

Hierarchy of Messages:


Now we have got a long list of Message classes/tables. Looks a bit better. But how to organize them? In the real world we will get maybe hundreds of *Message/*_messages classes/tables.

Maybe with a combination of inheritance and messages' awareness of context?

Hierarchy of Messages with context:


The context can provide information about the status, or the current event, or something like this.

But we still have to place references to the messages into a lot of tables. What about an association table? It should connect messages with the rest of the database and make the other tables free from the references to the messages:


Actually we can even move the context from the messages to the object_message_links and make the messages completely free from any context knowledge:


Of course in this case all Messages have to be really the same and may only differ in the types and order of their content blocks.

3 Answers 3


Your proposed solution

Not every solution works in every context. Your idea to unify all these messages seems correct; but when you change the names of the entities (but not the structure), that conclusion can change.

As a simple counterexample, consider a database with Cars, People and Pets. All of these have a Name and could possibly have multiple names.
But making a unified Names table makes no sense here. There is no relation between a car's name, a person's name and a pet's name. Just because they have the same properties does not mean they are the same thing.

So the question here is whether the Message entity represents a meaningful abstraction. There are a few possibilities here, based on what you want to to with these messages.

  • If these messages are a simple string property, then I'd say no. That is essentially the same as the names example.
  • If these messages have a lot of business logic attached (e.g. placeholder, localization, translations, ...), then I'd say yes, they are an entity in and of themselves.

My preferred solution

There's also a third option here, a more middle of the road approach. These messages are essentially resources, in the same way that UI labels are often stored as resources to allow easy abstractions (e.g. localization).

The important thing to take note of in this situation is that the resource (message) is the pre-existing entity, and the consumers (game, level, ...) refer to a resource by that key.

This offers the best solution for you, in my opinion:

  • Message does not need to store every type of FK that may or may not be relevant.
  • Every entity that wishes to have messages, is therefore responsible of storing a Message-FK. This way, the responsibility of maintaining the relationship is held by whoever wishes to make use of the pre-existing message entity.
  • Consumers can reuse messages between themselves if needed. E.g. a certain global error message can be referenced by every entity that wishes to use it.

Note that you can still make messages for specific entities. E.g. if you use a string-key for the message, you could still design your messages with a key like LEVEL_GAMEOVER_MESSAGE. Theoretically, the Game class could refer to that message; but I see no need to explicitly prevent that from happening.

Minor considerations

A further consideration is also the enum you've subtly referred to. One value you use is level2_Start, which implies that you're having an issue here.

The data model suggests that you have a single enum which comprises all events from all classes (game, level, ...). I'd already advise strongly against that.

Secondly, you seem to refer to an enum value specifically for level 2. Not just any level start. I'd also advise against that. For the sake of example, let's assume a level has 2 events (start and end).

The issue here is that you'd have to create 2 new enum values every time you create a new level, which means your enum is not reusable.

public enum LevelEnum 

What's worse is that every enum value will only be used by one particular level object. That defeats the purpose of creating a Level class.

A better approach is to create your enum once, and apply it to any level:

public enum LevelEventsEnum 

And then your level class can refer to a particular event. You don't need the event to know which level # it is, because your level already knows that.

Note: While I think it's relevant to mention this, I think there's room for further improvement here, which could preclude the need for an enum altogether.

Optimizing the relationships

Given the suggested solution for messages, where a Level holds the FK to the Message (instead of the other way around), this means you can effectively drop the enum in some cases (at least for the purpose of the relationship between the entity and its messages).

There are two cases I need to cover here:

  1. A single level can hold an unspecified amount of messages.
  2. A level can hold one message for a specific event (shared by all levels).
  1. A single level can hold an unspecified amount of messages.

A simple example here: Suppose that every button you can press shows a particular message. Not every level has the same amount of buttons, and therefore not every level has the same amount of button messages. Some might not even have any buttons.

The solution here is to use a cross table:

 Levels one<-->many LevelButtonMessages many<-->one Messages

Note that you can give a LevelButtonMessage additional properties to define when to use them. This can be an enum, or other identifiers (e.g. the name of the specific button). Implement it how you see fit, but it's important to see that LevelButtonMessage obviously defines the structure only for levelbuttons, and the structure can be completely different for e.g. GameButtonMessage (a different table altogether).

  1. A level can hold one message for a specific event (shared by all levels).

As a simple example, it stands to reason that every level can be failed or passed, and therefore every level will have a single "game over" message and a single "level succeeded" message.
Note: This does not apply if levels can have several different victory conditions, but let's keep it simple for the sake of example and assume that there is a single victory message.

Here, you can use a MessageFK for every event:

public class Level
     public int LevelPassed_MessageId { get; set; }
     public int LevelFailed_MessageId   { get; set; }

This precludes the need for an enum. Instead of referring to the correct enum value, you simply refer to the appropriate property of your Level object.

  1. Note that 1. and 2. can be combined.

For example, let's say that there is a fixed message for a level's start and end (2.), but then there's an unspecified amount of messages for special things that happen in the level (1.)

public class Level
     public int LevelPassed_MessageId { get; set; }
     public int LevelFailed_MessageId   { get; set; }

     public List<LevelMessage> CustomLevelMessages { get; set; }

Nothing prevents you from sometimes using a crosstable and sometimes linking directly to the Messages table.

Is this not a code smell?

This comes across as a code smell initially, but that is not inherently the case. Every relationship, even between the same tables, can express different things. And when they express different things, that means the relationship can take a different shape.

A simple example to prove the case is a system where there are Employees and Projects. Look at the following example. All the properties are Employee-FKs, but they all express a different relationship:

public class Project
    public int ProjectManagerId { get; set; }
    public int ProjectAnalystId { get; set; }

    public List<Employee> Developers { get; set; }

There is only one project manager (= level failed message), but there is an unspecified amount of developers (= level button messages).

Just because these relationships are both between the Employees and Projects table does not mean that they need to be merged in a single relationship.

To summarize

  • Contextual meaning can influence which approach to take. Unifying everything under a single entity only matters if there is a significant amount of relation between the separate instances.
  • If messages are simple string properties, the overhead cost of abstracting a Message class is not worth the effort.
  • If messages have sufficient overlapping business logic attached to them, the overhead cost of abstracting them will save you a lot of copy/paste code.
  • You should not make enums with specific values that pertain to one particular instance of an entity. An enum should be limited to a single entity type, but the enum's values should generally be applicable to every object of that entity type (fringe exceptions are possible, but should not be the standard).
  • For single-message-events (that are shared by all entities of the same type, e.g. all levels having a single start message), I would have the entity (level) store a specific Message-FK.
  • For messages that are not shared by all entities (e.g. not every level has the same amount of level-specific messages), you can use a cross table between the entity and messages, where you are able to add axtra properties used for distinguishing between a level's list of messages.
  • It is not inherently wrong to mix-and-match the two approaches. Two entities can have multiple different relations. However, you should always first check that these relationships are meaningfully different. If they are, then it's not wrong to keep them separate.

My choice would be the last solution. It includes the link between a message and its consumer, if you will. That makes sense, since you are looking to include a lot of functionality based on that construct.

If you do need more flexibility in the Messages, you could always include a MessageProperties table with a dictionary style list of properties for messages. This would allow you to include specific properties into messages for specific consumers without the Message having any knowledge of the consumer.

  • Thanks for the answer! What would you say about this solution? So we create one association table per business unit (e.g. games_messages, levels_messages, foos_messages) and give to these tables a column context (for things like game_start, game_end level_start).
    – automatix
    Apr 11, 2018 at 10:02
  • That could also work. The upside is that you can put constraints on the relationships. The downside however is that you are not flexible when a new business unit is added. It's up to you to decide if that's a bad thing or not. It might require massive changes any way, in which case that argument is void. Apr 11, 2018 at 11:12
  • 1
    The downside however is that you are not flexible when a new business unit is added. If a new one is added, I just have to add an association table and the appropriate contexts.
    – automatix
    Apr 11, 2018 at 12:13

A further option:

One Messages table, that contains nothing non-message. Games, Levels, Foos all omit anything message-related. For each context, a link table (e.g. Game start messages) which links Messages with one of Games, Levels, Foos etc.

That is similar to your last option, but replaces object_type and context with the identity of the link table, and allows you to have pure FK relations everywhere.

If there is always exactly one message for each context, then your "Types should reference the Message for each context?" is basically the same. There is no problem with a table having multiple links to another table, as long as each link is a separate relation.

  • Thank you for your answer! If I understand you correctly, we need then an association table for each context of each business unit, right? E.g. game_start_messages, game_end_won_messages, game_end_lost_messages, game_..._messages, level_start_messages, level_end_messages, level_canceled_messages, level_..._messages. It would work, but we'll get an extremely high amount of such association.
    – automatix
    Apr 11, 2018 at 9:56
  • What I'm thinking about, is actually a combination of you solution with context. So we create one association table per business unit (e.g. games_messages, levels_messages, foos_messages) and give to these tables a column context (for things like game_start, game_end level_start).
    – automatix
    Apr 11, 2018 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.