The Database Layer
While Sam, Teri, and Al have been brainstorming application design, Vijay has been creating preliminary plans for a database layout (see Figure I-2.5). He hasn't actually created any databases yet, as that would be premature at this point (after all, no final design exists). But he knows that visuals will help the team think through the design when they meet later this week.
I-2.5 Vijay's initial database schema.
Vijay breaks content into two tablesone for the definition of the content (what it is, what category it's in, when to display it) and another for the content itself. He notes that the original design calls for supporting multilingual sites, and by allowing multiple content body records per content item, he can support as many languages as needed.
Categories are stored in their own table.
Pages are stored in two tables. The first is the page definition, the second is a related table listing the categories to be included in the page.
Vijay also creates two tables for keywords. The ability to search for content is part of the specification, and this requires allowing either free-form searches or keyword searches. Vijay thinks that they need to support both, since keyword searches (using predefined keywords) is fast and accurate, but limited to the specified keywords. Free-form searching allows for any and all searches but is generally less accurate, and slower too. He creates one table to contain keywords and a second to store keyword-to-content relationships.
He prints copies of his database schema.