How to store collections of linked Text objects in database

Question

I'm writing a Java program that works as a presentation of a written document, which needs to be stored in a database (I'm using MySQL, but am open to suggestions of other DB types).

The Essay class is a collection of objects of the Paragraph class (more specifically Essay extends LinkedList<Paragraph>, which consists primarily of an int ID and a String content. The Essay object is stored as its own table in the DB, which is fairly straightforward (with columns int paragraphId and Blob text), and I do a simple query that creates a new Paragraph object for each row in the table when the program gets initialized.

Within each paragraph, certain hyperlinked words link to other Essay objects, which also need to be stored in the database. My question is how best to save them - the options I'm considering are:

• Creating a single additional DB table where each essay is stored on a row, with paragraphs being stored in a single text object separated by a delimiter, and the program separates them into separate Paragraph object as it pulls them from the DB. (this is fairly manageable because these linked essays are much shorter than the main essay).
• Create a single additional table, with a large number of content columns, storing each paragraph in a separate column. Then, when the data is pulled from the database, it creates a new essay for each row and a new paragraph for each column in that row, until it comes upon one that is null.
• Dynamically create a separate DB table for each linked essay, setting it up identically to the primary one. This doesn't seem ideal because it would end up being a very large number of tables, and many of them would only have two or three paragraphs, making a new table seem wasteful.
• Do a different arrangement altogether: Have one Paragraphs table, where all paragraphs from all essays are stored, and another table called Essays, which uses a SET datatype to store the IDs (foreign keys) of the paragraphs included in each essay.

I've modified my actual use case here to make it more simple to explain. I'm new to database programming, and am doing this largely as an exercise, so I'm looking for general tips about the pros/cons of these approaches, as well as any additional suggestions.

Answer 1

What you describe doesn't sound like a good fit for a SQL database.

Basically, what you have is a graph of documents, I would store those in either a document database (e.g. Cassandra, CouchDB, MongoDB, Redis) or a graph database (e.g. Neo4J), depending on how you want to traverse / query the data. (Or maybe even both: store the documents in a document database and the graph structure in a graph database, that way you can traverse the graph in the graph database, which is what they are good at, and then retrieve the document from the document database, which is what those are good at.)

There are even databases such as ArangoDB which combine a graph-oriented, document-oriented and key-value-based storage model in a single database.

This idea of choosing a database model that fits the shape of your data instead of trying to awkwardly fit the shape of your data to match the (SQL) database model has been gathering a lot of attention over the past 10 years or so, and is known as NoSQL (Not Only SQL). Note that NoSQL doesn't mean you shouldn't use SQL. It means that you should use SQL when it is the right tool for the job, i.e. when your data is actually table-shaped and relational.

Answer 2

There are problems with each of the first three options.

Storing the entire text in one column can work, but may become inefficient depending on how large and how numerous your paragraphs are, whether you always want to retrieve all paragraphs of an essay or maybe only some of them, etc. A separate table for paragraphs linked to essays via foreign keys is often the better way to go.

Stuffing successive paragraphs into neighbouring columns of one table is a bad idea. You have to predict how many paragraphs there can be, then provision that much storage for every essay, much of which will be wasted, only to find that you misestimated and either have to tweak the schema in production, or introduce unwanted limits that look totally arbitrary to the users (because they are). Also, you'll have to generate at least part of your queries dynamically depending on just how many of those columns there are, which opens up several cans of worms better left closed.

Creating a new table for each linked essay is much worse still. Any solution that requires you to change not only your table definitions but the set of tables that exist in the schema just because a user entered new data is at best a profound misunderstanding of what relational data bases are for. It can work with a schema-free platform (usually called 'NoSQL', but the decisive difference is the lack of a relational schema, not the query syntax), but I'm not experienced enough here to advise you on how and when.

Option 4 is what I would do, but instead of maintaining a set of foreign keys, which introduces all of the problems of multiple columns in miniature, I would go with paragraphs pointing back to essays via a foreign key. If your database is too inefficient to perform the JOIN needed to reconstruct an entire essay, then something else is probably wrong besides the organisation of your data - databases have been optimized for exactly this task for decades, so it would be very surprising if this were really the limiting factor.

Answer 3

There should be a table for each type of entity: Essay and Paragraph. I'm assuming the essays have some external identifier that the hyperlinks are referencing which I'm just going to call essay_name for this example. It sounds like you also need an attribute to indicate which is the "primary" essay. In pseudo-DDL:

CREATE TABLE Essay (
    essay_id INT NOT NULL, 
    essay_name TEXT -- ...,
    is_primary BOOLEAN NOT NULL,
    PRIMARY KEY (essay_id)
)

A Paragraph has a many-to-one relationship with an Essay (via the essay_id column). It also needs to have some way to represent the ordering of paragraphs within an essay. The following example uses position to capture the ordering of paragraphs.

CREATE TABLE Paragraph (
    paragraph_id INT NOT NULL,
    essay_id INT NOT NULL,
    position INT NOT NULL,
    content TEXT,
    PRIMARY KEY (paragraph_id),
    FOREIGN KEY (essay_id) REFERENCES Essay (essay_id)
)

Answer 4

In Semantic Processing, automatically generated annotations are created that relate to a particular block of text. Annotations would flavour the context of individual words or phrases within the block of text. The phrases were tagged with ideas like 'city' or 'government department', or 'medical field'. While this is not exactly what you're doing, the idea is the same - how do you relate additional information associated with a block of text.

The blocks of text were stored in a single table with an autogenerated id for each row. The annotations were stored in a separate table that referenced the id. The fields it had were: major_type, minor_type, start, end, and value. Start and end were numbers that indicated character numbers within the block of text. "The desktop was large" would annotate desktop as a 'physical object', 'furniture', 4, 10, 'desktop'. It used the character values to reference the annotated details. While it might not be ideal, it's one way a real system did something similar.

If I were you, I'd strongly consider a markup language to modify the text to include the links. The character reference seems easy to break. It really depends on your data - how often it is updated, or how much control you have over it.

Hope this helped!