I have a project where I am making the Ancient Egyptian language dictionary. For this dictionary, besides words and translations, I want to have a reference for every entry, so the dictionary can be much reliable as possible.

For this situation, I divided references into 3 groups:

  • Primary reference (wall, tomb, pyramid, bowl, papyrus scroll... from which word comes directly)
  • Secondary reference (standard references as books, journal articles, Ph.D. thesis... which contains/mention a primary reference)
  • Tertiary reference (in 99% of cases an existing Ancient Egyptian language dictionary which contains/mention secondary reference, or very rarely primary reference)

So, the dictionary entry (hieroglyphic word, translation) can have one or more of those references. Also, dictionary entry can have a primary reference, secondary and tertiary, primary reference could be without citation in the secondary/tertiary reference existing into database and vice versa.

The primary reference would have only a few descriptors as:

  1. ID
  2. Name
  3. Alternative name
  4. Hieroglyphic or hieratic (boolean)

The problem is when I come to the secondary and tertiary references (technically the same thing). I am not quite sure how to store those into the database and how to connect those to primary ones, and how to connect those with words/translations/... I am thinking about using BibTex Fields and Entries to make a table which will store secondary and tertiary references, but I am don't know how to organize this to work for my needs.

Can you give me suggestion how could I approach to this problem, and what is the practice when saving references into MySQL database?

  • 1:many relationship? Or many:many? Does the database need to care about "primary" vs secondary vs tertiary? – Rick James Mar 8 '19 at 23:29
  • Many-to-many in all three cases (primary, secondary, tertiary). In most times, secondary references contains primary, rarely secondary. Tertiary references in most cases contains secondary references, rarely primary. Can you explain last question, I don't understand "care about" part? – Boris J. Mar 11 '19 at 14:32
  • I don't understand "primary", etc. Can you give some SQL to show what is going on? See this for tips on efficient implementation of many:many. – Rick James Mar 11 '19 at 14:53
  • I described in my question what "primary, secondary and tertiary references" mean. I do not have SQL because I am trying to get idea how to make tables which store those three types. – Boris J. Mar 11 '19 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.