I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that nearly every type of information relating to a single "Entry" can have more than one value. Here is the outline of the database, in terms of how the tables relate to one another:

  • Entry
    • Keyword
      • Keyword_Orthography
      • Keyword_Priority
    • Reading
      • Reading_Orthography
      • Reading_Priority
      • Reading_ReadingToKeywordRestriction
    • Sense
      • Sense_Antonym
      • Sense_CrossReference
      • Sense_Dialect
      • Sense_Field
      • Sense_Gloss
      • Sense_GlossType
      • Sense_LanguageSource
      • Sense_Misc
      • Sense_PartOfSpeech
      • Sense_SenseRestrictedToKeyword
      • Sense_SenseRestrictedToReading
      • Sense_Translation

Every table has a one-to-many relationship with the tables below it in the hierarchy - each Entry can have multiple Keywords, Readings and Senses, every Keyword can have multiple Keyword_Orthography and Keyword_Priority values, and so on.

So visualising even a single Entry seems like a complicated task. I could (probably) write a query that puts all of the information in a single table, but it will lead to massive redundancy if, for example, I'm displaying the same "Entry" five times in order to display all five of the "Sense_Misc" values which relate to it. Which, I gather, is the whole point of using a relational database instead of a spreadsheet.

Which leads me to my question:

In a situation like this, how do you effectively visualise all data across a complex database, not just one or two tables?

The best I can do right now is write individual queries to display, for example, each Keyword for a single Entry, or each Reading_Orthography for a single Reading. But I don't know what the best practice would be if I want to see the whole of the database across all of the tables.

What I really need is something that can neatly sum up a single "Entry" including all of the related information, and hopefully some way of summing up all of the Entries in a single table. Is there a method for concatenating values in a single query, for example?

I realise my question seems broad, so if the specific question about my situation seems unanswerable, I'll stick to the more general question: what is the best practice for visualising a relational database comprising of many tables in one place?

  • Why would an "ER Diagram" not work? Commented Jan 3, 2020 at 18:45
  • Because I need to see the data itself, not the structure of it. I know the structure, I want to find a good way to display all the data for each entry in the database.
    – Lou
    Commented Jan 3, 2020 at 18:54
  • 1
    You could design an Access Form or Report, or use a tool like Power BI Desktop powerbi.microsoft.com/en-us/desktop Commented Jan 3, 2020 at 19:05
  • @DavidBrowne-Microsoft - I was under the impression that Forms were write-only tools, and wouldn't be suitable for reading data? Also, with Reports, I'm not sure if I would have the same problem as a Query - with repeated rows and redundancy because of the number of one-to-many relationships in the linked tables.
    – Lou
    Commented Jan 4, 2020 at 11:49


Your Answer

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