Relationship Models and Schema

When working with relational databases, instead of thinking only about what information your database describes, you should think of the relationships between pieces of information.

When you create a database, you should start by creating a conceptual model, or schema. This schema defines the overall structure of your database in terms of associations between pieces of information.

There are three basic types of relationships in databases: one-to-one, one-to-many (or many-to-one), and many-to-many. In order to use relational databases effectively, you need to think of the information in terms of those types of relationships.

A good way to show the three different types of relationships is to model a student’s class schedule.

Here are examples of each type of relationship in the context of a student class schedule:

• one-to-one relationship—A student has only one student ID number and a student ID number is associated with only one student.

• one-to-many relationship—A teacher teaches many classes, but generally speaking, a class has only one teacher of record.

• many-to-many relationship—A student can take more than one class. With few exceptions, each class generally contains more than one student.

Because of the differences in these relationships, the database structures that represent them must also be different to maximize efficiency.

• one-to-one—The student ID number should be part of the same table as other information about the student. You should generally break one-to-one information out into a separate table only if one or more of the following is true:

  • You have a large blob of data that is infrequently accessed (for example, a student ID photo) and would otherwise slow down every access to the table as a whole.

  • You have differing security requirements for the information. For example, social security numbers, credit card information, and so on must by stored in a separate database.

  • You have significantly separate sets of information that are used under different conditions. For example, student grade records might take advantage of a table that contains basic information about the student, but would probably not benefit from additional information about the student’s housing or financial aid.

• one-to-many—You should really think of this relationship as “many-to-one.” Instead of thinking about a teacher having multiple classes, think about each class having a single teacher. This may seem counterintuitive at first, but it makes sense once you see an example such as the one shown in Table 3-3.

  Table 3-3  The “classes” table

  ID	Teacher_ID	Class_Number	Class_Name
  1	1	MUS111	Music Appreciation: Classical Music
  2	1	MUS112	Music Appreciation: Music of the World

  Notice that each class is associated with a teacher ID. This should contain an ID from the teacher table (not shown). Thus, by creating a relationship from each of the many classes to a single teacher, you have changed the relationship from an unmanageable one-to-many relationship into a very simple many-to-one relationship.

• many-to-many—Many-to-many relationships are essentially a convenient fiction. Your first instinct would be to somehow make each class contain a list of student IDs that were associated with this class. This, however, is not a workable approach because relational databases (or at least those based on SQL) do not support any notion of a list.

  Indeed, someone thinking about this from a flat database perspective might think of a class schedule as a table containing a student’s name and a list of classes. In a relational database world, however, you would view it as a collection of students, a collection of classes, and a collection of lists that associate each person with a particular class or classes.

  Thus, you should think of a many-to-many relationship as multiple collections of many-to-one relationships. In the case of students and classes, instead of having a class associated with multiple students or a student associated with multiple classes, you instead have a third entity—a “student class” entity. This naming tells you that the entity expresses a relationship between a student and a class. An example of a student_class table is shown in Table 3-4.

  Table 3-4  The “student_class” table

  ID	Student_ID	Class_ID
  1	19	37

  Instead of associating either the student or the class with multiple entries, you now simply have multiple student_class entries. Each entry associates one student with one class. Thus, you effectively have multiple students, each associated with multiple classes in a many-to-many relationship, but you did it by creating multiple instances of many-to-one relationship pairs.

SQL Basics

The Structured Query Language, or SQL, is a standard syntax for querying or modifying the contents of a database. Using SQL, you can write software that interacts with a database without worrying about the underlying database architecture; the same basic queries can be executed on every database from SQLite to Oracle, provided that you limit yourself to the basic core queries and data types.

The JavaScript database uses SQLite internally to store information. SQLite is a lightweight database architecture that stores each database in a separate file. For more information about SQLite, see the SQLite website at http://www.sqlite.org/.

For syntax descriptions in this section:

• Text enclosed in square brackets ([])is optional.

• Lowercase text represents information that you choose.

• An ellipsis (...) means that you can specify additional parameters similar to the preceding parameter.

• Uppercase text and all other symbols are literal text and keywords that you must enter exactly as-is. (These keywords are not case sensitive, however.)

The most common SQL queries are CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and DROP TABLE. These queries are described in the sections that follow.

For a complete list of SQL commands supported by SQLite and for additional options to the commands described above, see the SQLite language support specification at http://www.sqlite.org/lang.html.

CREATE [TEMP[ORARY]] TABLE [IF NOT EXISTS] table_name (

    column_name column_type [constraint],

    ...

    );

CREATE TABLE IF NOT EXISTS family (

    ID INTEGER PRIMARY KEY,

    Last_Name NVARCHAR(63) KEY,

    Address NVARCHAR(255),

    City NVARCHAR(63),

    State NVARCHAR(2),

    Zip NVARCHAR(10));

INSERT INTO table_name (column_1, ...)

    VALUES (value_1, ...);

INSERT INTO family (Last_Name, Address, City, State, Zip)

    VALUES ('Doe', '56989 Peach St.', 'Martin', 'TN', '38237');

SELECT column_1 [, ...] from table_1 [, ...] WHERE expression;

SELECT name, age FROM people WHERE age > 18;

# Alphabetic comparison

SELECT name, age FROM people WHERE name < "Alfalfa";

# Equality and inequality

SELECT name, age FROM people WHERE age = 18;

SELECT name, age FROM people WHERE age != 18;

# Combinations

SELECT name, age FROM people WHERE (age > 18 OR (age > 55 AND AGE <= 65));

SELECT * FROM people WHERE age > 18;

SELECT First_Name, Last_Name FROM family, familymember WHERE familymember.Family_ID = family.ID AND familymember.ID = 2;

SELECT familymember.*, Last_Name from family, familymember WHERE ...

UPDATE table_name SET column_1=value_1 [, ...]

    [WHERE expression];

UPDATE familymember set Family_ID=32767 WHERE ID=179;

DELETE FROM table_name [WHERE expression];

DELETE FROM people WHERE ID=973;

DROP TABLE table_name;

DROP TABLE roster_2007;

Transaction Processing

Most modern relational databases have the notion of a transaction. A transaction is defined as an atomic unit of work that either completes or does not complete. If any part of a transaction fails, the changes it made are rolled back—restored to their original state prior to the beginning of the transaction.

Transactions prevent something from being halfway completed. This is particularly important with relational databases because changes can span multiple tables.

For example, if you are updating someone’s class schedule, inserting a new student_class record might succeed, but a verification step might fail because the class itself was deleted by a previous change. Obviously, making the change would be harmful, so the changes to the first table are rolled back.

Other common reasons for failures include:

• Invalid values—a string where the database expected a number, for example, could cause a failure if the database checks for this. (SQLite does not, however.)

• Constraint failure—for example, if the class number column is marked with the UNIQUE keyword, any query that attempts to insert a second class with the same class number as an existing class fails.

• Syntax error—if the syntax of a query is invalid, the query fails.

The mechanism for performing a transaction is database-specific. In the case of the JavaScript Database, transactions are built into the query API itself, as described in Executing a Query.

Relational Databases and Object-Oriented Programming

Relational databases and data structures inside applications should be closely coupled to avoid problems. The best in-memory architecture is generally an object for each row in each table. This means that each object in your code would have the same relationships with other objects that the underlying database entries themselves have with each other.

Tying database objects to data structures is important for two reasons:

• It reduces the likelihood of conflicting information. You can be certain that no two objects will ever point to the same information in the database. Thus, changing one object will never require changing another object.

• It makes it a lot easier to keep track of what data is stored in which table when you are debugging.

It is best to keep the names of tables and classes as similar as possible. Similarly, it is best to keep the names of instance variables as similar as possible to the names of the database fields whose contents they contain.

SQL Security and Quoting Characters in Strings

When working with user-entered strings, additional care is needed. Because strings can contain arbitrary characters, it is possible to construct a value that, if handled incorrectly by your code, could produce unforeseen side effects. Consider the following SQL query:

 UPDATE MyTable SET MyValue='VARIABLE_VALUE';

Suppose for a moment that your code substitutes a user-entered string in place of VARIABLE_VALUE without any additional processing. The user enters the following value:

'; DROP TABLE MyTable; --

The single quote mark terminates the value to be stored in MyValue. The semicolon ends the command. The next command deletes the table entirely. Finally, the two hyphens cause the remainder of the line (the trailing ';) to be treated as a comment and ignored. Clearly, allowing a user to delete a table in your database is an undesirable side effect. This is known as a SQL injection attack because it allows arbitrary SQL commands to be injected into your application as though your application sent them explicitly.

When you perform actual queries using user-provided data, to avoid mistakes, you should use placeholders for any user-provided values and rely on whatever SQL query API you are using to quote them properly. In the case of the JavaScript database API, this process is described in Executing a Query.

If you need to manually insert strings with constant string values, however, using placeholders is overkill and can make the query harder to read. To insert these strings manually, use double-quote marks around any strings that contain a single-quote mark, and vice-versa. In the rare event that you must manually insert a string that contains both types of quotation marks, use single quotes, but add a backslash before every single-quote mark within the string. For example, to insert the value

"It's a boy," he whispered softly.

you would write it like this:

'"It\'s a boy," he whispered softly.'