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
- Keyword
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?
