How to use Bit flags in mysql

Question

I am using binary bits flags in a mysql to determine the role of a person. The person can be either faculty, instructor or both. Each academy has one or more unique faculty person (one-to-many relation). Also each academy has an instructor person. An instructor person can belong to one or more academy. The table person stores the basic data(name, email, etc.) of that specific individual. In order to determine the role I have partially created a person_role table but not sure what fields or their type are needed for the use of bit flags?

CREATE TABLE IF NOT EXISTS `academy` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL,
  `academy_id` int(11) NOT NULL,
  `street_address` varchar(50) NOT NULL,
  `city` varchar(25) NOT NULL,
  `state` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `person` (
 id int(11) NOT NULL auto_increment,
 `academy_id` int(11) NOT NULL,
 `person_name` varchar(50) NOT NULL,
 `person_email` varchar(50) default NULL, 
 `person_phone` varchar(15) default NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT `academy_id` FOREIGN KEY (`academy_id`) REFERENCES `academy` (`academy_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS `person_role` (
  `contact` tinyint(1) NOT NULL,
  `instructor` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

score 1 · Accepted Answer · answered Nov 15 '13 at 06:39

You can add another bit field on the person_role table called faculty. That way you'll be able to satisfy:

The person can be either faculty, instructor or both.

Since there is an "one to one" relation between a person and its role, you could move the instructor and faculty bit fields on the person table and drop the person_role table.

How to use Bit flags in mysql

1 Answers1