As part of the Nightlife Project, you will have to store some data through binary representation in the payment_rates table. While doing the writeup on the MySQL schema, I decided that this section was too large to easily fit inside of that article and it should be its own article. The goal of this article is to explain how database schemas employ the principles of binary notation to store a sequence of on/off flags in a single field.

Imagine this scenario: You are the head of maintenance at an office and are trying to determine which lightbulbs get the most usage and should be replaced with high efficiency bulbs. In order to determine which gets the most usage, you walk through the building in the morning, at lunch and again at night and make a note of which bulbs are on and which are off. Once you’ve gathered your data, you store the information in a database and after 2 months, you will view the results and determine the top used bulbs and replace them with higher efficiency bulbs to save electricity.

After each walk through the building, you have a list of lights and a corresponding on/off value for each one. Your list may look something like this:

Reception	Break Room	Bathroom	Office 1	Office 2	Office 3	Office 4	Hallway	Copy Room
On	Off	Off	Off	On	On	Off	On	Off

The obvious way to store this data is to create a table called ‘lights’ and give it 10 fields – a timestamp (we need to know when this walk through occurred, after all) and a field for each of the 9 rooms. However, this obvious way has a few nasty shortcomings. What if you decide to add additional lights to be checked, such as desk lamps or the front walkway? Adding additional fields rarely ends well. This is also extremely inefficient, as you now have an 10 field table when a 2 field table would suffice.

Instead of storing it through this obvious, but inefficient method, instead you can convert that list of on/off switches into a binary string. Using the same data above, the binary string would look like this: 100011010. Each of the lights that are on is a 1 and each light that is off is a 0. Each character represents a room – the first position is reception, the second position is break room, etc. Converting this binary string to decimal, 100011010 becomes 282 (why?).

Reception	Break Room	Bathroom	Office 1	Office 2	Office 3	Office 4	Hallway	Copy Room
1	0	0	0	1	1	0	1	0

The key thing behind storing binary information in a database is this: no other combination of binary will equal that number. There is simply no other set of on/off values that will add up to 282. This lets us store an infinite amount of on/off values in a single integer slot, without ambiguity and always available for reading. As far as how you will read it, look into your language’s “bitwise and” and “bitwise or” operators. An example in PHP would be:

$user_permission = 6; // binary 0110

$view            = 1; // binary 0001
$edit            = 2; // binary 0010
$create          = 4; // binary 0100
$delete          = 8; // binary 1000

if($user_permission & $view)
    echo 'user can view';

if($user_permission & $edit)
    echo 'user can edit';

if($user_permission & $create)
    echo 'user can create';

if($user_permission & $delete)
    echo 'user can delete';

You can use a system like this for storing user permissions(which is how *nix permissions work), which days of the week an event occurs(the nightlife project will be doing this for storing payment rates), or any other piece of information that can be summed up in a series of on/off switches. Using binary storage is extremely efficient and flexible. Remember, however, that any additional fields to be added should be added to the left side of the string to preserve old data.

“A carelessly planned project takes three times longer to complete than expected; a carefully planned project takes only twice as long.”

The Nightlife Project is a novice PHP/MySQL tutorial. While you don’t have to know a whole lot to get started, you should understand the basics of PHP and MySQL. Knowledge of variables, functions and flow control is required. If the following block of code makes sense to you, you are ready to start the project:

<?php
repeat('foo', 15);
function repeat($string, $count) {
     for($i = 0; $i < $count; ++$i) {
          mysql_query("INSERT INTO `data` VALUES ('$string')");
     }
}
?>

Now, an explanation is due.

You work for Vision Nightlife, a nightlife promotion company working out of Las Vegas. Your company hires promoters to hand out nightclub flyers for the various clubs that have hired your company. These promoters give flyers to tourists for things like free entry to Domi Lounge, a free drink at Club Septuro, etc. Each flyer is stamped with a unique code identifying the promoter that drove the tourists to the club. The club will then pay your company, based on how many people your promoters drove to their club. The amount is based on how many people and what day of the week. For example, Hoodoo Lounge pays $1 per person on Friday or Saturday nights, if you bring between 1 and 10 people. If you bring between 11 and 20 people, it is $1.50 per person, and 21 or more people is $2.25 per person. On a Wednesday night, those amounts are cut in half. Your company wants you to write software to keep track of all of this information.

Your software needs to track clubs, promoters, payment rates, referral amounts and payout amounts.

This is just an introduction to the nightlife project. Part 2 will begin looking at the database structure and writing the ideal schema for each table.