I want to know the most effective ways to model complex relationships with DB diagrams

Ask the Community
1

Hello Community Member,

I am working on a project that involves modeling a complex database schema & I am looking for advice on how to best represent intricate relationships using DB Diagram. The database I am designing has several many to many relationships, as well as multiple hierarchical structures that I am struggling to visualize effectively.

What the best way to model these in DB Diagram to ensure clarity without overcrowding the diagram? Are there any tips for using junction tables effectively in the diagram?

I have parent child relationships between several Businesses. How can I best structure these to make the diagram intuitive and easy to understand?

Are there strategies for keeping large diagrams manageable and readable?? Also I am thinking about exporting the diagram to Nextcloud for team collaboration or possibly integrating with google cloud has anyone tried either of these?

I would greatly appreciate any advice, examples or resources you can share.

Thank you… :slightly_smiling_face:

2

What the best way to model these in DB Diagram to ensure clarity without overcrowding the diagram?

You can switch between detail levels, for example, Keys Only, when presenting your diagram to make sure the diagram is not overcrowding.

Are there any tips for using junction tables effectively in the diagram?

You can present a many-to-many relationship with our native ‘<>’ relationship type or use two alternative one-to-many relationships with a junction table, depending on whether you want to communicate the junction tables. We also have a tutorial on this topic.

I have parent child relationships between several Businesses. How can I best structure these to make the diagram intuitive and easy to understand?

I’m not sure about this, can you share more about this?

Are there strategies for keeping large diagrams manageable and readable??

You can put tables into table groups and collapse/expand them to make the diagram easier to manage and read.

Hope the information helps.

3

Table groups is great, recommend using these. When Dimensional Modelling I found it better to limit your fact to these.

I wish you could name relationships and have the names of appear on the relationship lines when documenting.

1 Like
4

Hi @tommy,

Currently, you can name the relationships using the below syntaxes:

// Long form
Ref name_optional {
  schema1.table1.column1 < schema2.table2.column2
}

// Short form:
Ref name_optional: schema1.table1.column1 < schema2.table2.column2

However, the diagram will not show the relationship names at the moment. We take note of the suggestion and will look into it in the future.

Best Regards
Huy Phung.

1 Like
5

This would be a very good enhancement.(adding the name onto the diagram itself). Particularly in dimensional modelling - its common to have a conformed dimension join to many facts across multiple subject areas

1 Like
6

Hi @Tommy ,

Thank you for your reply.

its common to have a conformed dimension join to many facts across multiple subject areas

Can you explain more about this? Maybe it can help us gain more information on brainstorming and considering implementing the feature.

7

Hi Huy,

I am referring to Data Warehouse design - top down using dimensional modelling techniques. Part of dimensional modelling is to identify “dimensions” or tables which hold information about facts(typically events).

For ease of use and to ensure one consistency, these tables are re-used often across entities. For example a business may use a “user” dimension to store staff or customer details which could then be used across operational, sales, hr subject areas. This would mean say if user information is in multiple source systems, it would be conformed into the one table for consistency.

I found removing all dimensions from your table groups helped with presentation(as i only wanted the one table group in the table group view on dbdocs), but really naming relationships on the diagram would be even more helpful - particularly for the large wiki view. This is a feature of some other mature modelling tools.

Another dimension that is joined virtually everywhere is a calendar dimension - used for simplifying queries to gather data for specific date periods.

Read here for some more information: Dimension (data warehouse) - Wikipedia

1 Like
8

Hey @Tommy

I’m Patrick - the Product Manager here at dbdiagram. I understood that you’re mentioning Kimball Dimensional Modeling, which I found interesting.

I definitely want to learn more about your case, I’d love see the data models that your team is currently building - you know, to get a sense of the complexity, and how dbdiagram could help with that.

You can send me the example diagram via my email phat.nt@holistics.io so I can take a look :blush:

Perhaps setting a quick call with me via this link is a great option if you prefer video calls. You can book a chat here with this link.

Patrick