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
SETdatatype 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.