|
|
|
|
|
|
Database Design
- Data Modelling
|
|
The entire database needs to be designed using data modeling tools like the one that we are using
from:
xCase
and just to give you an idea here is how a model with 100 tables
may look like it is just a snippet of the entire model. See below image. Each rectangular represents the name of table and
if you right click on that using the data modeling tool, you will
get the field's names, relations and business rules needed to maintain the data base.
Not going through the database design steps and a complete specs
preparation may double the cost of your application for the long
run. Many mistakes could also be avoided if starting with the
database design process at first.
The database that we are using in most of our development is
MicroSoft, MSSQL database server. |
Prevent data redundancy
Database that was not design properly can cause
a duplicated data in the application. In this case when you enter data
somewhere you need to save it in several other places in order for
the system to work properly. Due to this problem, later, when you
need to modify some data you will need to process all other tables
where the same data is stored.
The above is a major flaw in the design and it breaks the basic
rule that is needed to prevent data redundancy. In a normal
design you enter the data in one place and use that data all
over. Data stored only once guarantee consistency all over the
application and this way one change of data could be reflected
anywhere in the application. Redundant data will cause a lot of
UN predicted issues. Database design using data modeling tools
will prevent and will optimize the entire development while
saving money to the client. In addition for future changes it
will be easier to have a global view of the entire database and
implement new changes quite rapidly. |
Database Normalization Basics
If you've been working with databases for a while, chances are you've heard the term normalization.
Normalization is often brushed aside as a luxury that only academics have time for. However, knowing
the principles of normalization and applying them to your daily database design tasks could drastically
improve the performance of your DBMS.
Normalization is the process of efficiently organizing data in a database. There are two goals of the
normalization process: eliminating redundant data (for example, storing the same data in more than one table)
and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy
goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
|
The Normal Forms
The database community has developed a series of guidelines for ensuring that databases are normalized.
These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred
to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll
often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen.
It is important to point out that they are guidelines only. Occasionally, it becomes necessary to stray
from them to meet practical business requirements. However, when variations take place, it's extremely
important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies.
|
|
Below is a snippet from the 100 tables model. |
Each dotted line between one table to another represents a relation
between each table. If you observe
the Students table in brown color then the Sat1 is a child of that
table. In a database design relations
between tables are part of the actual design.
|
|
Figure 1:
|
 |
Specs Preparation
Without specs preparation and analysis of the requirements needed it
is NOT feasible to assess the scope of work needed for a total
re-write of an application if it is needed. The above will include
the database design as shown in figure 1 and figure 2 and will be
used for the entire new development process. At the end of the
design the data modeling tool will create the entire data base with
all relations captions fields names. When ever a change is occurred
it will be implemented via the our
data modeling tool.
When you design a database while using a data modeling tool, you can
also provide notes of the business
rules and formulas as you discuss the specs with the client and
enter all in the entity which represents
either the table or view.
|
|
|
|
|
|
|
|
