[SQL] Bijbel in database, welke structuur te gebruiken?

[TheNephilim]

Enkelen van jullie zijn vast bekend met dit boek dat behoort tot de meestgelezen boeken op de wereld. Bij wijze van bijprojectje voor mezelf, wilde ik deze eens in een database stoppen, MySQL om precies te zijn. Meteen viel me op dat deze grote hoeveelheden tekst nog wel eens een probleem kunnen opleveren als het om performance gaat.

Dus dacht ik; er moeten mensen zijn die hier een goed idee over hebben! Dus hoe structureer je dat?

De data

De bijbel bestaat uit grote hoeveelheden tekst, opgedeeld in het Oude testament en het Nieuwe testament. Elk testament bestaat vervolgens uit verschillende boeken en die bestaat weer uit hoofdstukken met verzen. Er zijn meerdere vertalingen van de bijbel en het is de bedoeling dat alle Nederlandse vertalingen in de database kunnen.

Testament
----Boek
--------Hoofdstuk
------------Vers

Eventueel kan een boek meerdere delen hebben zoals 1 Koningen en 2 Koningen. Verder zijn er niet heel veel uitzonderingen voor zover ik ze bedenken kan.
Je zou ervoor kunnen kiezen om de tekst met vers nummers ervoor gewoon plat in een TEXT veld te kunnen zetten, maar dat is niet de bedoeling. Elk vers moet apart benaderbaar zijn en als eigen entiteit behandeld worden.

What came to mind first!

MySQL Workbench

Waar ik nog niet helemaal uit ben zijn de sub-hoofdstukken. Deze horen bij een groep verzen die gerelateerd zijn of bij elkaar horen. Hiervoor zou een tabel voor gemaakt kunnen worden die begin- en eindvers opslaat en de titel.

Andere manier

Gezien de tabel `chapters` eigenlijk alleen nummers bevat, kunnen we bij `verses` ook gewoon `book_id` & `chapter` opnemen.

MySQL Workbench

Suggesties

Suggesties en tips zijn van harte welkom, ik schuw geen kritiek!

Bijlagen

*snip* geen idee wat het doel is van deze url; en met (verkapte) spam zijn we niet zo blij

[ Voor 14% gewijzigd door RobIII op 02-01-2012 15:26 ]

[GlowMouse]

Ik mis in bijna elke tabel nog een veld om de volgorde aan te geven. Hoe weet je nu in welke volgorde de hoofdstukken staan?

[Juup]

Je 1e voorstel lijkt me de beste.
Vwb sub-hoofdstukken: maak daar een nieuwe tabel voor tussen verses en chapters en maak altijd (minimaal) 1 subchapter per chapter aan.

[TheNephilim]

GlowMouse schreef op maandag 02 januari 2012 @ 14:47:
Ik mis in bijna elke tabel nog een veld om de volgorde aan te geven. Hoe weet je nu in welke volgorde de hoofdstukken staan?

Daar heb je een goed punt! Alleen `testaments` & `books` zou een volgorde veld kunnen gebruiken, `chapters` is numeriek, dus 1, 2, 3 ... etc en dat geld ook voor `verses`.

Het is uiteindelijk de bedoeling om er een PHP applicatie aan te koppen om de boel doorzoekbaar te maken en dergelijke. Dat lijkt me voor de structuur van de database verder niet belangrijk, het enige wat vervelend zou kunnen zijn is geen FULLTEXT indexes op innodb.

[GlowMouse]

TheNephilim schreef op maandag 02 januari 2012 @ 14:52:
[...]


Daar heb je een goed punt! Alleen `testaments` & `books` zou een volgorde veld kunnen gebruiken, `chapters` is numeriek, dus 1, 2, 3 ... etc en dat geld ook voor `verses`.

chapter_name is nu een string, geen integer
in verses mis ik het veld voor het versnummer

Het is uiteindelijk de bedoeling om er een PHP applicatie aan te koppen om de boel doorzoekbaar te maken en dergelijke. Dat lijkt me voor de structuur van de database verder niet belangrijk, het enige wat vervelend zou kunnen zijn is geen FULLTEXT indexes op innodb.

InnoDB krijgt binnen een jaar FULLTEXT ondersteuning, maar een bijbel is niet zo groot en dus zal LIKE %zoekwoord% ook voldoen.

[Frash]

GlowMouse schreef op maandag 02 januari 2012 @ 14:56:
[...]

chapter_name is nu een string, geen integer
in verses mis ik het veld voor het versnummer

[...]

InnoDB krijgt binnen een jaar FULLTEXT ondersteuning, maar een bijbel is niet zo groot en dus zal LIKE %zoekwoord% ook voldoen.

Sinds de release van 5.6.4 zelfs al, maar helaas duurt het vaak eeuwen voordat shared webhosts hun MySQL versie updaten.

[Gtoniser]

Ik zou voor de tweede optie gaan, qua belangrijkheid is er in de bijbel niet super veel onderscheid tussen hoofdstukken en subhoofdstukken, en niet alle hoofdstukken hebben een titel.

Je zou dan evt een aparte tabel met de subhoofdstukken kunnen doen en erin zetten in welk hoofdstuk ze zitten en van/tot welk vers een hoofdstuk loopt (vers 1 kan ook)

[TheNephilim]

Owh, dat is goed om te horen! Iets in me dacht dat dat nooit zou komen.

Er duikelen nog enkele problemen op, ik ga eens opnieuw tekenen. Behalve alle genoemde elementen zijn er ook nog meerdere vertalingen. Had ik al bedacht, maar is niet boven komen drijven zo de 1e werkdag van het jaar.

Dus je hebt een Vertaling met Testamenten met Boeken met Hoofdstukken met Verzen

@Gtoniser: Dat zou kunnen werken, al zit ik met de vertalingen. Of ik moet overal steeds de vertaling meenemen, maar dat lijkt me niet zo handig.

[Carpento]

Niet dat ik het heel zéker weet, maar even over die subhoofdstukken: neem je gemakshalve aan dat een subhoofdstuk altijd binnen een 'normaal' hoofdstuk valt ? Ik meen bijvoorbeeld dat Psalm 42 en 43 heel nauw verwant zijn, en mogelijk vallen ze dus wel binnen één subhoofdstuk maar zijn ze toch twee aparte hoofdstukken

[Gtoniser]

Ik geloof niet dat van iedere vertaling de verzen exact 1 op 1 'mappen', je zou in dat geval beter voor vertalingen aparte tabellen kunnen maken.

@Caprento, subhoofdstukken kunnen idd over meerder hoofdstukken spannen, of zoals ik al zei, niet ieder hoofdstuk heeft een aparte titel.
Je zou gewoon een tabel `subchapters` kunnen pakken, met bv `chapter_start`, `chapterverse_start`, `chapter_stop`, `chapterverse_stop`

[Rub3s]

En hoe zit het eigenlijk met vertalingen? Ook een aparte tabel van maken en een Vers een id van die tabel meegeven?

En over de subhoofdstukken: Ik denk dat het het makkelijkst is om gewoon alleen binnen een hoofdstuk de subhoofdstukken te bepalen. Scope bepalen over verschillende hoofdstukken is denk ik nogal uitleg-gevoelig. Waarschijnlijk ook weer een aparte tabel voor de subhoofdstukken maken waar je de vers van/tot kan bepalen en de (mogelijke) titel?

[TheNephilim]

Gtoniser schreef op maandag 02 januari 2012 @ 15:07:
Ik geloof niet dat van iedere vertaling de verzen exact 1 op 1 'mappen', je zou in dat geval beter voor vertalingen aparte tabellen kunnen maken.

Dat lijkt me niet handig, dan zou je voor elke vertaling een andere tabellenset moeten maken.

[Gtoniser]

Het is ook niet handig dat de verzen niet in ieder vertaling exact hetzelfde zijn, maar het is wel zo

[Carpento]

Mjah, dan hebben we het over diverse vertalingen, lijkt me in dat geval handig als de topicstarter (in elk geval voor zichzelf) definiëert wat hij met 'de Bijbel' bedoelt Welke vertaling(en), welke editie(s) ?

[TheNephilim]

Gtoniser schreef op maandag 02 januari 2012 @ 15:09:
Het is ook niet handig dat de verzen niet in ieder vertaling exact hetzelfde zijn, maar het is wel zo

Inderdaad, hier een voorbeeld: http://www.biblija.net/bi...id18=1&id37=1&l=nl&set=10

De noot vermeld ook dat er verschillen zijn. Opzich moet dit niet uitmaken, pas als je verzen gaat vergelijken is dat niet zo handig. Echter voor het weergeven van een hoofdstuk maakt het niet zoveel verschil.

@Carpento: Elke Nederlandse vertaling moet er in opgenomen kunnen worden. Ik zat dit even in de TS vermelden.

[ Voor 8% gewijzigd door TheNephilim op 02-01-2012 15:15 ]

[Carpento]

TheNephilim schreef op maandag 02 januari 2012 @ 15:14:
[...]
voor het weergeven van een hoofdstuk maakt het niet zoveel verschil.

Toch wel, zoals je hier kunt zien bij het boek Maleachi. Met name voor de weergave van hoofdstuk 3 maakt het best wat verschil

[Rub3s]

En hoe zit het eigenlijk met voetnoten? Of horen die niet officieel bij een vertaling?

[TheNephilim]

@Carpento: Misschien is het mogelijk om in een andere tabel de eventuele overloop mee te nemen. Dan kun je met een PHP frontend gewoon alsnog de verzen weergeven die horen bij een vorig of volgend hoofdstuk en dergelijke.

w.b.t de voetnoot: De vertalingen hebben inderdaad aantekeningen, check deze link: http://www.statenvertaling.nu/Aantekeningen.html

Die moeten ook meegenomen worden, maar behalve deze zijn er meerdere kanttekeningen die bij sommige verzen etc genoemd kunnen worden. Ik weet nog niet of ik daar een algemeen systeem voor wil hebben. Al is het wel handig om de 'officiële' kanttekeningen/aantekeningen/etc die bij een vertaling horen te scheiden van eigen inbreng.

[ Voor 51% gewijzigd door TheNephilim op 02-01-2012 15:22 ]

[Carpento]

Lijkt me niet eigenlijk Anders krijg je met kanttekeningen ook nog een uitdaging

[Gtoniser]

@Rubes
Je kunt inderdaad aannemen dat een subhoofdstuk aan het einde van een hoofdstuk automatisch eindigd, wat mijn punt vooral was is dat niet ieder hoofdstuk een aparte titel heeft, dus een tabel met de nummers van de hoofdstukken zou in dat geval alleen maar een rijtje getallen zijn.
Het enige waar een hoofdstukken tabel dan goed voor zou zijn is om snel een efficiente mogelijkheid te hebben om de hoofdstukken in een lijst te stoppen (als in: selecteer het hoofdstuk wat u wilt lezen).

Aangenomen dat alle vertalingen wel hetzelfde aantal hoofdstukken hebben zou je gewoon voor ieder vers een aparte TEXT kolom per vertaling kunnen doen (en verzen die niet bestaan in een vertaling NULL meegeven), maar ik denk dat het hebben van een aparte tabel `verzen` per vertaling efficiënter is.

Voetnoten zou je in een aparte tabel kunnen zetten met referentie naar een vers

[ Voor 6% gewijzigd door Gtoniser op 02-01-2012 15:20 ]

[jurp5]

Waarom losse tabel voor testament? Er zijn er maar 2 van, enum of iets dergelijks voldoet lijkt mij ook?

offtopic: Wel ironisch dat je je zelf TheNephilim noemt..

[ Voor 23% gewijzigd door jurp5 op 02-01-2012 15:33 ]

[TheNephilim]

Gtoniser schreef op maandag 02 januari 2012 @ 15:19:
Aangenomen dat alle vertalingen wel hetzelfde aantal hoofdstukken hebben zou je gewoon voor ieder vers een aparte TEXT kolom per vertaling kunnen doen (en verzen die niet bestaan in een vertaling NULL meegeven), maar ik denk dat het hebben van een aparte tabel `verzen` per vertaling efficiënter is.

Ik ben er nog niet helemaal uit of deze aanname gaat werken. Mijn gedachten op dit moment zijn om elk vers zijn eigen ID te geven en een plek in een boek en een versnummer en uiteraard een translation_id of iets dergelijks. Even kijken!

Tot zover erg bedankt voor alle reacties

@jurp5: Misschien dat ik voor de apocriefe boeken een "testament" aanmaak. Dus behalve het Oude- en Nieuwe Testament krijg je dan ook 'Apocriefe boeken'. Maar nog niks is zeker, ik moet eerst even bekijken wat de exacte relatie van de apocriefe boeken is... Wikipedia: Apocrief

Haha, ja wel toevallig hè Het blijft moeilijk een nickname bedenken, deze vond ik onderscheidend genoeg

[ Voor 24% gewijzigd door TheNephilim op 02-01-2012 15:45 ]

[Teunis]

Gtoniser schreef op maandag 02 januari 2012 @ 15:19:
Aangenomen dat alle vertalingen wel hetzelfde aantal hoofdstukken hebben zou je gewoon voor ieder vers

er zijn wel verschillen, bv. Maleachi NBV 3, STV 4 , misschien zijn er wel meer.
zeker als TS ook nog andere talen wil gaan meenemen.

Tenzij de TS ook de 'Deuterocanonieke boeken' wil meenemen zijn het er inderdaad 2

Verschillen per vers lijkt mij geen issue als je wil zoeken, en zal juist duidelijker zijn als op de orignele plek bevinden, mocht je het willen terug zoeken in een gedrukte versie.
Je kan altijd nog link tabel aanleggen als je van ene naar de andere vertaling wil schakelen of ze naast elkaar simultaan wil scrollen.

Wil je ook Titels (die soms ook halver wege versen) zoals op de website staan ook ondersteunen.
en wat wil je met eigenvoet notes gaan doen.

[ Voor 24% gewijzigd door Teunis op 02-01-2012 15:53 ]

[Beatboxx]

Kan je niet gewoon een table maken met collumns vers, hoofdstuk, tekst etc?

[TheNephilim]

Weer even een schets, de basis:



Zoals je ziet begint het bij een vertaling, deze kan meerdere testamenten bevatten, een testament kan meerdere boeken bevatten en een boek kan meerdere verzen bevatten. Je kunt vanaf een vers weer helemaal terug naar de vertaling waar hij vandaan komt.
Ik ben nog niet helemaal zeker van m'n zaak, maar het doel is dat het dynamisch is. Met de frontend moet ik per vers/hoofdstuk/boek aantekeningen toe kunnen voegen. Het moet uit elkaar getrokken kunnen worden.

Hierboven op moeten dan nog enkele functies zoals aantekeningen/voetnoten/kantekeningen/etc meegenomen worden.

@Beatboxx: Volgens mij heb ik het zo als je aangeeft uitgevoerd in bovenstaande tekening te zien is.

Probleem

De 'Deuterocanonieke boeken' zijn in geen enkele vertaling opgenomen en dat zou betekenen dat ze geen `translation_id` hebben om het maar even zo te zeggen. Moet ik nou het translation_id naar de andere kant verplaatsen? Dus alleen bij een vers en niet bij een testament...

[ Voor 15% gewijzigd door TheNephilim op 02-01-2012 16:11 ]

[Teunis]

Testamenten zijn constant, Boeken zijn constant,
enige wat veranderd per vertaling is Text van vers, vers nummer, hooftstuk, en misschien zelfs het boek.

Je kan ook in per vers je vertaling_id, boek_id opnemen. (testament kun je uit boek op halen)
nu krijg je nodeloos veel enrties in testaments en books.

TheNephilim schreef op maandag 02 januari 2012 @ 16:06:
Probleem

De 'Deuterocanonieke boeken' zijn in geen enkele vertaling opgenomen en dat zou betekenen dat ze geen `translation_id` hebben om het maar even zo te zeggen. Moet ik nou het translation_id naar de andere kant verplaatsen? Dus alleen bij een vers en niet bij een testament...

Alleen inderdaad in de laatste vertaling opgenomen.

[ Voor 43% gewijzigd door Teunis op 02-01-2012 16:27 ]

[kwaakvaak_v2]

Wat meestal goed werkt is vanaf je kleinste entiteit terug naar boven werken. Dus eerst bepalen wat je absoluut moet weten op het laagste nivo, in dit geval dus één vers. Vervolgens daar tabellen om heen maken en verder normaliseren. Database servers zijn gemaakt om veel data in te stoppen, dus de overhead die Teunis weg wil werken met constants hoeft geen issue te zijn. Wat zou kunnen is een tabel met constants en dan een relatie leggen op de plakken waar je constant nodig hebt. Maar of dit veel leesbaarder wordt waag ik te betwijfelen.

[ Voor 5% gewijzigd door kwaakvaak_v2 op 02-01-2012 17:39 ]

[Verwijderd]

Ik weet niet of het een goed idee is, maar een andere suggestie is om het aan te pakken met de volgende tabellen:

text_type(id, name, description, parent_text_type_id)
text (id, parent_text_id, content, summary)

text_type bevat dan de entries "vers", "hoofdstuk", etc.

Klopt niet helemaal, maar een soort van EAV dus.

[ Voor 20% gewijzigd door Verwijderd op 05-02-2012 17:06 ]