Marco's Blog

All content strictly personal opinions.
en eo

PSC Database Structure

2007-03-31 1 min read Password Store Component marco

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.