Data modeling questions

[shanebo]

I've noticed some data model/structure irregularities and inconsistencies (totally understandable given the data size/scope). For examples:

1. sometimes non uids are used as foreign keys (e.g., writers are personLookup rather than uid)
2. sometimes props/keys for things that can only be one object (father, mother, etc.) are arrays instead of string foreign keys
3. some number props/keys are strings when they could be integers
4. some keys are all caps, while most are camelCase, and a few are snake_case
5. arrays of relationships are embedded in some types while others they aren't (e.g., book has writers but a person in people doesn't have associated books array of ids)

Are these things you're open to refining, or has that ship sailed?

[robertrouse]

I am interested in refining the model and cleaning up some of these things above. At the moment my focus is on expanding the timeline data to include all the events in the Gospels.

For now, here are my comments on the points above:

1. The writers formatting is an artifact of using Airtable. In the books data, it's a rollup of values from the chapters data. Unfortunately this rollup doesn't support revealing underlying uids.
2. There should never be more than one person in the father/mother field. That looks like a data entry error. Could you point me to an example or two?
3. The numbers exported as strings might be related to the Airtable API. Which fields are you looking at?
4. The style differences are due to many iterations of these datasets over the years. It could use some standardization.
5. The linkage to writers is defined at the chapter level since Psalms has different writers for different chapters. I haven't included a process to derive book names and hesitate do so because it is incorrect to have data indicating something like "David wrote the book of Psalms."

It may be useful for you to browse the source data in Airtable here: https://airtable.com/app5UK4s70d5PwupX/shrlkhIzIUzmx9whR

[shanebo]

Thanks for your response.

1. 👍
2. My apologies for the confusion. I haven't seen any father/mother fields with more than one item in the array. My point was more wondering why an array was chosen as the type for that field when it could just be a foreign key string.
3. Some examples, easton table (startDate, verseSort), periods table (yearNum), places (openBibleLat, openBibleLong, recogitoLat, recogitoLon), verses table (verseNum (which might be fixed in newer data).
4. 👍. Do you have source files that you run migrations on, or is everything set manually in air table and then exported to json? I ask because I have written a lot of migrations from your dataset to try to standardize and cleanup somethings.
5. Yeah that makes sense. Although I think it'd be accurate to say David was one of the many writers of the Psalms. At any rate, I was just using that as a for instance. My point is more about representing many to many relationships in json documents. Usually both tables' items would have the ids of the other tables' ids (like how you've done with people and verses).

Yes I've browsed the airtable data. Very helpful thank you.

Again, thanks for this open source project brother.

[Peashooter8890]

Cleanup Proposal

After analysis, I made a draft here of a practical proposal on implementing all the points that @shanebo brought up. @robertrouse please let me know which of the things in my proposal is invalid / not OK and I will remove it and re-submit by editing this message.

Once a final version of proposal becomes accepted, I will submit the python script for the json dumps notebook that will intercept the default airtable JSON and will automate all changes mentioned here.

Name Change (camelCase) Proposal

chapters.json

• "writer count" -> "writerCount"

easton.json

• "def_id" -> "defId"
• "has_list" -> "hasList"

events.json

• "people (from verses)" -> "people"
• "places (from verses)" -> "places"

people.json

• "Disambiguation (temp)" -> "disambiguation"
• "Easton's Count" -> "eastonsCount"
• "personID" -> "personId"

peopleGroups.json

• "events_dev" -> "eventsDev"

periods.json

• "BC-AD" -> "eraType"

places.json

• "duplicate_of" -> "duplicateOf"
• "recogitoUID" -> "recogitoUid"

Data Type Change Proposal

events.json

• verseSort (string) -> number
  Script verified that all current entries are numbers like '01001001', '01002004', ... etc
• predecessor (array[string]) -> string
  Script verified that all 374 current entries of this field are arrays of size 1. Given the chronological nature of events, it seems unlikely that there will be more than one predecessor?

periods.json

• yearNum (string) -> number
  Script verified that all current entries are numbers like '-4004', '-3941', ... etc

easton.json

• matchSlugs (string) -> array[string]
  Currently, matchSlugs is either a regular string like "abdon_10" or a string that represents an array like "['abdon_3', 'abdon_11']". It would be more consistent for everything to be a literal array of strings.

people.json

• birthYear (array[string]) -> string
  It does not make sense that someone is born at more than one date.
• deathYear (array[string]) -> string
  Script verified that all 64 current entries are arrays of size 1. I do see how someone could die more than once if they are someone like Lazarus, Jarius's Daughter, etc but the data currently does not seem to have any instances (arrays of size 2 or greater) where it captures this.
• birthPlace (array[string]) -> string
  Script verified that all 77 current entries are arrays of size 1. If someone was born on the border of two or more places, they could have more than one birth place, but this seems unlikely for this dataset to contain.
• deathPlace (array[string]) -> string
  Script verified that all 45 current entries are arrays of size 1. If someone died on the border of two or more places, they could have more than one death place, but this seems unlikely for this dataset to contain.
• mother (array[string]) -> string
  Script verified that all 200 current entries are arrays of size 1. Mr. Rouse has verified on January 18 on github that "There should never be more than one person in the father/mother field".
• father (array[string]) -> string
  Script verified that all 1584 current entries are arrays of size 1. Mr. Rouse has verified on January 18 on github that "There should never be more than one person in the father/mother field".
• eastons (array[string]) -> string
  Script verified that all 1817 current entries are arrays of size 1.
• dictText (array[string]) -> string
  Script verified that all 1817 current entries are arrays of size 1.
• alsoCalled (string) -> array[string]
  Example is "Ner,Jehiel". Better as array[string].
• openBibleLat (string) -> number
  Script verified that all current entries are numbers like '33.545097', '31.7613578', etc.
• openBibleLong (string) -> number
  Script verified that all current entries are numbers like '36.224661', '35.74614824', etc.
• recogitoLat (string) -> number
  Script verified that all current entries are numbers like '41.97858', '33.75', '-9.29722', etc.
• recogitoLon (string) -> number
  Script verified that all current entries are numbers like '34.011', '35.75', '-77.61778', etc.
• latitude (string) -> number
  Script verified that all current entries are numbers like '33.545097', '31.7613578', etc.
• longitude (string) -> number
  Script verified that all current entries are numbers like '36.224661', '35.74614824', etc.
• aliases (string) -> array[string]
  An array of strings would better fit the aliases data for Jerusalem ("Zion, Jebus, Salem, City of God").
• recogitoType (string) -> array[string]
  Example is "temple-2,tomb,arch,church,settlement,bath". Better as array[string].
• hasBeenHere (string) -> array[string]
  Example is "paul_2479, timotheus_2863, silas_2740". Better as array[string].

peopleGroups.json

• partOf (array[string]) -> string
  Script verified that all 8 current entries are arrays of size 1. It feels unlikely that a group will be part of two or more larger groups?

places.json

• eastons (array[string]) -> string
  Script verified that all 966 current entries are arrays of size 1.
• dictText (array[string]) -> string
  Script verified that all 966 current entries are arrays of size 1.
• rootID (array[string]) -> string
  Script verified that all 672 current entries are arrays of size 1. It feels unlikely that a place will have more than one root place?
• duplicate_of (array[string]) -> string
  Script verified that all 27 current entries are arrays of size 1. It feels unlikely that a place will be a duplicate of more than one place?

verses.json

• book (array[string]) -> string
  Script verified that all 31102 current entries are arrays of size 1. This makes sense as a verse should only be linked to one book.
• chapter (array[string]) -> string
  Script verified that all 31102 current entries are arrays of size 1. This makes sense as a verse should only be linked to one chapter.
• verseNum (string) -> number
  Script verified that all current entries are numbers like '1', '2', ... etc
• verseID (string) -> number
  Script verified that all current entries are numbers like '01001001', '01001002', ..etc

chapters.json

• book (array[string]) -> string
  Script verified that all 100 current entries of this field are arrays of size 1. This makes sense because chapters like ex. "1Thess.4" can only be linked to one book.

Data Value Change Proposal

books.json

• Make "writers" reference id of people in people.json.
  Currently, "writers" references personLookup instead of id.

easton.json

• Make "writers" reference id of person in people.json.
  Currently, it references personLookup instead of id.
• Make "matchSlugs" reference id of place in places.json.
  Currently, it references placeLookup instead of id.

places.json

• Make "hasBeenHere" reference id of people in people.json
  Currently, it references personLookup instead of id.