Beginning Database Design Solutions, 2nd Edition

BSample Relational Designs

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.) ...