1 CREATE TABLE library_groups (
2 id INT(11) NOT NULL auto_increment, -- unique id for each group
3 parent_id INT(11) NULL DEFAULT NULL, -- if this is a child group, the id of the parent group
4 branchcode VARCHAR(10) NULL DEFAULT NULL, -- The branchcode of a branch belonging to the parent group
5 title VARCHAR(100) NULL DEFAULT NULL, -- Short description of the goup
6 description TEXT NULL DEFAULT NULL, -- Longer explanation of the group, if necessary
7 created_on DATETIME NOT NULL, -- Date and time of creation
8 updated_on DATETIME NULL DEFAULT NULL, -- Date and time of last
10 FOREIGN KEY (parent_id) REFERENCES library_groups(id) ON UPDATE CASCADE ON DELETE CASCADE,
11 FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON UPDATE CASCADE ON DELETE CASCADE
12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;