Ah! Gotcha! Any ideas on what this could look like? We are always looking for ways to make it easier here 
AI-generated summary
In a discussion titled āA data model to make perusing the database easier,ā users explore the idea of creating a visual data model for Discourseās database to help users understand table relationships. lju suggests a simplified model like Northwindās, useful for developers and data architects to visualize joins (e.g., users ā user_actions ā groups). osioke requests clarification, and lju explains it helps avoid manually inspecting 180+ tables and plugins. osioke notes the complexity due to ActiveRecord, lack of foreign keys, and dynamic plugin tables, but leaves the door open for solutions. Falco and sam share tools like RubyMine and rails-erd to generate models, though they question their practicality. lju proposes focusing on 15ā25 key tables (e.g., users, topics, posts) used in 90% of queries, possibly grouped by āzones of interest.ā Falco points out Data Explorer already highlights 9 core tables with schema details. niledebunkr seeks shareable diagrams and confirms users and user_options are the only 1-to-1 relationship. Dante_Dante asks if a solution was implemented and whether rails-erd can filter tables; SStrong laments the feature never materialized, affirming its usefulness. The thread remains open for community contributions to simplify Discourseās complex schema.
2 Likes
Of course! Something like the Northwind (MS Access fame!) data model would be brilliant. There may even be a tool thatāll generate one by investigating the Postgres schema
2 Likes
Can you share that like youāre talking to a noob? Like:
- What would adding this help solve?
- How would you expect to see this if it is added? How would it look?
I am basically taking your suggestion and asking for more details (as much as you can give 
) so we can improve the experience for the next Data Architect that comes around. I have no background in data, so your detailed suggestion will be helpful
2 Likes
Haha Osioke,
A very fair shout! TouchĆ©ā¦
So most folk who work with databases (not just data architects! Developers too!) find it very helpful to have some sort of a data model that shows them how the various tables are connected to each other.
For example, letās take my query ;), I needed several pieces of information about a user - I required information about a user who:
- was in (or not in) a particular group
- had solved topic
- within a certain date range
To answer the above, I need the users table, the user_actions table, and the groups table. A data model would have shown me that I can link a user to a user action via id/user_id, and to link a user to a group via their primary_group_id/id visually.
It helps to visualise not only what data is available, but how to join it, especially if there are some long-winded queries at play.
Yes you could click through every single table in the data explorer to figure out what fields are available and write them down so that you donāt forget, but having a data model may be a bit more humane for some of us
5 Likes
Ah! Gotcha again! 
I am not technical, so this was not clear to me. I do see the need though, so yes. A data model is something that would be helpful. Let me see what I can do.
In the meantime I have moved this conversation to a new topic in our Contribute > Site feedback category so we keep the other discussion clean.
2 Likes
That would be amazing, thank you!
1 Like
So I am talking with the team and this is not something that would be straightforward for so many reasons. We also moved it to Contribute > Feature because that shows more of what this would be.
Currently, those of us that work on/with data inhouse mostly use the models that are available in the source code:
I also got a look at the Northwind data model:
That is definitely easy to understand and fit on paper or one screen. 13 tables in all.
Comparing that to Discourse, we have a whole lot more tables over 180+ or more, visualising this would be ā¦ a journey. Especially because there are also tables from plugins (and these change from install to install) and data in *_custom_fields tables which should be included as well if you really want to have a complete picture.
Also because of how our database is designed, we canāt use most tools for data modeling, weād need to find one that works with ActiveRecord models. And I think that also makes this tricky too, all these data conversations are over my head.
But that is not to say this isnāt something we donāt want to do though, this is just commentary. Iād love to hear suggestions from you or anyone else on ways we could make this better.
6 Likes
Itās really not that useful, as the sheer size of it, the lack of foreign keys and we simply leaving very little logic to the RDMS means itās hard to understand Discourse DB without reading Discourse source.
But if you really need one, RubyMine can generate it for you.
14 Likes
You can generate one with relationships with rails-erd: GitHub - voormedia/rails-erd: Generate Entity-Relationship Diagrams for Rails applications Ā· GitHub
Not sure how useful this is though
10 Likes
@lju I hope all our explanations help here, especially with the added context. Iāll be closing this off in the next day or two. If you still feel you need some extra detail, feel free to ask.
3 Likes
Hi @osioke ,
Sorry for the delay in replying, Iāve been a bit jammed.
Iāve got some ideas for what would be helpful - if you can give me a few days Iāll write something up.
Cheers,
Lju
1 Like
Awesome! A few days you now have thank you for giving this attention.
Hi All,
So my argument would be that a data model would be useful, but we donāt necessarily need to include all of the tables. I suspect there are probably the ākeyā 15-25 odd tables that 90% of all queries use/what people are looking for. In fact, looking at the various tables made available - there are probably a series of data models that could be created, based on the types of queries/data you are looking to explore.
I can take a stab over the next few days to pull together what I think would be the most commonly queried tables - this wouldnāt be extensive research, just a stab in the dark. Iām sure the various questions asked in the Data Explorer category will also shine a light on the popular tables.
There could also be another diagram to represent āzonesā of interest to make navigation of different parts of the available data.
Does that make sense?
Cheers,
Lju
6 Likes
Data explorer already lists the 9 most important tables first in the query edit UI panel, and you can see all of the tables column structure and types with a click:
3 Likes
So we could take those 9 tables and turn them into a simplified data model? 
5 Likes
Sure, go ahead and share the results!
4 Likes
damn this is huge; one of the largest db schema i have seen online
was that created with https://dbdiagram.io? mind sharing the public url of the diagram?
i am more interested in the relationships and connections between these tables
users,
user_options,
api_keys,
user_api_keys,
user_auth_tokens,
user_auth_token_logs,
notifications
thanks
very helpful but will be great to have a shareable url so we can see the table relationships and also the primary/foreign keys on the tables
are there any 1-to-1 relationships in the schema? will like to know especially between users and user_options tables
anyone willing to help with the relationships between these tables? from the schema diagram
users,
user_options,
api_keys,
user_api_keys,
user_auth_tokens,
user_auth_token_logs,
notifications
interested to know if there are 1-to-1 relationships
will appreciate it..thanks
Itās mostly 1-N, as users have multiple notifications, auth tokens, etc.
user_options is 1-1.