When you break a data model down into small pieces, there are really only three types of data relationships: one-to-one, one-to-many, and many-to-many (modeled with one-to-many relationships). If you think in those terms, then you really don't need examples. Just break the problem into small enough pieces and start assembling these three kinds of relationships. (Inheritance and subtyping forms another kind of logical relationship that you can also model.)

However, it may be useful to see more complete examples that include several different entities associated in typical ways. This appendix is intended to show you those kinds of examples.

Note that many different problems can be modeled in very similar ways. For example, consider a typical library. It has one or more copies of a whole bunch of books and lends them to patrons for a specific amount of time. Patrons can renew a book once and pay late fees if they fail to return the book on time. Some libraries have other policies such as not charging late fees on children's books, not charging late fees at all, or sending out specially trained book-sniffing dogs to hunt you down if you don't return a book. (I'm guessing on that last one.)

Now, consider a business that rents party supplies such as tables, chairs, big tents, bouncy castles, dunk tanks, doves, and so forth. Like a library, this business has multiple copies of many of these items. (It probably has dozens of tables and hundreds of chairs.) Also like a library, this business “loans” (for a fee) its items to customers and charges a late fee if an item isn't returned on time.

Though a library and a party rental store are very different organizations, the structure of their databases is quite similar.

As you look at the examples in this appendix, think about variations that might use a similar structure. Though your application may not fit these examples exactly, you may find an example that uses a similar structure.

Also note that different applications might use very different database designs for the same data. The exact fields and sometimes even the tables included in the design depend on the application's focus.

For example, consider a large company's employee data. If you're building an application to assign employees to tasks for which they are qualified, your database will probably have an EmployeeSkills table that matches employee records to their skills. You'll also need a Tasks table that describes tasks and lists the skills that they require.

In contrast, suppose you need to build a human resources application that tracks employee payroll deductions for retirement contributions, medical coverage, and so forth. Although this application deals with the same employees, it doesn't need skill or task data, so it doesn't need the EmployeeSkills or Tasks tables. It also needs new employee data not required by the work assignment project, such as employee Social Security number, bank account number, next of kin, and anniversary date.

These two applications deal with the same physical entities (employees) but have very different data needs. Because the type of data that you might need to store in a particular table depends on your application, these examples don't try to be exhaustive. For employee data, a table might include a few fields such as FirstName, LastName, and HireDate to give you an idea of what the table might include, but you'll have to fill in the details for your application.

The following sections each describe a single example database design and give some ideas for variations that you may find useful. In particular, many of these models can include all sorts of address information such as Street, Suite, Building, Office, MailStop, POBox, RuralRoute, City, Urbanization, State, Province, Zip, PostalCode, Country, Phone, and Extension. To keep the models simple, some tables include a single Address entry to represent address information. You should use whatever address information is appropriate for your application.

These models are basically relational, but some might be interesting to study in NoSQL databases. For example, you could use a graph database such as Neo4j AuraDB to store movie information. Then you could examine the relationships among actors, directors, and movies. That might let you find patterns such as groups of actors who produce good movies. Add in customer ratings and you can search for preference patterns. “If you like this movie, then you may also like that movie.”

Many simpler designs would also work well in a document database such as MongoDB Atlas. Any time you need to store self-contained records, you can put the information in JSON files and store them in a document database. For example, cooking recipes, Pokémon card data, fantasy football player stats, and Cub Scout popcorn preorders (so you know where to deliver later) are all relatively self-contained and would fit well in JSON documents.

For example, you the database's JSON files could hold recipes. Then you could search the Keywords field for “holiday” or search the Ingredients field for “goji berries.”

BOOKS

The entities in a books database include the books themselves, authors, and publishers. Depending on your application, you may also want to include information about the book's various editors (acquisitions editors, managing editors, production editors, technical editors, and copy editors), artists, photographers, and all of the other many people associated with the book. If the book is a compilation (for example, a collection of articles or short stories), the “Editor” who put it all together is similar to the author of the work as a whole.

Some of these people may be associated with the publisher. For example, many editors work for a particular publisher, although others are hired by a publisher as contractors and editors often move from one publisher to another over time. So if you want to record these types of associations, you will probably need to allow editors to have a separate publisher affiliation for each book.

Books may have many printings and editions. Different printings contain few or no differences. Different editions may contain completely different content or may come in different media such as paperback, hardcover, audio CD, online video, DVD, PDF file, large print edition, and so forth.

Figure B.1 shows a simple book database design that models books, authors, and publishers. Book categories include Cooking, Home & Garden, Science Fiction, Professional, Bodice Ripper, and so forth. Recall that dashed lines represent lookup tables.

Figure B.2 shows a more complex design that includes media and edition information. The LengthUnit field depends on an item's medium. For example, audio books have length measured in minutes, whereas printed books have length measured in pages.

Figure B.3 generalizes the author data to include other types of people such as editors, artists, and photographers.

MOVIES

In many ways, movies are similar to books. Both are created by a team of people who may work on other projects as well, both are owned by some sort of entity (publisher or studio), and both have similar basic information such as lengths, descriptions, and URLs. Many of the details are different (movies have actors instead of authors and directors instead of editors), but some of the basic structure of the data is similar.

Figure B.4 shows a version of Figure B.3 that has been modified to hold movie data. To avoid confusion, this model uses the word “job” to represent a person's responsibility in the project (Actor, Director, Producer, Grip, Best Boy, Python Wrangler, and so forth) and “actor role” to represent a part played by an actor.

Notice that a single person may appear in more than one MoviePeople records for a movie. For example, an actor may play more than one role and may be the director. (I suppose an actor could also be a crew member but it's hard to imagine Orlando Bloom catering or Julia Roberts stringing cables.)

Notice also the one-to-one relationship between the MoviePeople and ActorRoles records. Each ActorRoles record represents a single role in a particular movie. If someone later shoots a remake of a movie, this model assumes that each of the characters gets a new ActorRoles record because the characters will represent new interpretations of the originals. If the ActorRoles records are abbreviated enough that they will be the same for different versions, you could make this a one-to-many relationship. You might also want to add version information similar to the Editions table in Figure B.3.

(I'll know a lot more about the movie industry so I can build better models after Steven Spielberg makes a movie out of this book.)

MUSIC

Though music collections, books, and movies all have similarities (for example, they are all produced by a team of people), some important differences exist. Songs are grouped by album and albums are grouped by band. Some or all of a band may participate in any given song on an album. Over time, the members of a band may change (except for U2), and one artist may be in many bands or even the same band more than once (in case they have a falling out and then later decide to make a reunion tour when the money runs out).

Although you could make similar distinctions for books (you could group books by series and you might define working combinations of authors), it doesn't make as much sense unless your application really needs to focus on that kind of information. Figure B.5 shows a design to hold music album data.

In this model, roles might include such values as Lead Vocal, Song Writer, Choreographer, and Costume Designer. The Instruments table holds values such as Electric Guitar, Drums, Zither, and Electric Stringed Didgeridoo.

Notice that one person may play multiple roles in the same song. Often, this will be singer and songwriter, but some artists sing while they play and really nimble artists may play multiple instruments.

DOCUMENT MANAGEMENT

A document management system provides features such as storing documents, managing concurrent use, controlling permissions, allowing you to create new versions over time, and showing you the changes between different versions. They also let you “fork” documents to create different branches that form a document hierarchy. For example, you might make a cookbook and then fork a copy to modify into a gluten-free cookbook. Those two branches could then evolve separately over time.

This is different from a NoSQL document database, which focuses on storing and querying documents. You could probably use a document database to build a document management system, but that's not what I'm talking about here.

Before you rush out and build a document management system, you might consider using one that is already available. Systems such as Git (https://git-scm.com), Concurrent Versions System (CVS—www.nongnu.org/cvs), and Apache Subversion (https://subversion.apache.org) manage multiple document versions quite effectively. They may not provide all of the features that you might add (such as advanced keyword queries), but they provide enough features to be quite useful without all of the work of building your own system.

However, Figure B.6 shows a data model that you could use to manage multiple document versions. This model assumes that a single author makes each version of a document and that multiple versions have major and minor version numbers as in 1.0, 1.1, 2.0, and so forth. The model allows you to store keywords and comments for the document as a whole and for each version.

The DocumentVersions table's Content field can hold either the complete document version or a list of differences between this version and the previous one.

CUSTOMER ORDERS

Several of the examples described in this book include data to record customer orders. Figure B.7 shows one of the simpler variations. It assumes a customer has a single address and all orders for that customer are shipped to that address.

EMPLOYEE SHIFTS AND TIMESHEETS

Employee shifts and timesheet records are very similar. Both record a date and hours scheduled or hours worked for an employee. Figure B.8 shows a simple model for storing shift and timesheet data.

EMPLOYEES, PROJECTS, AND DEPARTMENTS

Figure B.9 shows a model for storing employee, project, and department data. This model assumes that an employee can be in any number of projects but only one department. The DepartmentRoles table contains values such as Manager, Secretary, Member of Technical Staff, and Sycophant. The ProjectRoles table contains values such as Project Manager, Lead Developer, Toolsmith, and Tester. The primary key for EmployeeProjects includes the ProjectRole field, so a single employee can play multiple roles in a single project (for example, Project Manager and Doomsayer).

You can use this design for matrix management with only a few changes. In matrix management, an employee has a functional manager who coordinates employees who have similar functions (mechanical engineering, optical design, software development, and so forth). The functional manager guides the employee's career development and handles project reviews.

Project managers determine what the employee does on a particular project.

For example, an electronics technician might be in the Electronics department, report to a functional manager in that department, work on several projects in various other departments, and report to project managers in those departments.

If each functional department has a single manager, you can use Figure B.9 by simply adding the functional managers as members of their departments with DepartmentRole set to Functional Manager. If there is not a simple one-to-one relationship between departments and functional managers, you can add a FunctionalId field to the Employees table as shown in Figure B.10.

EMPLOYEE SKILLS AND QUALIFICATIONS

Employee skills and qualifications are important when certain jobs require them. For example, machining a particular glass part might require a technician who is certified to use a computerized ultrasonic cutter.

Depending on your application, some qualifications expire, so you need to take a class and/or test to remain current. For example, a Red Cross CPR certification lasts 2 years and the USA Gymnastics Safety Certification lasts 4 years (assuming you pay the annual fee). The design shown in Figure B.11 assumes that all skills expire. If a skill does not expire, you can set its ValidDuration to a really large value such as 100 years (or perhaps 1 million years for a longevity coach certification). If none of the skills you track expire, you can remove the ValidDuration field.

In a more formal setting, you might need to add more fields to the EmployeeSkills table. For example, you might need to track a certification number, issuing agency, and so forth to prove an employee has a certain skill.

Note that this design tracks employee skills but doesn't do anything with them. It would be up to the user interface application to ensure that only employees with the proper skills are assigned to a given job.

IDENTICAL OBJECT RENTAL

Just about any situation where something is given to a customer for a limited period of time can be modeled as a rental. For example, construction equipment rentals, kayak rentals, hourly contractors, and hotel rooms can all be treated as different kinds of rentals.

Figure B.12 shows a design that holds data for simple rental of identical objects. For example, a surf shop might have a dozen each of six different surfboard models and you don't really care which 8′ Wavestorm you get (as long as it's not covered in tooth marks).

Some businesses have items that are distinct, but the differences may not matter to you. For example, a kimono rental store might have hundreds of kimonos with different colors and patterns, but you may want to treat them as interchangeable in the database.

If you want to track specific instances of rented assets (for example, to keep track of the number of times each surfboard or kimono is rented), you can add an AssetInstances table, as shown in Figure B.13.

There are many variations on rental and late fees. A bicycle rental store might charge a daily fee and no late fees. A store that rents heavy equipment such as backhoes and pile drivers might charge an hourly fee and large late fees. A public library might charge no rental fee and a small daily late fee. (My local library charges no fees for children's books.)

DISTINCT OBJECT RENTAL

If the objects that are rented are distinct, then you need to modify the design slightly. Figure B.14 shows a rental variation that is more appropriate for a company that hires its employees as contractors.

Unlike kayak rental and libraries, the contractor “rental” design models distinct entities because different contractors are not all the same (unless you're the Army or a mega-corporation that treats people as interchangeable “assets”).

Other businesses can model distinct entities in a similar manner, although the exact details will usually differ. For example, Figure B.15 shows a model designed for hotel reservations.

A customer calls and makes a reservation. Initially the reservation is for a type of room, not a particular room. When the guests check in, the clerk fills in the floor and room number.

A Guest is a person staying in the room. (Several people may be staying in the same room.) A Customer is a person who pays for a reservation (only one person pays). If the IsConfirmed field has the value True, the reservation is confirmed for late arrival, so the hotel will hold the room and charge the Customer's credit card if they show up at midnight. (However, I've had my room sold to another customer despite being confirmed for late arrival, so the model may need tweaking in the real world.)

A Room Type defines the amenities in the room. Amenities include such things as hot tubs, balconies, bathrooms, nonsmoking, pets allowed, and king-sized, heart-shaped rotating beds that vibrate.

A Price Type defines the prices for a room. Price Types include values such as Business, Preferred, Frequent Visitor, Walk In, and Chump Whose Flight Was Canceled At The Last Minute And Is Desperate. (Hotels typically code price types as A, B, C, and so forth so the Chump doesn't notice he's paying four times as much as the family from Des Moines who booked three months in advance.)

STUDENTS, COURSES, AND GRADES

Figure B.16 shows a model for storing student, course, and grade data.

A Course represents a type of class (Introduction to Database Design). A Course Offering is a particular instance of a Course (this year's winter term Introduction to Database Design class on Tuesdays and Thursdays from 9:00 to 10:30 in Building 12, room B-16).

A Grade Item is something in the course that receives a grade, such as Quiz 1, Midterm, Term Paper 2, and Attendance. The CourseGradeItem table's Weight field lets you assign different weights to different Grade Items. For example, you might have 10 quizzes with a weight of 10 each and a final with a weight of 100, so it's worth half of the total grade.

Grade Items are somewhat tricky because a StudentGrades record should have an appropriate GradeItem value. You might like to make the record's combined CourseOfferingId/GradeItem be a foreign key into the CourseGradeItems table, but that table uses CourseId as a key, not CourseOfferingId. Ensuring that the CourseGradeItems record has a valid GradeItem must be handled as a business rule.

This database will probably be stored in two pieces: an online piece holding the current school year's data and a data warehouse piece holding older data. The data would undergo final consistency checks before it is moved from the current database to the warehouse. For example, you would verify that students have grades for every CourseGradeItem defined for their classes.

Other Students fields in the online database would probably record summary information. For example, a GPA field could record the student's grade point average for courses in the data warehouse. That field would be redundant because you could recalculate it from the data in the data warehouse, but placing it in the online database would let you avoid opening the data warehouse for day-to-day queries.

TEAMS

Figure B.17 shows a relatively simple data model for team sports. This design is based on a typical volleyball league. Players belong to a team and teams play in tournaments.

This model allows each player to be associated with several positions. You can include special non-playing “positions” such as captain, coach, and water boy, or you can add them as new fields in the Players or Teams table depending on your needs.

Tournaments occur at venues that have a given number of courts. A match is a game between two teams. (In practice, a match will include several games, so the scores may be games won rather than points won. In a really serious competition, you would need to expand the model to save scores for individual games in a match, so you can compare points head-to-head in case there's a tie based on games alone. In fact, official volleyball record sheets include so much detail that you can figure out exactly when each point was made by each team and every player's location at the time—at least you can if you're good at solving puzzles.)

In a normal tournament, teams play against each other in pools. For example, in a tournament of 12 teams, the teams might be divided into two pools of six with each pool on a separate court. Each pool would play a round-robin where every team plays against every other team in its pool. Then the top two teams from each pool would enter single-elimination playoffs.

You can modify the simple design shown in Figure B.17 to handle non-tournament situations. For example, in many soccer leagues, teams play one game a week so there isn't really a notion of a tournament. In that case, you can pull the relevant tournament fields (VenueId, Date, Notes) into the Matches table. You might also want to make some cosmetic changes such as changing “court” to “field” or “pitch.”

INDIVIDUAL SPORTS

An individual sport such as running doesn't need all of the team information recorded in the previous model. Instead, its database can focus on individual statistics and accomplishments.

Figure B.18 shows a model to hold running information. If you only store basic race information, you can treat races like any other run. If you're more competitive and want to record race data such as finishing position, position in age group, mile times, number of bathroom breaks, and so forth, you can add new Races and RunnerRaces tables similar to the Runs and RunnerRuns.

VEHICLE FLEETS

Fleet tracking can be quite complex. Different parts of a business might want to track the vehicles' cargo and weights, current location, special equipment and tools, leases, repairs and maintenance, mileage, equipment, drivers, taxes, fuel use and taxes, and so forth.

For this example, you should know a little about the International Fuel Tax Agreement (IFTA). IFTA is an agreement among most U.S. states and Canadian provinces to distribute fuel taxes fairly.

Each state and province charges a different tax rate on various kinds of fuel such as gasoline, diesel, propane, E-85, A55, and several others. (Perhaps the list will soon include hydrogen, capacitance gel, and antimatter.) The taxes are included in the price at the pump and you've been paying them for years, probably without thinking about it.

The system is simple and makes sense until you consider a big fleet of vehicles that buys fuel in one state and then drives mostly in another state. For example, suppose your business is in Kansas City, Missouri but you do most of your driving across the river in Kansas City, Kansas. Fuel is cheaper in Missouri so you buy yours there. Kansas screams, “No fair! You're paying fuel taxes to Missouri but using our roads!” Enter ITFA.

Each quarter, you need to file IFTA tax forms listing every mile you drove and every drop of fuel you purchased in every state or province. You then need to pay any extra taxes that you owe based on how much tax you paid in each state and where you drove. In this example, you probably owe Kansas some money. The net result is there's much less incentive for you to cross borders to buy fuel. The IFTA agency gathers all of these records from fleets all over North America, performs lengthy calculations, and then makes the states pay each other the differences between their taxes collected and what they should have collected based on miles driven. The numbers tend to cancel out so the grand totals aren't necessarily big.

Figure B.19 shows a model designed to hold license, permit, fuel, and mileage data. Each field marked with an asterisk should be validated against a States lookup table, but to keep the model simple (relatively simple, anyway), the States table and its links aren't shown.

The model's FuelPurchases table records the states in which fuel is purchased. The TripStateEntries records the mileages at which a vehicle entered a new state. By subtracting Mileage values in subsequent TripStateEntries, you can calculate the number of miles driven in each state.

Another interesting case is modeling jobs, employees, and vehicles with special requirements, tools, and skills. Employees have tools (such as wrenches, ohm meters, and chainsaws) and skills (such as the ability to fix dishwashers, install phones, and juggle). Vehicles have equipment such as pipe benders and threaders, cherry pickers, and pole setters.

Finally, jobs require certain skills and tools. For example, if you need to haul a lot of logs, you need a vehicle with a tree grapple and an employee who has tree grappling as a skill. (“Tree grappling” sounds like a wrestling move but it's not.)

Figure B.20 shows a data model to store this information. The model is simplified and leaves out a lot of information. For example, you may need to add job addresses, appointments, site contact, and so forth.

Note that assigning a tool to an employee implies that the employee can use the tool. You wouldn't give a defibrillator to an employee who didn't know how to use it. However, if you also want to model employees signing equipment in and out, you might need to make this assumption explicit by giving the employee a Defibrillator skill.

This model assumes that multiple employees may be assigned to a single job. You could allow an Assignments record to have a null VehicleId value to allow two employees to ride in the same vehicle.

Of course, once you have this data stored, someone will need to figure out a way to match employees, vehicles, and jobs to get the most work done as efficiently as possible, and that's a whole different book.

CONTACTS

The most obvious application that needs to store contact information is an address book, but many other applications store contact information too. Most complex applications that involve interaction among customers, employees, vendors, and other people can benefit from a contact database. For example, an order placement and processing application can use contact data to keep track of customer calls that place orders, change orders, request returns, and register complaints.

Figure B.21 shows a general contact data model. An application can use these tables to remember contacts at different times covering different topics.

If you want to integrate contact data in an application involving multiple employees, you may want to add an EmployeeId field to the Conversations table so that you know who talked to the customer. You might also want to add fields to refer to a customer order to help further define the conversation. That would allow you to search for all of the conversations related to a particular order.

PASSENGERS

There are several ways you might like to model vehicles with passengers. For example, typically city buses don't take reservations and don't care where passengers sit as long as the number of passengers doesn't exceed the vehicle's capacity (which in larger cities seems to be approximately two passengers per cubic foot of space).

Figure B.22 shows a simple design to track the number of passengers on a bus.

A route defines the stops that a bus will take. The information for each stop includes the time it should take to get to that stop and the duration of time that the bus should ideally wait at that stop.

A trip represents a bus traveling over a route. The TripStops records correspond to RouteStops records and record actual times and passenger counts. (In practice I don't know how often drivers record passenger numbers. As long as one more passenger can pile on top, most drivers don't seem too bothered.)

Figure B.23 shows a slightly more complex model that allows passengers to reserve room on a bus but not to reserve individual seats. This model is intended for long-distance common carriers such as long-distance buses (Greyhound, Trailways) and railroads. In this model, the customer makes reservations to ensure that a seat is available on each leg of the trip but specific seats are not assigned.

This model is very similar to the previous one except it includes Reservations and ReservationSeats tables. Each Reservations record records information about a customer's trip. A ReservationSeats record holds information about a set of seats on a particular bus trip. The collection of ReservationSeats records corresponding to a particular Reservations record contains all of the information about the buses that a passenger's trip will use.

You can model airline and other travel where passengers have previously assigned seats using a very similar model. The only change you need (at least to model this part of the system) is to add assigned seat information to the ReservationSeats data. You could also add meal selection and other special information to each seat.

Note that these databases are typically enormous. For example, a typical large airline runs several thousand flights per day holding up to a few hundred passengers each. That means the Trips and TripStops tables grow by a few thousand records per day and the ReservationSeats table might grow by a few hundred thousand records per day. If you allow passengers to reserve seats up to a year in advance, the database must be able to hold several hundred million records.

Keeping such a large and quickly changing database running efficiently 24 hours a day is a Herculean effort. It may require huge disk farms, segmented data, special route-finding algorithms, and massive backup and warehousing processes. In other words, don't try this at home.

RECIPES

This may seem like a silly example, but it demonstrates how to store a set of instructions that require special equipment such as pots, pans, and ingredients.

A recipe database needs to store basic information about recipes such as their names, difficulty, and tastiness rating. It also needs an ingredient list and instructions. Figure B.24 shows a simple recipe database design. This model assumes the Difficulty and Rating fields are simple numeric values (for example, on a 1 to 10 scale). If you wanted to, you could change them to values such as Easy, Medium, and Hard, and make them foreign keys to lookup tables.

You can use this design to store information about other assembly tasks (such as putting together skateboards, tuning a car, building a tokamak reactor, and so forth) or more generally for giving instructions for complex tasks (troubleshooting a wireless network or deciphering a long-distance calling plan).

Unfortunately, generalizing this model to pull information out of the steps is trickier than it might initially seem. For example, you might like to make an instruction record refer to an ingredient and then tell you what to do with it as in “Oatmeal, 2 cups, mix.” That instruction would work but others are more complex.

For example, a recipe might ask you to mix different ingredients in separate bowls and then combine them. To break that information out, you would probably need to record the bowls as equipment and then somehow associate ingredients with each bowl. Some recipes call for even more complex steps such as separating eggs, scalding milk, caramelizing sugar, changing temperatures during cooking, and even lighting food on fire.

With enough time and effort, you might be able to write a cooking language to let you represent all of these operations (you could call it CML—Cooking Markup Language) but what would you have gained? Breaking instructions down to that level would let you do amazing things like finding all recipes that require you to perform certain tasks such as “powderizing” oatmeal in a food processor, but how often will you need to perform those kinds of searches?

The simpler model already lets you search for specific tools, ingredients, and temperatures, so it's probably best to stick with that model unless you have a very specialized need with well-defined steps. If necessary, you can add keywords to the recipes to let you search for particular unusual tools and techniques such as flambé and fossil-shaped gelatin molds.

A simple recipe database would also fit naturally in document database. Each document would be a JSON file describing a recipe and having fields that let you search for keywords or specific ingredients.