How to setup a flagging system

Question

I was wondering if someone could point me to the right direction in setting up a flagging/notification system. I'd say very similar to a tagging system. If anyone knows of any tutorial or example setups, that would be great.

Purpose

Basically, the flags are to attach to data items including customers, properties, contractors, applications, etc with the role of notifying users of an important piece of info regarding that item. The flags should come from a predetermined list of items where users can add or remove.

Database

I was thinking about going with a 3 table setup.

• flags table: the list of available flags, along with settings determining what those flags are applicable to (customers, properties, etc).
• linking tables indicating a given item has a given flag attached.
• the appropriate entities tables

User Interface

I would like some of the functionalities be similar to that of stack overflow's such as the add and removing of tags. Maybe use jquery tag-it for this? Mainly, I would like the user to be able to select the flag from a drop down list and a flag icon display when one or more flags have been selected. Mousing over the flag should show a tooltip listing the flags attached to that item. Not sure if this setup of the one flag show all be good for querying purposes as opposed to displaying multiple flags. Sample visual: http://i232.photobucket.com/albums/ee2/nismomatic97/flag.png

Answer 1

Your main problem is that you have a flag record for any number of possible different entities, and not a single entity (like "customers.").

In the past, I have solved this problem by providing an additional Foreign Key field in each table ("FlagID") that is unique across the entire database. A GUID is suitable. Then you can simply JOIN the table that applies to your particular context to the FlagID in your Flags table, and you will get only the flags that apply to that particular entity.

For example, when you JOIN FlagID in the Flags table to the FlagID in your Customers table, you get only those flags that pertain to Customers.

Note that Stack Overflow doesn't have the "Multiple Entities" problem; all flags are cast to Posts.

Answer 2

Basically, you need a table with flags and a table with customers/users. Those table should look like this:

Flags table:

• a unique ID of a flag (the easiest way is to set an "ID" field to AUTO_INCREMENT).
• flag name
• flag description
• something more you want

Users table:

• a field to store flags (I suggest to store serialized array of flags)
• other data you need for other purposes

Other than storing a serialized array, you can make another table where you'd store users' unique ID and flag ID. One row per flag. However, I think the first method is better and more efficient.

Selecting and displaying flags:

• select a field that contains serialized array of flags
• unserialize the array and use JOIN to select name and description of flags.

---

Examples

Storing flags to users' profiles:
Let's assume you want to store flags with ID 1, 3, 5 and 7:

$flags = serialize(array(1, 3, 5, 7));
mysql_query("UPDATE `users` SET `flags` = '$flags'") or die(mysql_error());

If you want to edit flags, select the field, unserialize the array and edit it by using Array functions.

Selecting and displaying:
So, you have the array with values of 1, 3, 5 and 7.
Here's an example of creating a SELECT query:

$flags = array(1, 3, 5, 7); // selected from 'users' table and unserialized
$query = "SELECT u.name, f.name, f.description FROM users u INNER JOIN flags f ON ";
$conditions = null;
for($i = 0; $i < count($flags); $i++)
{
    $conditions .= "f.id = $flags[$i] OR ";
}
$conditions = substr($conditions , 0, -4); // removing the last " OR "

$query = $query . $conditions . " WHERE u.name = 'Nikola'";

This is the query we got from the code:

SELECT u.name, f.name, f.description 
    FROM users u 
    INNER JOIN flags f 
    ON f.id = 1 OR f.id = 3 OR f.id = 5 OR f.id = 7 
    WHERE u.name = 'Nikola'

It will return all flags along with their names, descriptions and user's name.

All you have to do is to now display the results.

---

EDIT 1: User Interface
I'm not sure if I understood what you exactly want, but if you want to allow multiple flags per user, I suggest you to make a checkbox for every flag or a select menu with multiple choices rather than drop-down. I think the drop-down is not a good idea, since you can select only one item.

EDIT 2:
You can use the jQuery Autocomplete which is very similar to Stack Overflow's tagging and probably the best choice.

Another idea is that you actually can use the drop-down menus and still allow multiple flags.
Here's the thing: when a user select a flag from drop-down, store a flag in PHP array via AJAX, and store the array in $_SESSION.

Example: send request to a PHP file via AJAX, in PHP file read your $_SESSION variable where flags should be stored. If it's empty (no flags yet) - add an array of 1 key. If it isn't empty, read its value (whole array), add new flag to the array and return it to the session variable.

session_start();

if(isset($_POST['id'])) $flagid = $_POST['id']; // previously sent request via AJAX that contains this ID.
if(empty($_SESSION['flags'])) $_SESSION['flags'] = array($flagid); // this is the first selected flag
else 
{ 
    // other tags was selected earlier
    $flags = $_SESSION['flags'];
    $flags[] = $flagid;
    $_SESSION['flags'] = $flags;
}
die("ok");

Now, back in your JS file, check for the returned result. If it's "ok", then proceed, and if it's different than "ok", display an error.
To prevent selecting the same tag multiple times, you can delete it from drop-down by using jQuery (see this question) when user selects it. On some other place on the page, you can display selected flags.

EDIT 3:
You also can use a <select> with "multiple" attribute.