1

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.

  • How are you representing the text and the hyperlinks in the program? Markup? – user2313838 Dec 16 '13 at 5:35
4

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.

  • 1
    I can't believe I never accepted this. Your answer here led me to investigate NoSQL for the first time. I was almost instantly drawn to neo4j and quickly became captivated with it and with graph theory in general. I've spent much of the past six months developing my competency and understanding in these areas, and these developments have not only added enormously powerful tools to my toolbelt, they have fundamentally altered the way I think about programming on some very basic levels. I'll forever be grateful. Thank you so much. – drewmore4 May 4 '14 at 6:48
1

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.

  • To make this explicit: In your suggested design, the paragraph table will also have an order column. So to reconstruct article № 17, one would do SELECT text FROM paragraphs WHERE article = 17 ORDER BY order. – amon Dec 12 '13 at 10:29
  • Yes, absolutely. ORDER BY is another thing that RDBMS are really good at. – Kilian Foth Dec 12 '13 at 10:37
  • Thanks for your suggestions I really appreciate them. That said, Jorg's suggestion got me investigating couchDB and Neo4j for the first times and, at this point, I'm inclined to think that either of them (Neo4j in particular) fits my use case better than MySQL. Would you disagree, and/or encourage me to look harder at MySQL? – drewmore4 Dec 12 '13 at 12:50
  • Sure, go ahead and see how it works out! Then when someone else asks about data models, you'll be able to share better advice than me. :) – Kilian Foth Dec 12 '13 at 13:27
0

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)
)
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.