Factoid database design for Chrisbot

By Chris Angell aka Chris62vw

Below is a proposed design for an irc bot factoid database that supports fact aliasing and the locking of fact aliases and fact definitions. Every "factoid" is really an alias to a definition. A change to an alias' value actually changes the underlying definitions. An alias to another alias will be treated as an alias to a fact who has the same def_id as the target alias.

The database used is SQLite. SQLite is like perl in how it treats values as open containers, much like anything can be stored in a perl scalar. SQLite also ignores certain constraints, like primary and foreign keys. Visit the SQLite website for more information.

Table Defintions

Table "aliases"

Key:

• "alias_id" is an auto-incrementing integer that starts at 1
• "alias_name" is a simple container
• "def_id" is a foreign key belonging to the "definitions" table
• "alias_locked" is either 0, or 1, depending on if the alias is locked or not

Table "definitions"

Key:

• "def_id" is an auto-incrementing integer that starts at 1
• "def_value" is a simple container that contains fact values
• "def_locked" is either 0, or 1, depending on if the definition is locked or not

User Access Explanation

User levels will be used so alaises and definitions can be "locked" to prevent change. There will be five user levels:

• 0 - trouble user - stick troublemakers in this category
• 1 - average user - everyone is automatically in this category by default, unless user is specifically in another category
• 2 - admin - trusted users
• 3 - sysop - trusted users that have more powers than admins
• 4 - superuser - very trusted users
• 5 - owner - the bot owner

The user levels will also be used for other things. For example, if someone is abusing the bot, he could be given a level 0 access level. Then the bot would ignore the user. User levels for the factiod database is simply the first step.

Table "locked_aliases"

A "locked" alias can only be removed or changed depending on the user level of the user who is attempting to affect the change.

Key:

• "alias_id" is a foreing key from the aliases database
• "user_id" is a foreing key from the users database (not shown in this document)
• "locked_level" is either the access level of the user who locked the alias, or less depending on if the user who locked the alias specified a lower lock level

Table "locked_defs"

A "locked" definition can only be removed or changed depending on the user level of the user who is attempting to affect the change.

Key:

• "def_id" is a foreing key from the definitions database
• "user_id" is a foreing key from the users database (not shown in this document)
• "locked_level" is either the access level of the user who locked the alias, or less depending on if the user who locked the alias specified a lower lock level