<com:TContent ID="body"> <h1>Database Design</h1> <p>The design steps we take in the sample application is a data centric approach. That is, we shall consider the application from the view point of data structures, data relationships and data integrity. Other designs starting from object and class design are also possible but is not the approach taken in this guide. </p> <h2>Entity Relationship</h2> <p>We start with the following entity relationship diagram. The bold text in each entity indicate required fields. The underline text indicates the entity's primary key, also denoted as <tt>PK</tt>. Entity relationships are indicated with arrows between entities and each relationship field is denoted with <tt>FK</tt>.</p> <p>The four entities: <tt>Project</tt>, <tt>Categories</tt>, <tt>TimeEntry</tt> and <tt>ProjectMembers</tt> makes up the time tracker application. While the other entities: <tt>Users</tt>, <tt>UserRoles</tt>, <tt>RoleTypes</tt> and <tt>Signon</tt> are usually found in applications where membership or user management is required.</p> <img src=<%~ db.png %> class="figure" /> </p> <div class="info"><b>Info:</b> See <a href="http://en.wikipedia.org/wiki/Database_normalization">Database Normalization</a> for a quick reference regarding database design and normalization, especially the 3rd normal form (3NF). </div> <h2>MySQL database</h2> <p>We shall concentrate on the MySQL database for this sample application. Other database engines are also possible and can be quite easily inter-changed. The SQL script to initialize the MySQL database is given below. The following SQL script was created using MySQL version 4.1.7. </p> <com:TTextHighlighter Language="sql" CssClass="source"> <%= file_get_contents('protected/data/time-tracker-mysql.sql') %> </com:TTextHighlighter> </com:TContent>