Chapter 4. Advanced Database Design
This chapter is all about improving the design of a database in terms of ease of management, efficiency, and limiting the risks of entering invalid data. The chapter begins with a bit of theory and relates it to the practical, in particular the Film Club database. This covers improving efficiency in terms of data storage and minimizing wasted space.
Next, the chapter covers various ways of ensuring that only valid data enters the database. The database can't check the accuracy of what you enter, but it can ensure that what you enter doesn't cause the database to stop working as expected. For example, the Attendance table in the Film Club database relies on its relationship with the Location table. The Attendance table doesn't store the full address details of meetings; it just stores a unique ID that matches a record in the Location table. If, however, no matching record exists in the Location table, then queries return incorrect results. This chapter shows you, among other things, how to enforce the relationship between tables and prevent such a situation from occurring.
You use the things you learn throughout the chapter to update and improve the Film Club database. The chapter finishes off with some tips on things to look out for and things to avoid when designing your database.
Normalization
Chapter 1 discussed how to design the database structure using tables and fields in order to avoid problems such as unnecessary duplication of data and the ...
Get Beginning SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
