Real-World Example: Creating a Publishing Tracking System
|
|
You'll now walk through the database design process with a step-by-step example. The Poet's Circle is a publisher that publishes poetry and poetry anthologies. It is keen to develop a new system that tracks poets, poems, anthologies, and sales. The following sections show the steps taken from the initial analysis to the final, working database.
Poet's Circle Database Phase 1: Analysis
The following information is gleaned from speaking to the various stakeholders at Poet's Circle: They want to develop a database system to track the poets they have recorded, the poems they write, and the publications they appear in, as well as the sales to customers that these publications make.
The designer asks various questions to get more detailed information, such as "What is a poet, as far as the system goes? Does Poet's Circle keep track of poets even if they haven't written or published poems? Are publications recorded even before there are any associated poems? Does a publication consist of one poem or many? Are potential customer details recorded?" The following summarizes the responses:
-
Poet's Circle is a publisher that bases its choices of publications on an active poetry community on its website. If enough of the community wants a poem published, Poet's Circle will do so.
-
A poet can be anybody who wants to be a poet, not necessarily someone who has a poem captured in the system or someone who has even written a poem.
-
Poems can be submitted through a web interface, by e-mail, or on paper.
-
All captured poems are written by an associated poet, whose details are already in the system. There can be no poems submitted and stored without a full set of details of the poet.
-
A publication can be a single poem, a poetry anthology, or a work of literary criticism.
-
Customers can sign up through a web interface and may order publications at that point in time or express interest in receiving updates for possible later purchases.
-
Sales of publications are made to customers whose details are stored in the system. There are no anonymous sales.
-
A single sale can be for one publication, but many publications can also be purchased at the same time. If more than one customer is involved in this sale, Poet's Circle treats it as more than one sale. Each customer has their own sale.
-
Not all publications make sales—some may be special editions, others never sell any copies!
Poet's Circle Database Phase 2: Design
Based on this information, you can begin your logical design and should be able to identify the initial entities:
-
Poet
-
Poem
-
Publication
-
Sale
-
Customer
The Poet's Circle is not an entity or even an instance of the publisher entity. Only if the system were developed for many publishers would publisher be a valid entity.
Neither website nor poetry community is an entity. There is only one website, and anyway, a website is merely a means of processing the data to populate the database. There is also only one poetry community as far as this system is concerned, and there is not much you'd want to store about it.
Next, you need to determine the relationships between these entities. You can identify the following:
-
A poet can write many poems. The analysis identified the fact that a poet can be stored in the system even if there are no associated poems. Poems may be captured at a later point in time, or the poet may still be a potential poet. Conversely, many poets could conceivably write a poem, though the poem must have been written by at least one poet.
-
A publication may contain many poems (an anthology) or just one. It can also contain no poems (poetry criticism, for example). A poem may or may not appear in a publication.
-
A sale must be for at least one publication but may be for many. A publication may or may not have made any sales.
-
A customer may be made many sales or none at all. A sale is made for one and only one customer.
You can identify the following attributes:
-
Poet: first name, surname, address, telephone number
-
Poem: poem title, poem contents
-
Publication: title, price
-
Sales: date, amount
-
Customer: first name, surname, address, telephone number
Based on these entities and relationships, you can construct the entity-relationship diagram shown in Figure 9.6.
Figure 9.6: Poet's Circle entity-relationship diagram
As shown in Figure 9.6, there are two many-to-many relationships. These need to be converted into one-to-many relationships before you can implement them in a DBMS. The result is Figure 9.7, with the intersection entities poem-publication and sale-publication.
Figure 9.7: Poet's Circle entity-relationship diagram, with the many-to-many relationships removed
Now, to begin the logical and physical design, you need to add attributes that can create the relationship between the entities, and specify primary keys. You do what's usually best and create new, unique primary keys. Tables 9.1 through 9.7 show the structures for the tables created from each of the entities.
| Field | Definition |
|---|---|
| poet code | primary key, integer |
| first name | character (30) |
| surname | character (40) |
| address | character (100) |
| postcode | character (20) |
| telephone number | character (30) |
| Field | Definition |
|---|---|
| poem code | primary key, integer |
| poem title | character (50) |
| poem contents | text |
| poet code | foreign key, integer |
| Field | Definition |
|---|---|
| poem code | joint primary key, foreign key, integer |
| publication code | joint primary key, foreign key, integer |
| Field | Definition |
|---|---|
| publication code | primary key, integer |
| title | character (100) |
| price | numeric (5.2) |
| Field | Definition |
|---|---|
| sale code | joint primary key, foreign key, integer |
| publication code | joint primary key, foreign key, integer |
| Field | Definition |
|---|---|
| sale code | primary key, integer |
| date | date |
| amount | numeric (10.2) |
| customer code | foreign key, integer |
| Field | Definition |
|---|---|
| customer code | primary key, integer |
| first name | character (30) |
| surname | character (40) |
| address | character (100) |
| postcode | character (20) |
| telephone number | character (30) |
MySQL will have no problem with this design and is selected as the DBMS. Existing hardware and operating system platforms are also selected.
Poet's Circle Database Phase 3: Implementation
With the design complete, it is time to install MySQL and run the CREATE statements, as follows:
mysql> CREATE TABLE poet (poet_code INT NOT NULL, first_name VARCHAR(30), surname VARCHAR(40), address VARCHAR(100), postcode VARCHAR(20), telephone_number VARCHAR(30), PRIMARY KEY(poet_code)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE poem(poem_code INT NOT NULL, title VARCHAR(50), contents TEXT, poet_code INT NOT NULL, PRIMARY KEY(poem_code), INDEX(poet_code), FOREIGN KEY(poet_code) REFERENCES poem(poet_code)) type=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE publication(publication_code INT NOT NULL, title VARCHAR(100),price MEDIUMINT UNSIGNED, PRIMARY KEY(publication_code)) type=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE poem_publication(poem_code INT NOT NULL, publication_code INT NOT NULL, PRIMARY KEY(poem_code, publication_code), INDEX(poem_code), INDEX(publication_code), FOREIGN KEY(poem_code) REFERENCES poem(poem_code), FOREIGN KEY(publication_code) REFERENCES publication(publication_code)) TYPE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> CREATE TABLE sales_publication(sales_code INT NOT NULL, publication_code INT NOT NULL,PRIMARY KEY(sales_code, publication_code)) TYPE =InnoDB; Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE customer(customer_code INT NOT NULL, first_name VARCHAR(30), surname VARCHAR(40), address VARCHAR(100), postcode VARCHAR(20), telephone_number VARCHAR(30), PRIMARY KEY(customer_code)) TYPE=InnoDB; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE sale(sale_code INT NOT NULL, sale_date DATE, amount INT UNSIGNED, customer_code INT NOT NULL, PRIMARY KEY(sale_code), INDEX(customer_code), FOREIGN KEY(customer_code) REFERENCES customer(customer_code)) TYPE = InnoDB; Query OK, 0 rows affected (0.08 sec)
Poet's Circle Database Phases 4–6: Testing, Operation, and Maintenance
Once the database is ready and the application programs have been rolled out, it's time for the testing to begin. While the other phases of the database lifecycle can occur reasonably independently of the systems development process, part of the testing phase is testing how all components of the system run together.
Load testing may indicate that MySQL has not been set up to handle the expected 600 concurrent connections, and the configuration file needs to be changed. Other tests may indicate that in certain circumstances, duplicate key errors are received, as the locking mechanism is not uniformly implemented, and the application does not handle locking correctly. The application needs to be fixed. Backups also need to be tested, as well as the ability to smoothly restore from backup with a minimum of downtime.
| Warning | Testing is one of the most neglected and critical phases. A designer or manager who does not properly account for testing is simply incompetent. No matter how tiny your system, make sure you allocate time for thorough testing and time for fixing the inevitable bugs. |
Once testing is complete, the system can be rolled out. You decide on a low-key rollout and give a few selected poets access to the website to upload their poems. You discover other problems: Obscure browsers have incompatibilities that lead to garbled poems being submitted. Strictly speaking, this doesn't fall into the database programmer's domain, but it's the kind of situation testing will reveal once all the elements of the system are working together. You decide to insist that users make use of browsers that can render the developed pages correctly, and browsers that don't adhere to these standards are barred from uploading.
And soon enough, the system is rolled out completely. Maintenance, though, is a never-ending task, and with large numbers of updates and deletes being performed, the database has a tendency to become fragmented. The administrator must run regular OPTIMIZE statements, and of course, the inevitable disk failure leads to an all-night restore session and much thankfulness for the ease of use of mysqldump.
|
|
