I have 4 entities: Event, Message, Flow and Document.
Event table stores a limited (seeded) number of records. Message has many events and each event can be related to many messages. The name event_message was given for the intermediate table.
As you can see, the convention for intermediate tables are: {tablename}_{tablename}.
Flow table stores a limited (seeded) number of records. Message has many flows and each flow can be related to many messages. The name flow_message was given for the intermediate table.
A document is created on each relation between Flow and Message (each record on flow_message).
The issue starts here:
Each event on a message has different documents by flow. It means: for each new record on intermediate table flow_message, each record on intermediate event_message has a new document related.
To solve this, I created an intermediate table between event_message and flow_message named: event_message_flow_message.
Is this correct (in some conventional way)? Is this modeling correct?
How to proper model and naming the intermediate table derivative by two others intermediate tables?
Best Answer
As I assume naming the relationship has some value, possibly, to communicate the model to business people, it needs to have a better name. It's not easy to guess all the details from your description, but necessity for the relationship either hints that the model it represents either does not capture all important information (for example, are you missing some important Entity? Maybe, all three intermediates can be put together to one, sacrificing normalization? Etc) or the relationship is quite artificial, solution-domain one.
To rephrase, if the relationships do not match any reasonable business domain ones, then try to find if the domain has something else instead. Ask domain experts on how they relate flow, event, document and message - maybe, they mention something useful. Then you can capture that knowledge in the model. This will be the best outcome, because relationship will get natural name. If what you are doing is heavily in the solution domain due to some optimization concerns or simply because it's too abstract, I'd still recommended to name relationships to reflect their intention. For example, from your diagram it is not at all clear what kind of association event_message and flow_message hide - even less so when you add event_message_flow_message. At least, give it a try to find more meaningful name. One thing is certain: it's impossible to suggest you a name (or even approve addition of those as you call them intermediate relationships) without knowing your business domain.
I'd recommended to at least skim through the book "Modelling Business Information - Entity relationship and class modelling for Business Analysts" by Keith Gordon, published by BCS Learning & Development Limited, 2017.
For example, from the book, "Although many-to-many relationships and associations are legitimate constructs to have on a model, they can pose a problem as they may be hiding some important information that the business should be interested in recording. It is the job of the analyst to test each of these many-to-many relationships and associations to see what information, if any, they are hiding." And you find more wisdom there on modelling business information, exactly as the name of the book suggests.