blob: a306ba2ce0a3a43fa7f5de68697a8db3d4cc6714 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
<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>
|
