Here you will see SQL, XML, CSV, and JSON. Of special note is the cross-reference table (quite extensive and amazing) and a simple verse-id system for fast queries.
EDIT: Notice the ids of the tables are book-chapter-verse combinations, always unique.
CREATE TABLE IF NOT EXISTS `bible` (
`id` int(11) NOT NULL AUTO_INCREMENT, --Global unique number or verse
`book` varchar(25) NOT NULL, --Book, chapter, verse is the combined primary key
`chapter` int(11) NOT NULL,
`verse` int(11) NOT NULL,
`section_title` varchar(250) NOT NULL, -- Section title, A section starts from this verse and spans across following verses until it finds a non-empty next section_title
`foot_note` varchar(1000) NOT NULL, -- Store foot notes here
`cross_reference` int(11) NOT NULL, -- Integer/Array of integers, Just store `id`s of related verses
`commentary` text NOT NULL, -- Commentary, Keep adding more columns based on commentaries by difference authors
`AMP` text NOT NULL, -- Keep, keep, keep adding columns and good luck with future expansion
`ASV` text NOT NULL,
`BENG` text NOT NULL,
`CEV` text NOT NULL,
PRIMARY KEY (`book`,`chapter`,`verse`),
KEY `id` (`id`)
)
哦，小型大写字母和红色字母怎么样？
嗯，小型大写字母和红色字母怎么样？您可以使用 HTML 或适当的格式将红色字母存储在版本列中。在界面中，您可以根据用户的选择将其剥离，无论他需要红色字母还是小型大写字母。
SQL is the BEST way to store this. Considering your requirement we can split them into two major parts
Information that's dependent on individual version
Small caps
Red letter print
Information that isn't dependent on individual version
Book, Chapter, Verse numbers
Section title
Foot notes (??????)
Cross Reference
Commentary
For various reasons I prefer to store the whole bible project into one SINGLE table, Yes call it as bible
For your visual here is my screen I have stored nearly 15 versions of bible in single table. Luckily the different version names are just kept as column wide. When you add more version in future your table grows horizontally which is okay thus the # of rows remain constant(31102). Also, I will request you to realize the convenience of keeping the combination of ('Book, Chapter, Verse') as the PRIMARY key because in most situations that's the look-up way.
That said here is my recommended table structure.
CREATE TABLE IF NOT EXISTS `bible` (
`id` int(11) NOT NULL AUTO_INCREMENT, --Global unique number or verse
`book` varchar(25) NOT NULL, --Book, chapter, verse is the combined primary key
`chapter` int(11) NOT NULL,
`verse` int(11) NOT NULL,
`section_title` varchar(250) NOT NULL, -- Section title, A section starts from this verse and spans across following verses until it finds a non-empty next section_title
`foot_note` varchar(1000) NOT NULL, -- Store foot notes here
`cross_reference` int(11) NOT NULL, -- Integer/Array of integers, Just store `id`s of related verses
`commentary` text NOT NULL, -- Commentary, Keep adding more columns based on commentaries by difference authors
`AMP` text NOT NULL, -- Keep, keep, keep adding columns and good luck with future expansion
`ASV` text NOT NULL,
`BENG` text NOT NULL,
`CEV` text NOT NULL,
PRIMARY KEY (`book`,`chapter`,`verse`),
KEY `id` (`id`)
)
Oh, What about the Small caps and Red letters?
Well, Small caps & Red letters you can store in version columns using HTML or appropriate formats. In the interface you can strip them off based on user's choice whether he requires red letter or small caps.
For reference, you can download the SQLs from below and customize in your way
您可以考虑使用“圣经 SDK”，例如存储文本的 AV Bible，而不是重新发明轮子，以开放的、自定义的二进制格式进行格式、节数等。
我认为他们拥有您列出的所有内容，除了交叉引用。
Rather than reinventing the wheel, you might consider using a "Bible SDK" such as AV Bible, which stores text, formatting, verse numbers, etc. in an open, custom binary format.
I think they have everything you've listed except cross-references.
一切 WernerCD 的答案，但将 verseText 存储为 xml，以便您可以添加格式化标签，如 例如 Red Text 并使用这些标签在您的应用程序中对其进行格式化
Everything WernerCD's answer, but store the verseText as xml so you can add formatting tags like <red>e.g. Red Text</red> and use the tags to format it in your application
Mark Rushakoff's answer is probably the best for you specific need. However more generally if need to store content that either has data within the content or if you need to store data about the content a Content Management System is typically used. You can build your own (which WernerCD's answer had a table structure for) or use a CMS product. The list here shows the wide variety of technologies used (around 30 in this list use MySQL)
，因为它取决于独特的内容，最好将其存储在自己的表中。如何识别它并将其放置在内容中可以使用内容中的静态参考点，例如 x 个字符数或 x 个单词数，然后再次使用外键与内容链接。因此，脚注表可能类似于 Primary_id、foreign_id、reference、footnote，数据示例可能是 2304、452、64，“有些手稿不包含此内容”。主键是2304，链接到内容表的外键是452，脚注放置在64个字符中，脚注是“有些手稿不包括这个”至于A，B，C这样的递增脚注或者1、2、3所有这些都可以动态生成。如果静态字母/数字很重要，那么您可能希望将其包含在数据中，但我宁愿拥有允许自动执行此操作的良好数据，然后将其列为静态数据。
expanding the DB horizontally isn't very efficient with the potential of having very large tables and complex updates. so id, book, chapter, verse, V1, V2, V3, V4... Vn just seems to be looking at the problem like a spreadsheet rather than taking advantage of what a DB has to offer.
the references are static (book, chapter, verse) so they can be populated in one table with an id and with that you have the framework of the entire bible. the verse content can potentially have hundreds of versions so it would be better stored in its own table and linked with a foreign key to identifying the references. the structure would be primary_id, foreign_id, version, content.
now the content just fills in as needed and there is no need to have thousands of empty fields that in the future you have to go back and fill in or needing to expand the table and backfill all the existing data every time you add a new version. just fill in the verses as you get them which works better I think if you building it yourself.
This also makes sense as some versions only have the NT or some verses that they think were added later aren't available so there is no need to have empty fields you just have the data and it links to the verse reference. "version" can also be a foreign key to identify more information in the version like a publishing date or long/short name (ie. "NIV", "New International Version") This also works well when using more than one revision of a translation like the 1984 NIV vs 2011 NIV. Both can be identified as "NIV" but differ in content so the version_id can link another table with expanded information about the version it's using. Once that data is in and linked properly you can display it however you wish for example combining the publishing date/short version name making a name like "NIV1984" or have a separate column unique for a display name.
I'm not sure how red letter or footnotes could be displayed and I know sites like biblegateway have this as a toggle switch so it's nice to have the option to sort it like this. with red letters, this could be a special static identifier directly in the verse content that is parsed out later as a CSS identifier. It could be its own foreign table too but since it is so little a delimiter would be really easy. It really depends what you're using the data for and if you wanted to do queries for the red letters then it would be best as a foreign table (fast) rather then search the db for the delimiter (slow)
with footnotes, since it depends on unique content it would be best stored in its own table. how it is identified and placed in the content could use static reference points within the content like x number of characters in or x number of words in and then linked with the content using a foreign key again. So the footnote table could be something like primary_id, foreign_id, reference, footnote and an example of the data could be 2304, 452, 64, "some manuscripts don't include this". Primary key would be 2304, the foreign key that links to the content table is 452, the footnote is placed 64 characters in, and the footnote is "some manuscripts don't include this" As for the incrementing footnote like A, B, C or 1, 2, 3 all of this can be dynamically generated. If it's important to be a static letter/number then you might want to include it in the data but I would rather have good data that allows this automatically then list it as static data.
here's the hint, Don't add hundreds of columns... that would just a headache and it's spreadsheet thinking. it's better to work through the perfect queries to link tables with the right content.
You may need to split the verse_number column into verse_start and verse_end. As example, the 1 Chronicles 6:78 in an Ilocano Bible is merged with verse 79. Therefore, the database must be able to handle a Single verse with 1 Chronicles 6:78-79 as reference. Posting the link for reference. https://ebible.org/study/?w1=bible&t1=local%3Ailoulb&v1=R16_78
As discussed, verse number (start and end) may be Bible version specific. It all depends on the Bible versions you are working on.
发布评论
评论（9）
这是另一个适合您的集合/示例：
https://github.com/scrollmapper/bible_databases
在这里您将请参阅 SQL、XML、CSV 和 JSON。特别值得注意的是交叉引用表（相当广泛且令人惊叹）和一个用于快速查询的简单诗句 ID 系统。
编辑：请注意，表的 id 是书籍-章节-诗句的组合，始终是唯一的。
Here is another collection / example for you:
https://github.com/scrollmapper/bible_databases
Here you will see SQL, XML, CSV, and JSON. Of special note is the cross-reference table (quite extensive and amazing) and a simple verse-id system for fast queries.
EDIT: Notice the ids of the tables are book-chapter-verse combinations, always unique.
SQL 是存储它的最佳方式。考虑到您的要求，我们可以将它们分为两个主要部分
依赖于各个版本的信息
不取决于个别版本的信息
由于各种原因，我更喜欢将整个圣经项目存储到一个表中，是的，将其称为
圣经为了您的视觉效果，这是我的屏幕，我在单个表中存储了近 15 个版本的圣经。幸运的是，不同的版本名称只是保留为列宽。当您将来添加更多版本时，您的表水平增长，这是可以的，因此行数保持不变（31102）。另外，我会要求您认识到将（“Book，Chapter，Verse”）的组合保留为主键的便利性，因为在大多数情况下这是查找方式。
这就是我推荐的表结构。
哦，小型大写字母和红色字母怎么样？
嗯，小型大写字母和红色字母怎么样？您可以使用 HTML 或适当的格式将红色字母存储在版本列中。在界面中，您可以根据用户的选择将其剥离，无论他需要红色字母还是小型大写字母。
作为参考，您可以从下面下载 SQL 并按照您的方式自定义
SQL 格式的圣经
SQL is the BEST way to store this. Considering your requirement we can split them into two major parts
Information that's dependent on individual version
Information that isn't dependent on individual version
For various reasons I prefer to store the whole bible project into one SINGLE table, Yes call it as
bibleFor your visual here is my screen I have stored nearly 15 versions of bible in single table. Luckily the different version names are just kept as column wide. When you add more version in future your table grows horizontally which is okay thus the # of rows remain constant(31102). Also, I will request you to realize the convenience of keeping the combination of ('Book, Chapter, Verse') as the PRIMARY key because in most situations that's the look-up way.
That said here is my recommended table structure.
Oh, What about the Small caps and Red letters?
Well, Small caps & Red letters you can store in version columns using HTML or appropriate formats. In the interface you can strip them off based on user's choice whether he requires red letter or small caps.
For reference, you can download the SQLs from below and customize in your way
Bibles in SQL format
您可以考虑使用“圣经 SDK”，例如存储文本的 AV Bible，而不是重新发明轮子，以开放的、自定义的二进制格式进行格式、节数等。
我认为他们拥有您列出的所有内容，除了交叉引用。
Rather than reinventing the wheel, you might consider using a "Bible SDK" such as AV Bible, which stores text, formatting, verse numbers, etc. in an open, custom binary format.
I think they have everything you've listed except cross-references.
我还发现 http://www.lyricue.org/downloads/ 其中包含 mysql 中的几个圣经翻译格式。
I also found http://www.lyricue.org/downloads/ that includes several bible translations in mysql format.
该存储库包含以 sql 形式给出的整本圣经。
https://github.com/godlytalias/Bible-Database
This repository contains entire Bible given in sql.
https://github.com/godlytalias/Bible-Database
一切 WernerCD 的答案，但将 verseText 存储为 xml，以便您可以添加格式化标签，如
例如 Red Text Everything WernerCD's answer, but store the verseText as xml so you can add formatting tags like
<red>e.g. Red Text</red>and use the tags to format it in your application马克·鲁沙科夫（Mark Rushakoff）的答案可能最适合您的特定需求。但是更一般地说，如果需要存储内容中包含数据的内容，或者需要存储有关内容的数据，则 内容管理系统。您可以构建自己的（WernerCD 的答案有一个表结构）或使用 CMS 产品。这里的列表显示了所使用的各种技术（此列表中大约 30 种使用 MySQL）
Mark Rushakoff's answer is probably the best for you specific need. However more generally if need to store content that either has data within the content or if you need to store data about the content a Content Management System is typically used. You can build your own (which WernerCD's answer had a table structure for) or use a CMS product. The list here shows the wide variety of technologies used (around 30 in this list use MySQL)
水平扩展数据库并不是很有效，因为可能会出现非常大的表和复杂的更新。所以 id、book、chapter、verse、V1、V2、V3、V4...Vn 似乎只是像电子表格一样看待问题，而不是利用数据库提供的功能。
参考文献是静态的（书籍、章节、诗句），因此可以使用 id 将它们填充在一个表中，这样您就拥有了整本圣经的框架。经文内容可能有数百个版本，因此最好将其存储在自己的表中，并与外键链接以识别引用。该结构将是primary_id、foreign_id、version、content。
现在内容只需根据需要填写，无需有数千个空字段，将来您必须返回并填写或需要在每次添加新版本时扩展表格并回填所有现有数据。只要填写你得到的诗句，我认为如果你自己构建它，效果会更好。
这也是有道理的，因为某些版本只有新台币，或者他们认为后来添加的一些经文不可用，因此不需要有空字段，您只需拥有数据并链接到经文参考。 “version”也可以是一个外键，用于标识版本中的更多信息，如发布日期或长/短名称（即“NIV”、“新国际版本”）。当使用一个版本的多个修订版时，这也很有效。翻译如 1984 NIV 与 2011 NIV。两者都可以标识为“NIV”，但内容不同，因此 version_id 可以链接另一个表，其中包含有关其所使用版本的扩展信息。一旦数据输入并正确链接，您就可以按照您的意愿显示它，例如将发布日期/短版本名称组合起来，形成“NIV1984”之类的名称，或者为显示名称设置一个唯一的单独列。
我不确定如何显示红色字母或脚注，而且我知道像 biblegateway 这样的网站将其作为切换开关，因此很高兴能够选择像这样对其进行排序。用红色字母表示，这可能是直接在诗句内容中的特殊静态标识符，稍后将其解析为 CSS 标识符。它也可以是它自己的外部表，但由于它很小，所以分隔符真的很容易。这实际上取决于您使用数据的目的，如果您想查询红色字母，那么最好作为外部表（快），而不是使用脚注在数据库中搜索分隔符（慢）
，因为它取决于独特的内容，最好将其存储在自己的表中。如何识别它并将其放置在内容中可以使用内容中的静态参考点，例如 x 个字符数或 x 个单词数，然后再次使用外键与内容链接。因此，脚注表可能类似于 Primary_id、foreign_id、reference、footnote，数据示例可能是 2304、452、64，“有些手稿不包含此内容”。主键是2304，链接到内容表的外键是452，脚注放置在64个字符中，脚注是“有些手稿不包括这个”至于A，B，C这样的递增脚注或者1、2、3所有这些都可以动态生成。如果静态字母/数字很重要，那么您可能希望将其包含在数据中，但我宁愿拥有允许自动执行此操作的良好数据，然后将其列为静态数据。
这是提示，不要添加数百列......这只会让人头疼，而且是电子表格思维。最好通过完美的查询来链接具有正确内容的表。
expanding the DB horizontally isn't very efficient with the potential of having very large tables and complex updates. so id, book, chapter, verse, V1, V2, V3, V4... Vn just seems to be looking at the problem like a spreadsheet rather than taking advantage of what a DB has to offer.
the references are static (book, chapter, verse) so they can be populated in one table with an id and with that you have the framework of the entire bible. the verse content can potentially have hundreds of versions so it would be better stored in its own table and linked with a foreign key to identifying the references. the structure would be primary_id, foreign_id, version, content.
now the content just fills in as needed and there is no need to have thousands of empty fields that in the future you have to go back and fill in or needing to expand the table and backfill all the existing data every time you add a new version. just fill in the verses as you get them which works better I think if you building it yourself.
This also makes sense as some versions only have the NT or some verses that they think were added later aren't available so there is no need to have empty fields you just have the data and it links to the verse reference. "version" can also be a foreign key to identify more information in the version like a publishing date or long/short name (ie. "NIV", "New International Version") This also works well when using more than one revision of a translation like the 1984 NIV vs 2011 NIV. Both can be identified as "NIV" but differ in content so the version_id can link another table with expanded information about the version it's using. Once that data is in and linked properly you can display it however you wish for example combining the publishing date/short version name making a name like "NIV1984" or have a separate column unique for a display name.
I'm not sure how red letter or footnotes could be displayed and I know sites like biblegateway have this as a toggle switch so it's nice to have the option to sort it like this. with red letters, this could be a special static identifier directly in the verse content that is parsed out later as a CSS identifier. It could be its own foreign table too but since it is so little a delimiter would be really easy. It really depends what you're using the data for and if you wanted to do queries for the red letters then it would be best as a foreign table (fast) rather then search the db for the delimiter (slow)
with footnotes, since it depends on unique content it would be best stored in its own table. how it is identified and placed in the content could use static reference points within the content like x number of characters in or x number of words in and then linked with the content using a foreign key again. So the footnote table could be something like primary_id, foreign_id, reference, footnote and an example of the data could be 2304, 452, 64, "some manuscripts don't include this". Primary key would be 2304, the foreign key that links to the content table is 452, the footnote is placed 64 characters in, and the footnote is "some manuscripts don't include this" As for the incrementing footnote like A, B, C or 1, 2, 3 all of this can be dynamically generated. If it's important to be a static letter/number then you might want to include it in the data but I would rather have good data that allows this automatically then list it as static data.
here's the hint, Don't add hundreds of columns... that would just a headache and it's spreadsheet thinking. it's better to work through the perfect queries to link tables with the right content.
您可能需要将 verse_number 列拆分为 verse_start 和 verse_end。例如，伊洛卡诺圣经中的《历代志上》6:78 与第 79 节合并。因此，数据库必须能够处理以《历代志上》6:78-79 作为参考的单节经文。贴出链接供参考。 https://ebible.org/study/?w1= bible&t1=local%3Ailoulb&v1=R16_78
正如所讨论的，经文编号（开始和结束）可能是特定于圣经版本的。这完全取决于您正在处理的圣经版本。
You may need to split the verse_number column into verse_start and verse_end. As example, the 1 Chronicles 6:78 in an Ilocano Bible is merged with verse 79. Therefore, the database must be able to handle a Single verse with 1 Chronicles 6:78-79 as reference. Posting the link for reference. https://ebible.org/study/?w1=bible&t1=local%3Ailoulb&v1=R16_78
As discussed, verse number (start and end) may be Bible version specific. It all depends on the Bible versions you are working on.