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:
- The per-user settings like passphrase, security question, etc.
- 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.