|
Database design methodology - IV At this stage of the design process, we are in a position to choose the back-end data engine. The choice depends on the server validation requirements, security requirements, data volume versus performance requirements, growth estimates, cost implications and other parameters. If your application is a packaged product which needs to run on multiple data engines, this process will shortlist all the RDBMS packages which need to be compliant.
Evolve datatypes
By this time we have enough information about the data requirements of the system being designed. Now is the time to think about the actual implementation of the database.
Datatypes will be chosen on the basis of the type of information to be stored. Datatypes must be chosen carefully. Do not try to put more than one meaning into any of the controls. Each field must contain only one kind of information.
Typical cases where one field provides more than one type of information, as given below, should always be avoided.
For a 12-character code field like this, the meaning depends upon the position of the characters.
1-3
4-8
9
10-12
Area
District
Type
Code
Large binary fields should be used with care as these occupy a lot of space and increase the complexity of coding.
Evolve constraints and rules
The constraints and rules that were documented earlier should now be applied using various domain constraint features of the data engine. Constraints can be simple user-defined datatypes, simple checks based upon a fixed list or more complex logic implemented as a trigger.
One important issue to consider while managing data constraints is their usage in the user interface.
Impact of constraints on the user interface: Let us take an example of a database level constraint and its user interface implementation.
Consider a field called Discount which is an integer type. Here the constraint says that the discount can only be in the range of 0 to 10 (%). Thus the following Insert statement:
Insert into Transact (Tranid, Amount, Discount)
values ("3b43", 3400, 12)
will fail with a constraint violation failure.
However, look at the user interface of this data. Here the textboxes are bound to temporary variables in memory till you click on the Save button. The Insert statement is generated in the code that executes in the Click event of the Save button. Therefore, even if the user has entered an invalid discount amount, there is no indication regarding this while exiting the Discount field.
In a complex form, there will be many such constraints. If all are checked only at the time of committing the transaction, it is still technically acceptable. But from an end-user perspective, it is too late. Users need to be told about their mistakes immediately.
If you want to include a dialog about invalid input immediately after the user moves to another field from the Discount field, the code needs to be written in the Losefocus (VB) or Valid (VFP) event of the textbox.
The problem here is that the constraint validation code is now being written again in another language and is outside the data engine.
This leads to maintenance related issues because, if the constraint changes from 0-10 to, say, 0-12, you will have to change the constraint not only in the database but also in all Losefocus events throughout all applications which refer to this table. This can become very cumbersome to maintain for all the business validations.
This is where the middle layer or business logic comes into the picture. Here the user interface calls a server-based object that performs
this validation. This validation is not performed using the data engine. In addition, you can also encapsulate the Insert statement using another component or method of an existing component.
If you follow this methodology, it is important to ensure that all modifications to the data occur only through the component layer and never directly, as there is no constraint defined at the data engine level.
Technically the components run at a higher privilege with the RDBMS in order to perform data modifications. End-users do not have any direct access to data. End-users must manipulate and view data through the business layer. This functionality provides for good security and centralised business logic processing.
This leads us to a different type of data and business logic design as compared to the traditional method.
This is what the traditional design looks like. For each new application, a new database is created and tables are added to it. At the end of many such development cycles, you have probably the same data engine but multiple databases that do not have any linkages across each other. However, the business logic required for managing day-to-day rules and processes often overlaps across application areas.
This approach makes it difficult to create a unified object model for creating the middle business object layer for further application development.
The aim should be reach as near as possible to the following situation:
Here we have a unified view of the data required by the organisation. Business rules are available as middle layer and the user interface does not need to contain validation code.
Create indexes for keys
Primary and foreign keys are typically indexed as they are used for most searches and joins. Therefore, it is important to have indexes on them. Clustered indexes are faster. There can be only one clustered index for each table. A clustered index is typically used for the most commonly used search field.
|
