Database Design for an Electronic Notebook

Question

I have a question about database design. I want to create an electronic notebook or "Everything Bucket" for my own education. I know there are great alternatives out there like EverNote or Circus Ponies Notebook, or the open source KeepNotes or Red Notebook. But, like I said, I want to create my own just for the learning experience.

I don't have much experience with database design however. My question is what should the design look like -- tables, indices, etc. The way I am thinking of it is that

• Every notebook has zero or more notes or pages.
• Every page has a unique title and content. In addition to the "real" content, there would be metadata like the creation date, last modified date.
• Every page belongs to one or more categories, even if the category is "none". Categories might have additional sub-categories. For example, under the category of "Computer Science" there might be sub-categories like "Programming Languages" and "Compiler Design".
• Every page has zero or more tags associated with it. These would be similar to categories but not quite the same. For example, a paper might belong to the "Compiler Design" category but might be tagged with the authors name(s), journal name, year of publication, and so on.
• The content of a page should include an HTML stream that can hold text, images, links, and so on. They could be pretty large chunks of data. Would it make sense to store certain parts, like images, separately from the text? (It would be great if I could display the contents of PDF docs too. But that isn't really required at this point.)
• It would also be nice if a page could contain cross references to other pages in the same database, like a wiki.

I would like to be able to search by category, tag, partial title or through the full text. I imagine the program could have a couple of different views of the database showing the categories, tags, and titles so it would be nice if those could be enumerated quickly.

So, given those desires, what would a good database look like? I'm assuming a SQL database would be used, but am not committed to it -- something like the Sleepycat Java Edition database would be ok too.

Thanks in advance for the suggestions!

Answer 1

Starting with a notebook:

CREATE TABLE `notebook` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `id` )
)

Add fields for any properties you'd like on the notebook. Then create similar tables for other things, like page, category, tag, etc making sure they all have a unique ID.

Now add relationships of those tables - one-to-one, one-to-many, many-to-many. A page will have a notebook_id field as it belongs to a notebook. Pages can belong to many categories, so make a link table with page_id and category_id.

Create the perfect data structure first, then think about querying it :) Hope that helps a bit.