JavaServer Pages, Second Edition

7.1 Designing the Tables

As a data model was already developed in Chapter 5, the simplest plan of attack would be to turn this model into SQL and create the database. Once that's done, an object-relational mapping tool could turn these tables back into beans, and the job would practically be finished. The reason is that, if all the naming conventions for bean properties and column names are carefully followed, the new beans will have the same property names as the original ones, and none of the pages or forms will need to change at all. This is another big advantage to the model/view/controller paradigm: It makes it possible to change completely the way the model works; as long as the interfaces between the model and the view stay the same, the view will not need to be rewritten.

Although it would be very easy to follow this plan of attack and recreate the existing site on top of a database, doing so would preclude a great deal of possible new functionality that a database could offer. Creating a site based on hard-coded beans leads to necessary restrictions in the ways in which the data could be accessed. Because there is no easy way to filter out a subset, it is necessary to show all the sections within in an edition and all articles within a section. With a database and a set of beans that make it easy to construct SQL where clauses, the data can be managed, grouped, and arranged in any way that might be useful. In particular, users now have the option to view only sections in which they are interested; further, it is possible to rank articles within those sections to indicate which ones are likely to be the most interesting.

To support these features, the data model will need to be rethought a little. The basic fields in the old beans will still be needed; for example, the ArticleBean will still need the text of the article, the time it was published, a headline, a summary, and the name of the author.

This last item already suggests one major change that should be made. In the CD database from Chapter 6, it was noted that rather than store the artist's name in every CD, it made more sense to have a separate table of artists and to link artists to CDs through the use of a small ID. The same is true for authors and articles; it would be possible to connect an author to an article by storing in the article table the user_id of the author rather than the author's name. This way, if an author's name changes, it will not be necessary to change every article; the user_info table can simply be updated in one place. This is also more efficient, as the name may take up 20 bytes to store, but an ID will take only 4. This process of pulling common data into separate tables is called normalizing the database.

More generally, when working with a database, it is important to consider what relationships will exist between otherwise apparently unconnected data items. For example, currently there is no relationship between sections and users, but for users to be able to select the set of sections in their editions, such a relationship must be included in the database. The question then becomes, How this should be modeled?

One possibility would be to add to the user_info table some additional columns, such as wants_section_1, wants_section_2, and so on. But this is not very general; if it creates a new section a year from now, JNT will need not only to update all the users but also to change the very structure of the database and modify all the beans and JSPs that use this table. That is something that no one should have to live through if it can be avoided, and, fortunately in this case, it can be avoided.

Following the examples from Chapter 6, each of the tables will have a unique ID, so each user will have a user_id, each section will have a section_id, and so on. So, to model the connection between users and sections, another table that will have a user_id and a section_id can be introduced. If user 50 does not want section 3, this new table would have a row where user_id = 50 and section_id = 3. A table like this, which holds only the IDs of other tables and has no data of its own, is known as a join table.

It would also be possible, and in some ways simpler, to keep track of which sections a user does want. The advantage of storing unwanted sections is that if a new section is created, every user will initially get it by default and can then opt to turn it off. If the database tracked only sections a user did want, the user would need to act explicitly to add new sections and hence might miss out on some good content.

Two more new tables will be used to connect articles to users, although less directly. First, the notion of a keyword will be added to the system. A keyword is a single word or short phrase that describes an article. This is more finely grained than sections; whereas a section might deal with a broad category, such as "Java on consumer devices," the keywords might list particular devices or vendors that support Java.

As each article may have many keywords, each connected to many articles, another join table will be used to connect them. In order to do so, this new table will have a keyword_id and an article_id. This also suggests creating a similar table to connect users to keywords by maintaining a list of keyword_id and user_id pairs. This table will allow users to indicate the set of keywords in which they are interested. With these two tables, a user can be connected to an article by looking for good matches between article keywords and user keywords.

The database design is almost finished; the only other thing needed is a way to ensure that only Java News Today staff can create new articles. To do this, a new field will be added to the user_info table to mark certain users as reporters. With that done, the SQL needed to create the Java News Today database is shown in Listing 7.1.

Listing 7.1 The JNT schema

create table user_info (
        usr_id          int,
        username        char(40),
        password        char(40),
        name            char(20),
        bg_color        char(6),
        text_color      char(6),
        banner_color    char(6),
        reporter_ind    char(1)
);

create table section (
        section_id       int,
        name             char(20),
        summary          varchar(1024)
);

create table article (
        article_id       int,
        section_id       int,
        author_id        int,
        created_date     datetime,
        headline         varchar(80),
        summary          varchar(1024),
        text             varchar(4096)
);

create table keyword (
        keyword_id       int,
        name             char(20)
);

create table user_sections (
        user_id          int,
        section_id       int
);

create table user_keywords (
        user_id          int,
        keyword_id       int
);

create table article_keywords (
       article_id        int,
       keyword_id        int
);

create table comment (
        comment_id       int,
        article_id       int,
        author_id        int,
        created_date     datetime,
        text             varchar(4096)
);

create table quiz (
        question         varchar(80),
        answer1          varchar(80),
        answer2          varchar(80),
        answer3          varchar(80),
        correct_answer   int
);

The names for fields and tables follow certain well-accepted conventions. Database names use underscores to separate multiword names; when beans are generated from these tables, the underscores will be removed, and the letter following the underscores will be capitalized. Database fields ending with _ind are indicators with the value Y or N. The equivalent bean property will have values true or false and will therefore be suitable for use as the tests in c:if and c:when tags.