PSC Database Structure

After thinking about the component, it's time to define how it is going to work within the Joomla framework.

We will need two database tables:

  1. The per-user settings like passphrase, security question, etc.
  2. The notes themselves, tied to users

The first table needs to have the user id as primary key. Other columns will be:

  • encrypted passphrase
  • encrypted temporary passphrase
  • security question
  • encrypted answer to security question

The second table will be more complex and have the following columns in addition to an integer ID:

  • note title
  • note text (safe)
  • note text (encrypted)
  • creation time
  • last modification time
  • last access time
  • folders (for later use and expansion) 

Encyption will occur two ways: for the user table, encryption will use a key stored as parameter of the component. The administration piece will have to ensure that on change of parameter all data is re-encrypted. The notes table will of course use the user's passphrase to encrypt his or her notes.

The folders colum is for a logical extension: the ability to file notes in folders. Unlike in the analogy with computer folders (or file folders, for that matters), a note can be in as many folders as desired – makes a lot more sense, since a note can be relevant to many aspects.