Introduction
With the database engine chosen, we can now start building some objects! It’s time to create the database structures. This provides a direction for the project’s data flow. Let’s keep this as simple as it can be. I do expect changes as the project goes on. We’ll follow these steps:
- Defined the data requirements
- Build out the entities and constraints in the conceptual design
- Identify the business level attributes and keys in the logical design.
- Fill out the implementation details in the physical design.
This is a pretty standard process with RDBMS design. There is no need to do anything exotic. Remember the goal is to get data in a format to be analyzed.
Data Requirements
Let’s start with reviewing the original requirements for data to be analyzed. This tends to be a fun step to do. Rather than say “data are data,” think about the components that make up the data itself. Ask yourself a series of questions:
- What parts make up the data?
- In this case, what is a Religious Text composed of?
- How are those parts organized?
- Do some parts reference other parts?
With that in mind, time to dive in! Here is my initial set of requirements:
- D1. A text can be a single page, book, or a library of books (ex: Bible).
- D2. A text has a root URL, title, and description.
- D3. A text might have organization into chapters and/or sections which groups pages.
- D4. A page is composed of paragraphs.
- D5. A page has an associated URL and title.
- D6. A page might have a header.
- D7. A page can have zero or more references.
- D8. A paragraph can be a single sentence.
- D9. A paragraph might be proceeded with an identifier.
- D10. A paragraph can have citations to one or more references.
- D11. A single reference might be a series of texts.
- D12. Abbreviations exist that are used by references.
That seems to cover the bases. Maybe there will be more to uncover as we dive deeper. No one gets a database perfect in one try. That’s why the alter and create commands exist!
Conceptual Design
A conceptual design has the purpose of taking the broad requirements and focusing them into individual entities. Those components will further be defined later. For now, identifying them and seeing how they relate to each other is the key goal.
The following are the identified entities:
| Entity Name | Description |
| Texts | A text can be a book, letter, doctrine, and any other form of written material. A text can also be part of a grouping of texts (example: The Bible is a collection of books). |
| Chapters | In the case of books, there are often chapters used to group together a series of pages under a common topic. |
| Pages | This is a single page of material in the religious text. |
| Paragraphs | Paragraphs are basic groupings of sentences and are stored in the page. |
| References | Citations to other texts. Often formatted as the text name along with the specific page or related chapter. |
| Abbreviations | Basic list of abbreviations used by references to other texts. |
Assumptions and Constraints
For each entity, we need to describe possible constraints that might be needed. If there are elements that are essential, we need to note them. We also want to put anything else that might be needed for relationships.
| Entity | Assumptions/Constraints |
| Texts | Must have at least one entry when a page is found. Text title, text type, group, description, and URL are required. |
| Chapters | These are optional (ex: simple letter on a teaching). When entry exists, must have a relationship to at least one page and one text. |
| Pages | Each page must be related to a text. A page must have a title and URL. Description is an optional attribute. |
| Paragraphs | A paragraph might have an identifier (typically, a sequence Id). It might also have one or more references. It must be related to a page. |
| References | References must be related to a paragraph. It must contain reference text to relate to external source material. |
| Abbreviations | Must have a full description along with the abbreviation. |
Reviewing the conceptual design, we have the following diagram. This shows the basic concept of how the entities work together to create a data blueprint.
Logical Design
The first step in logical design is to develop the schema for each of the entities. A schema merely organizes the attributes that are critical to describing the properties of the entity. This should not be too much of a challenge. Again, remember to keep this simple. We want to avoid a situation of over-engineering.
Schema Diagram
| Schema | Fields | ||||||
| Texts | Text_Id | Title | Description | URL | Group | Type | |
| Chapters | Chapter_Id | Title | Description | URL | Text_Id | ||
| Pages | Page_Id | Page_Sequence_Id | Title | Description | URL | Text_Id | Chapter_Id |
| Paragraphs | Paragraph_Id | Paragraph_Sequence_Id | Paragraph_Text | URL | Reference_Ids | Text_Id | Page_Id |
| References | Reference_ID | Reference_Sequence_Id | Reference_Text | URL | Page_Id | Text_Id | |
| Abbreviations | Abbreviation_Id | Abbreviated_Name | Full_Name | Category | Text_Id | ||
Entity Definition
Now it’s time to get some details into the attributes. We also want to get the keys defined. For this project, I see the ability to pull together lineage at the text and page level as being essential. When you look at the entities, you will see the Text_Id and Page_Id used to accomplish that goal. For example, if we run a query on paragraphs, we can go back to the text without touching other entities.
Here are the entity definitions with their attributes. I will define the potential keys along with the dependencies. If you are not familiar with this process, it is a method for determining unique keys used in the database definition.
Entity: Texts
Contains the highest level information about the Religious Text.
| Attribute Name | Description |
| Text_Id | System generated identifier. |
| Title | Title of the religious text. |
| Description | Summary of the religious text. |
| URL | URL pointing to the root of the text source. |
| Group | General group used to describe if the text is part of a library of books (ex: Bible). |
| Type | Relays if the type is a book, library, doctrine, letter, etc. |
Keys:
- Text_Id
Functional Dependencies:
- Text_Id > Title
- Text_Id > Description
- Text_Id > URL
- Text_Id > Group
- Text_Id > Type
Attribute Closures:
- (Text_Id)+ = (Title, Description, URL, Group, Type)
Unique Keys:
- Text_Id
Entity: Chapters
This entity relays the grouping of pages within a Religious Text. It is not required to have a set of chapters for a given text.
| Attribute Name | Description |
| Chapter_Id | System generated identifier. |
| URL | URL (if available) pointing to the chapter listings. |
| Title | Chapter title. |
| Description | Description of what is contained in the chapter. |
| Text_Id | Religious text identifier that this chapter is contained within. |
Keys:
- Chapter_Id
Foreign Keys:
- Text_Id
Functional Dependencies:
- Chapter_Id > URL
- Chapter_Id > Title
- Chapter_Id > Description
- Chapter_Id > Text_Id
Attribute Closures:
- (Chapter_Id)+ = (URL, Title, Description, Text_Id)
Unique Keys:
- Chapter_Id
Entity: Pages
All pages related to a text are held in this entity. This can be representative of a website page as well as a page in a book. There are no limits to the size of the page.
| Attribute Name | Description |
| Page_Id | System generated identifier. |
| Page_Sequence_Id | Simple identifier for page within text. |
| Title | Title of the page. |
| Description | Page summary. |
| URL | URL pointing to the page source. |
| Text_Id | Religious text identifier that this page is held. |
| Chapter_Id | If part of a chapter, this field has the identifier value to foreign record. |
Keys:
- Page_Id
Foreign Keys:
- Text_Id
- Chapter_Id
Functional Dependencies:
- Page_Id > Title
- Page_Id > Description
- Page_Id > URL
- Page_Id > Text_Id
- Page_Id > Chapter_Id
Attribute Closures:
- (Page_Id)+ = (Title, Description, URL, Text_Id, Chapter_Id)
Unique Keys:
- Page_Id
Entity: Paragraphs
Within a page, there are paragraphs. This entity contains these paragraphs.
| Attribute Name | Description |
| Paragraph_Id | System generated identifier. |
| Paragraph_Sequence_Id | If available, the paragraph’s sequence is taken from the source. If not, the value will be blank/null. |
| Paragraph_Text | The actual text of the paragraph. |
| URL | URL pointing the paragraph (if available). |
| Reference_Ids | Matched references in the paragraph text. |
| Text_Id | Religious text identifier that this paragraph is held. |
| Page_Id | Page that this paragraph is held. |
Keys:
- Paragraph_Id
- Paragraph_Sequence_Id
Foreign Keys:
- Reference_Ids
- Text_Id
- Page_Id
Functional Dependencies:
- Paragraph_Id > Paragraph_Sequence_Id
- Paragraph_Id > Paragraph_Text
- Paragraph_Id > URL
- Paragraph_Id > Reference_Ids
- Paragraph_Id > Text_Id
- Paragraph_Id > Page_Id
Attribute Closures:
- (Paragraph_Id)+ = (Paragraph_Sequence_Id, Paragraph_Text, URL, Reference_Ids, Text_Id, Page_Id)
Unique Keys:
- Paragraph_Id
Entity: References
This contains the external references cited throughout the texts.
| Attribute Name | Description |
| Reference_Id | System generated identifier. |
| Reference_Sequence_Id | Sequence that the citation is called. |
| Reference_Text | The list of references. |
| URL | Reference URL. |
| Page_Id | Page reference is cited. |
| Text_Id | Text reference is cited. |
Keys:
- Reference_Id
Foreign Keys:
- Page_Id
- Text_Id
Functional Dependencies:
- Reference_Id > Reference_Sequence_Id
- Reference_Id > Reference_Text
- Reference_Id > URL
- Reference_Id > Page_Id
- Reference_Id > Text_Id
Attribute Closures:
- (Reference_Id)+ = (Reference_Sequence_Id, Reference_Text, URL, Page_Id, Text_Id)
Unique Keys:
- Reference_Id
Entity: Abbreviations
| Attribute Name | Description |
| Abbreviation_Id | System generated identifier. |
| Abbreviated_Name | Abbreviated name text. |
| Full_Name | Full version name of text. |
| Category | Category, if any, that the abbreviation belongs. |
| Text_Id | Matching text identifier for abbreviated name. |
Keys:
- Abbreviation_Id
Foreign Keys:
- Text_Id
Functional Dependencies:
- Abbreviation_Id > Abbreviated_Name
- Abbreviation_Id > Full_Name
- Abbreviation_Id > Category
- Abbreviation_Id > Text_Id
Attribute Closures:
- (Abbreviation_Id)+ = (Abbreviated_Name, Full_Name, Category, Text_Id)
Unique Keys:
- Abbreviation_Id
Physical Design
The Physical Design Phase now brings in how the tables will be built in the server (again, PostgreSQL). The data types will be added on as well as whether NULLs are to be allowed in the column. The primary and foreign keys are noted. Also, please note that I will add audit columns to each table. These are used as housekeeping data points in case there is some traceability needed for support issues.
*Notes:
- I will use a postfix of “T” to designate “Table.”
- URL is set to 2048 after doing a web search on large URL string lengths.
- Primary Key Ids are set to auto-incrementing SERIAL type.
- Foreign Key Ids will be INTEGER type.
- Titles will be set to 1000 varying characters.
- Description and text needs to handle large character streams (TEXT data type).
- Basic strings will be set to 50 varying characters.
- User Ids will be set to 100 varying characters.
- Dates are to include timestamp without time zone.
- The JSON data type will be used when fields contain variable, delimited values.
Table: Texts_T
| Table | Texts_T | |||
| Description | Contains the highest level information about the Religious Text. | |||
| Attribute | Description | Type | Allow NULL? | Example of Values |
| Text_Id | System generated Id | SERIAL | No | 1 |
| Title | Title of Religious Text | VARCHAR(1000) | No | Catechism of the Catholic Church |
| Description | Description of Text | TEXT | Yes | Summary of Catholic Faith |
| URL | URL pointing to the root of the text source | VARCHAR(2048) | Yes | https://www.usccb.org/beliefs-and-teachings/what-we-believe/catechism/catechism-of-the-catholic-church |
| Group | General group used to describe if the text is part of a library of books (ex: Bible) | VARCHAR(50) | Yes | Summary |
| Type | Relays if the type is a book, library, doctrine, letter, etc | VARCHAR(50) | No | Book |
| Audit_Create_Date | Audit field for creation date and time | TIMESTAMP | No | 05/15/2024 |
| Audit_Create_User | Audit field for created by user | VARCHAR(100) | No | andy |
| Audit_Update_Date | Audit field for record update date and time | TIMESTAMP | Yes | NULL |
| Audit_Update_User | Audit field for updated by user | VARCHAR(100) | Yes | NULL |
Primary Keys:
- Text_Id
Foreign Keys:
- None
Table: Chapters_T
| Table | Chapters_T | |||
| Description | This relays the grouping of pages within a Religious Text. It is not required to have a set of chapters for a given text. | |||
| Attribute | Description | Type | Allow NULLs? | Example of Values |
| Chapter_Id | System generated identifier | SERIAL | No | 1 |
| URL | URL (if available) pointing to the chapter listings | VARCHAR(2048) | Yes | NULL |
| Title | Chapter title | VARCHAR(1000) | No | PART ONE: THE PROFESSION OF FAITH |
| Description | Description of what is contained in the chapter | TEXT | Yes | NULL |
| Text_Id | Religious text identifier that this chapter is contained within | INTEGER | No | 1 |
| Audit_Create_Date | Audit field for creation date and time | TIMESTAMP | No | 05/15/2024 |
| Audit_Create_User | Audit field for created by user | VARCHAR(100) | No | andy |
| Audit_Update_Date | Audit field for record update date and time | TIMESTAMP | Yes | NULL |
| Audit_Update_User | Audit field for updated by user | VARCHAR(100) | Yes | NULL |
Primary Keys:
- Chapter_Id
Foreign Keys:
- Text_Id to Texts_T.Text_Id
Table: Pages_T
| Table | Pages_T | |||
| Description | All pages related to a Religious Text. | |||
| Attribute | Description | Type | Allow NULLs? | Example of Values |
| Page_Id | System generated identifier | SERIAL | No | 1 |
| Page_Sequence_Id | Simple identifier for page within text. | INTEGER | No | 1 |
| Title | Title of the page | VARCHAR(1000) | No | THE FINAL DOXOLOGY |
| Description | Page summary | TEXT | Yes | NULL |
| URL | URL pointing to the page source | VARCHAR(2048) | Yes | https://www.vatican.va/archive/ENG0015/__PAD.HTM |
| Text_Id | Religious text identifier that this page is held | INTEGER | No | 1 |
| Chapter_Id | If part of a chapter, this field has the identifier value to foreign record | INTEGER | Yes | 1 |
| Audit_Create_Date | Audit field for creation date and time | TIMESTAMP | No | 05/15/2024 |
| Audit_Create_User | Audit field for created by user | VARCHAR(100) | No | andy |
| Audit_Update_Date | Audit field for record update date and time | TIMESTAMP | Yes | NULL |
| Audit_Update_User | Audit field for updated by user | VARCHAR(100) | Yes | NULL |
Primary Keys:
- Page_Id
Foreign Keys:
- Text_Id to Texts_T.Text_Id
- Chapter_Id to Chapters_T.Chapter_Id
Table: Paragraphs_T
| Table | Paragraphs_T | |||
| Description | Holds the actual text data at the lowest level (paragraphs). | |||
| Attribute | Description | Type | Allow NULLs? | Example of Values |
| Paragraph_Id | System generated identifier | SERIAL | No | 1 |
| Paragraph_Sequence_Id | If available, the paragraph’s sequence is taken from the source. If not, the value will be blank/null | VARCHAR(1000) | Yes | 2856 |
| Paragraph_Text | The actual text of the paragraph | TEXT | No | Then, after the prayer is over you say ‘Amen,’ which means ‘So be it,’ thus ratifying with our ‘Amen’ what is contained in the prayer that God has taught us.”179 |
| URL | URL pointing the paragraph (if available) | VARCHAR(2048) | Yes | NULL |
| Reference_Sequence_Ids | Matched references in the paragraph text | JSON | Yes | {Reference:“179”} |
| Text_Id | Religious text identifier that this paragraph is held | INTEGER | No | 1 |
| Page_Id | Page that this paragraph is held | INTEGER | No | 1 |
| Audit_Create_Date | Audit field for creation date and time | TIMESTAMP | No | 05/15/2024 |
| Audit_Create_User | Audit field for created by user | VARCHAR(100) | No | andy |
| Audit_Update_Date | Audit field for record update date and time | TIMESTAMP | Yes | NULL |
| Audit_Update_User | Audit field for updated by user | VARCHAR(100) | Yes | NULL |
Primary Keys:
- Paragraph_Id
Foreign Keys:
- Text_Id to Texts_T.Text_Id
- Page_Id to Pages_T.Page_Id
Table: References_T
| Table | References_T | |||
| Description | This contains the external references cited throughout the texts. | |||
| Attribute | Description | Type | Allow NULLs? | Example of Values |
| Reference_Id | System generated identifier | SERIAL | No | 1 |
| Reference_Sequence_Id | Sequence that the citation is called | VARCHAR(1000) | No | 179 |
| Reference_Text | The list of references | JSON | No | {reference:”St. Cyril of Jerusalem, Catech. myst. 5,18: PG 33, 1124,”reference:”cf. Cf. Lk 1:38.”} |
| URL | Reference URL | VARCHAR(2048) | Yes | NULL |
| Page_Id | Page reference is cited | INTEGER | No | 1 |
| Text_Id | Text reference is cited | INTEGER | No | 1 |
| Audit_Create_Date | Audit field for creation date and time | TIMESTAMP | No | 05/15/2024 |
| Audit_Create_User | Audit field for created by user | VARCHAR(100) | No | andy |
| Audit_Update_Date | Audit field for record update date and time | TIMESTAMP | Yes | NULL |
| Audit_Update_User | Audit field for updated by user | VARCHAR(100) | Yes | NULL |
Primary Keys:
- Reference_Id
Foreign Keys:
- Text_Id to Texts_T.Text_Id
- Page_Id to Pages_T.Page_Id
Table: Abbreviations_T
| Table | Abbreviations_T | |||
| Description | Basic list of abbreviations used by references to other texts. | |||
| Attribute | Description | Type | Allow NULLs? | Example of Values |
| Abbreviation_Id | System generated identifier | SERIAL | No | 1 |
| Abbreviated_Name | Abbreviated name text | VARCHAR(100) | No | Acts |
| Full_Name | Full version name of text | VARCHAR(1000) | No | Acts of the Apostles |
| Category | Category, if any, that the abbreviation belongs to | VARCHAR(100) | Yes | Bible |
| Text_Id | Matching text identifier for abbreviated name | INTEGER | Yes | 2 |
| Audit_Create_Date | Audit field for creation date and time | TIMESTAMP | No | 05/15/2024 |
| Audit_Create_User | Audit field for created by user | VARCHAR(100) | No | andy |
| Audit_Update_Date | Audit field for record update date and time | TIMESTAMP | Yes | NULL |
| Audit_Update_User | Audit field for updated by user | VARCHAR(100) | Yes | NULL |
Primary Keys:
- Abbreviation_Id
Foreign Keys:
- Text_Id to Texts_T.Text_Id
Building Staging Tables
Staging tables are used for a method of temporary storage when loading from external files. They are typically loose in their constraints. This allows flexibility when organizing data. The end goal will be to maintain data integrity. Design of these will be done in future articles to accommodate the individual sources.
Entity Relationship Diagram
Finally, we have a full ER Diagram to represent the entire structure:
Next Steps
This finishes up the basic database design. For those new to this process, you should not be afraid of the work involved. In fact, a database architect might take further steps involving more detailed data normalization techniques. Perhaps this will be revisited later on if the structure grows.
In the next step, we will start loading data! I believe in continuing with a simple approach will be important. I am considering using a 3rd party tool to assist with this but that might not be necessary. I will do some research and experimentation then publish my results.
Thank you
Appendix
SQL to Generate Database Tables
*Note: I used the pgAdmin Tool included with PostgreSQL to pull this together. The schema is set to “public” but I’ll set to a different one later.
BEGIN;
CREATE TABLE IF NOT EXISTS public."Texts_T"
(
"Text_Id" serial NOT NULL,
"Title" character varying(1000)[] NOT NULL,
"Description" text,
"URL" character varying(2048)[],
"Group" character varying(50)[],
"Type" character varying(50)[] NOT NULL,
"Audit_Create_Date" timestamp without time zone NOT NULL,
"Audit_Create_User" character varying(100)[] NOT NULL,
"Audit_Update_Date" timestamp without time zone,
"Audit_Update_User" character varying(100)[],
CONSTRAINT pk_texts_t PRIMARY KEY ("Text_Id")
);
CREATE TABLE IF NOT EXISTS public."Chapters_T"
(
"Chapter_Id" serial NOT NULL,
"URL" character varying(2048)[],
"Title" character varying(1000)[] NOT NULL,
"Description" text,
"Text_Id" integer NOT NULL,
"Audit_Create_Date" timestamp without time zone NOT NULL,
"Audit_Create_User" character varying(100)[] NOT NULL,
"Audit_Update_Date" timestamp without time zone,
"Audit_Update_User" character varying(100)[],
CONSTRAINT pk_chapters_t PRIMARY KEY ("Chapter_Id")
);
CREATE TABLE IF NOT EXISTS public."Pages_T"
(
"Page_Id" serial NOT NULL,
"Page_Sequence_Id" integer NOT NULL,
"Title" character varying(1000)[] NOT NULL,
"Description" text,
"URL" character varying(2048)[],
"Text_Id" integer NOT NULL,
"Chapter_Id" integer,
"Audit_Create_Date" timestamp without time zone NOT NULL,
"Audit_Create_User" character varying(100) NOT NULL,
"Audit_Update_Date" timestamp without time zone,
"Audit_Update_User" character varying(100)[],
CONSTRAINT pk_pages_t PRIMARY KEY ("Page_Id")
);
CREATE TABLE IF NOT EXISTS public."Paragraphs_T"
(
"Paragraph_Id" serial NOT NULL,
"Paragraph_Sequence_Id" character varying(1000)[],
"Paragraph_Text" text NOT NULL,
"URL" character varying(2048)[],
"Reference_Sequence_Ids" json,
"Text_Id" integer NOT NULL,
"Page_Id" integer NOT NULL,
"Audit_Create_Date" timestamp without time zone NOT NULL,
"Audit_Create_User" character varying(100)[] NOT NULL,
"Audit_Update_Date" timestamp without time zone,
"Audit_Update_User" character varying(100)[],
CONSTRAINT fk_paragraphs_t PRIMARY KEY ("Paragraph_Id")
);
CREATE TABLE IF NOT EXISTS public."References_T"
(
"Reference_Id" serial NOT NULL,
"Reference_Sequence_Id" character varying(1000)[] NOT NULL,
"Reference_Text" json NOT NULL,
"URL" character varying(2048)[],
"Page_Id" integer NOT NULL,
"Text_Id" integer NOT NULL,
"Audit_Create_Date" timestamp without time zone NOT NULL,
"Audit_Create_User" character varying(100) NOT NULL,
"Audit_Update_Date" timestamp without time zone,
"Audit_Update_User" character varying(100)[],
CONSTRAINT pk_references_t PRIMARY KEY ("Reference_Id")
);
CREATE TABLE IF NOT EXISTS public."Abbreviations_T"
(
"Abbreviation_Id" serial NOT NULL,
"Abbreviated_Name" character varying(100)[] NOT NULL,
"Full_Name" character varying(1000)[] NOT NULL,
"Category" character varying(100)[],
"Text_Id" integer,
"Audit_Create_Date" time without time zone NOT NULL,
"Audit_Create_User" character varying(100)[] NOT NULL,
"Audit_Update_Date" timestamp with time zone,
"Audit_Update_User" character varying(100)[],
CONSTRAINT pk_abbreviations_t PRIMARY KEY ("Abbreviation_Id")
);
ALTER TABLE IF EXISTS public."Chapters_T"
ADD CONSTRAINT fk_chapters_t_to_text_t_01 FOREIGN KEY ("Text_Id")
REFERENCES public."Texts_T" ("Text_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS public."Pages_T"
ADD CONSTRAINT fk_pages_t_to_texts_t_01 FOREIGN KEY ("Text_Id")
REFERENCES public."Texts_T" ("Text_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS public."Pages_T"
ADD CONSTRAINT fk_pages_t_to_chapters_t_01 FOREIGN KEY ("Chapter_Id")
REFERENCES public."Chapters_T" ("Chapter_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS public."Paragraphs_T"
ADD CONSTRAINT fk_paragraphs_t_to_texts_t_01 FOREIGN KEY ("Text_Id")
REFERENCES public."Texts_T" ("Text_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS public."Paragraphs_T"
ADD CONSTRAINT fk_paragraphs_t_to_pages_t_01 FOREIGN KEY ("Page_Id")
REFERENCES public."Pages_T" ("Page_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS public."References_T"
ADD CONSTRAINT fk_references_t_to_pages_t_01 FOREIGN KEY ("Page_Id")
REFERENCES public."Pages_T" ("Page_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS public."References_T"
ADD CONSTRAINT fk_references_t_to_texts_t_01 FOREIGN KEY ("Text_Id")
REFERENCES public."Texts_T" ("Text_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
ALTER TABLE IF EXISTS public."Abbreviations_T"
ADD CONSTRAINT fk_abbreviations_t_to_texts_t_01 FOREIGN KEY ("Text_Id")
REFERENCES public."Texts_T" ("Text_Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID;
END;
Andy’s Neural Works 
One response to “Designing the Database Structures for The All Roads Project”
[…] Up until now, the work for The All Roads Project has been more high level. The final table structures are defined in the previous article. That step does gets more detailed but staging the data will bring us even closer to the atomic parts. We will have to review and deeply understand the source files. There are many files to go through but I think we can find some sort of pattern. […]
LikeLike