Most real life applications sport more than one entity and often many of these entities are related. Modeling these relations is one of the strong points of a relational database. In this chapter, we will develop an application to maintain lists of books for multiple users.
In this chapter, we will:
- Design and implement a data model consisting of several entities and relations
- Implement reusable entity and relation modules
- Look in depth at the necessity of clearly separating layers of functionality
- And encounter jQuery UI's autocomplete widget
So let's get started with it...
Before we start to design our application, let's have a good look at the different entities that need to be handled by it. The entities we recognize are a book, an author, and a user. A book may have many attributes, but here we limit ourselves to a title, an ISBN (International Standard Book Number), and a publisher. An author has just a name, but of course, if we would like to extend that with extra attributes, like the date of birth or nationality, we can always add that later. Finally, a user is an entity with a single attribute as well, the user ID.
The next important part is to have a clear understanding of the relations between these entities. A book may be written by one or more authors, so we need to define a relation between a book entity and an author entity. Also, any number of users may own a copy of a book. This is another relation we have to define, this time, between a book entity and a user entity. The following diagram may help to see those entities and their relations more clearly:
These three entities and the relations between them need to be represented in two realms: as database tables and as Python classes. Now we could model each entity and relation separately, like we did in the previous chapter for the tasklist application, but all entities share a lot of common functionality so there are ample opportunities for reuse. Reuse means less code and less code equals less maintenance and often better readability. So let's see what we need to define a reusable Entity class.
From what we learned in the previous chapters, we already know there is a shared body of functionality that each class that represents an entity needs to implement:
- It needs to be able to verify that a corresponding table exists in the database and create one if it doesn't.
- It needs to implement a way to manage database connections in a thread-safe manner.
Also, each entity should present us with a CRUD interface:
- Create new object instances
- Retrieve individual object instances and find instances that match some criteria
- Update the attributes of an object instance and synchronize this data to the database
- Delete an object instance
That is a lot of shared functionality, but of course a book and an author are not identical: They differ in the number and type of their attributes. Before we look at the implementation, let's illustrate how we would like to use an Entity class to define a specific entity, for example, a car.