In part 4 of this 5-part series on database design principles, we review Independent Multiple Relationships. By isolating relationships so that the database structure is fully hierarchical, the software will be more adaptable to future user needs, and require less reprogramming when the requirements will inevitably change in the future.
An independent multiple relationship usually occurs in many-to-many relationships where requirements are added after the initial design. As an example, let’s say that in addition to publishers, authors, and books, we also want to keep track of the book conventions where authors make presentations and host book signings. The many-to-many table might look like the following:
ConventionAuthors
- Convention ID (linked to Convention table)
- Author ID (linked to Author table)
- Presentation Start Time
- Presentation End Time
- DJ
Convention ID and Author ID are foreign keys, linked to their respective parent tables. Presentation Start and End Times define the time block that the author has for giving their speech and signing books, and DJ is the name of the DJ who will be playing music in the background and running the sound system.
Assuming that the same DJ might be used for more than one author, this design is incorrect. Two unrelated multiple relationships are in the same table. The table should be split into two as follows:
ConventionAuthors
- Convention ID (linked to Convention table)
- Author ID (linked to Author table)
- Presentation Start Time
- Presentation End Time
ConventionDJs
- Convention ID
- DJ
- Start Time
- End Time
This design will allow for a much more effective and maintainable system. In the future, the DJ’s event fee can now be stored in the ConventionDJs table. Information about their performance or complaints can also be organized in one place. This updated schema will provide for a better quality application, and require less rework as user requirements could change throughout the lifecycle of the application.
Join us next week for the final database design principle in this series – isolating semantically related multiple relationships.
Written by Andrew Palczewski
About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+
