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.
Reverse Database Engineering can expedite your database design by far for tracking down the Foreign Key relationships, Primary Keys and all indexes, stored procedures, Triggers and Views right into your data modelling tool.
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.
In relational database design, the process of organizing data and prevent redundancy is called normalization. The idea is to enter the data only once and use the same data all over the system. In the process of storing data in efficient way, the idea is to eliminate redundancy. The design of tables in database will be in one of the five possible normal forms. Each normal form contains and enforces the rules of the previous form. The first 3 forms are the most popular ones.
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.
We perform system analysis, software testing - quality assurance and data modeling. We provide custom made reports into your database such as: MS Sql Server, Oracle, Ms-Access and Visual FoxPro and others.
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 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.