Calsoft

Home	(Create an Account) Username: Password:

Learning about relational database design

To construct this program, we had to learn the theory and operation of relational databases. We believe the following discussion will be helpful to other students interested in database-supported web applications.

Information is stored in a set of tables, but the way it is organized is very important. We learned that we could not simply put all the data in one table...if we did, we'd find it almost impossible to get the information out later. Our sources are listed in the References section of this site.

Table Types

The tables listed below show how we organized the database.

Tables constructed as objects which reflect the properties of the real-world primary objects they describe:

Events

Categories

Subcategories

People

Tables which represent additional details about a primary object:
Time
Tables which describe relations between primary objects:
Privileges

Table Details

All of the tables in Calsoft are composed of columns, the first of which is an identification value. Whenever a new entry is inserted into the table, its identification value is automatically created to be one greater than the previous entry, so no two entries ever have the same identification number, much like a Social Security Number.

Table Events

Fields eventID int(11) NOT NULL auto_increment primary key
eventTitle varchar(30) NOT NULL,
eventDetails text NOT NULL,
subCategoryID int(11) NOT NULL default '0',
dateModified timestamp(14) NOT NULL,
dateCreated timestamp(14) NOT NULL,
modifiedBy int(11) NOT NULL default '0',
createdBy int(11) NOT NULL default '0',
urgent tinyint(4) NOT NULL default '0',

Description This table stores one record for every event in the calendar. The fields in this table store an index value, a name for the event, a text description of the event details, the index value of the subcategory it is under, the date it was created, the last person to modify the event, the date it was last modified, the index value of the person who created it, and a Boolean value which is true if the event is "urgent."

Name Categories

Fields categoryID int(11) NOT NULL auto_increment primary key,
categoryName text NOT NULL,

Description Aside from the index/identification field, the categories table has only one additional field, which holds the name of the category. The only function of this table is to keep track of the categories.

Name SubCategories

Fields subCategoryID int(11) NOT NULL auto_increment primary key,
subCategoryName char(30) NOT NULL,
categoryID int(11) NOT NULL default '0',
dateModified timestamp(14) NOT NULL,
dateCreated timestamp(14) NOT NULL,
modifiedBy int(11) NOT NULL default '0',
createdBy int(11) NOT NULL default '0',

Description This table contains seven fields. One, of course, to store an index value, one to store the name of the subcategory, one to store which category that subcategory falls under, one to store the creator's index value from the people table, one to store the index value out of the people table of the last person to modify the subcategory, one to store the date the subcategory was created, and one to store the date it was last modified.

Name People

Fields personID int(11) NOT NULL auto_increment primary key,
firstName varchar(20) NOT NULL,
lastName varchar(20) NOT NULL,
email varchar(30) NOT NULL,
username varchar(16) NOT NULL,
password varchar(16) NOT NULL,
displayArray text NOT NULL,

Description This table is slightly complex; it is composed of eight fields: an identification/index field, firstName, lastName, email, username, password, displayArray. While most of these fields are self explanatory, displayArray is an array that stores each user's preferences. The array is composed of a list of categories that a user wishes to view on the calendar; any events that are in an unselected category will be hidden. Users with their own account will have their preferences saved in this table; the next time they log in the same preferences will be in effect. Users without an account (Public Users) will have their preferences retained until the browser is closed.

Name Time

Fields timeID int(11) NOT NULL auto_increment primary key,
eventID int(11) NOT NULL default '0',
eventDate date NOT NULL default '0000-00-00',
startTime varchar(10) NOT NULL,
endTime varchar(10) NOT NULL,

Description The events table does not store the date(s) or time(s) an event is scheduled for. This is where the time table comes into play. Since some events occur multiple times, each occurrence of any event is stored as one record in the 'time' table. Each instance stores an index value, the index from cal_events of the event it corresponds to, the date of this particular occurrence of the event, as well as the time the event starts and ends on that date. This is useful because it allows us, as programmers, to look at each occurrence of an event as a separate entity.

Name Privileges

Fields privID int(11) NOT NULL auto_increment primary key,
personID int(11) NOT NULL default '0',
subCategoryID int(11) NOT NULL default '0',
isSubCatAdmin tinyint(1) NOT NULL default '0',
isSuperAdmin tinyint(1) NOT NULL default '0',

Description The privileges table has five fields: privID, personID, subCategoryID, isSubCatAdmin, and isSuperAdmin. The field "privID" stores the index value. The field "personID" stores the index value from people of the person whom this privilege corresponds to. The fields "isSubCatAdmin" and "isSuperAdmin" store boolean values used to determine what exactly is specified by this privilege.

All of the tables in Calsoft are composed of columns, the first of which is an identification value. Whenever a new entry is inserted into the table, its identification value is automatically created to be one greater than the previous entry, so no two entries ever have the same identification number, much like a Social Security Number.

Table	Events
Fields	eventID int(11) NOT NULL auto_increment primary key eventTitle varchar(30) NOT NULL, eventDetails text NOT NULL, subCategoryID int(11) NOT NULL default '0', dateModified timestamp(14) NOT NULL, dateCreated timestamp(14) NOT NULL, modifiedBy int(11) NOT NULL default '0', createdBy int(11) NOT NULL default '0', urgent tinyint(4) NOT NULL default '0',
Description	This table stores one record for every event in the calendar. The fields in this table store an index value, a name for the event, a text description of the event details, the index value of the subcategory it is under, the date it was created, the last person to modify the event, the date it was last modified, the index value of the person who created it, and a Boolean value which is true if the event is "urgent."


Name	Categories
Fields	categoryID int(11) NOT NULL auto_increment primary key, categoryName text NOT NULL,
Description	Aside from the index/identification field, the categories table has only one additional field, which holds the name of the category. The only function of this table is to keep track of the categories.

Name	SubCategories
Fields	subCategoryID int(11) NOT NULL auto_increment primary key, subCategoryName char(30) NOT NULL, categoryID int(11) NOT NULL default '0', dateModified timestamp(14) NOT NULL, dateCreated timestamp(14) NOT NULL, modifiedBy int(11) NOT NULL default '0', createdBy int(11) NOT NULL default '0',
Description	This table contains seven fields. One, of course, to store an index value, one to store the name of the subcategory, one to store which category that subcategory falls under, one to store the creator's index value from the people table, one to store the index value out of the people table of the last person to modify the subcategory, one to store the date the subcategory was created, and one to store the date it was last modified.

Name	People
Fields	personID int(11) NOT NULL auto_increment primary key, firstName varchar(20) NOT NULL, lastName varchar(20) NOT NULL, email varchar(30) NOT NULL, username varchar(16) NOT NULL, password varchar(16) NOT NULL, displayArray text NOT NULL,
Description	This table is slightly complex; it is composed of eight fields: an identification/index field, firstName, lastName, email, username, password, displayArray. While most of these fields are self explanatory, displayArray is an array that stores each user's preferences. The array is composed of a list of categories that a user wishes to view on the calendar; any events that are in an unselected category will be hidden. Users with their own account will have their preferences saved in this table; the next time they log in the same preferences will be in effect. Users without an account (Public Users) will have their preferences retained until the browser is closed.

Name	Time
Fields	timeID int(11) NOT NULL auto_increment primary key, eventID int(11) NOT NULL default '0', eventDate date NOT NULL default '0000-00-00', startTime varchar(10) NOT NULL, endTime varchar(10) NOT NULL,
Description	The events table does not store the date(s) or time(s) an event is scheduled for. This is where the time table comes into play. Since some events occur multiple times, each occurrence of any event is stored as one record in the 'time' table. Each instance stores an index value, the index from cal_events of the event it corresponds to, the date of this particular occurrence of the event, as well as the time the event starts and ends on that date. This is useful because it allows us, as programmers, to look at each occurrence of an event as a separate entity.

Name	Privileges
Fields	privID int(11) NOT NULL auto_increment primary key, personID int(11) NOT NULL default '0', subCategoryID int(11) NOT NULL default '0', isSubCatAdmin tinyint(1) NOT NULL default '0', isSuperAdmin tinyint(1) NOT NULL default '0',
Description	The privileges table has five fields: privID, personID, subCategoryID, isSubCatAdmin, and isSuperAdmin. The field "privID" stores the index value. The field "personID" stores the index value from people of the person whom this privilege corresponds to. The fields "isSubCatAdmin" and "isSuperAdmin" store boolean values used to determine what exactly is specified by this privilege.