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.