|
Database design methodology - III We have seen that the relationship between entities is primarily of a parent-child nature, wherein they can be further classified into two types--an identifying relationship where the child entity is meaningful only when it has a corresponding relationship with the parent entity, and a non-identifying relationship where an entity is meaningful even if the foreign key is null. Let us now take a further look at the other aspects governing the relationship between entities.
Evolve referential integrity requirements
When a relationship between entities is established, it has to be followed under all circumstances. There can be various actions that can take place on the parent or child side that can invalidate the relationship.
The actions are Insert, Update or Delete. And these actions can occur for the parent or child. Thus you have to define how these six events will be managed.
When a new child record is added, the restrictions that apply to the foreign key must be specified. Here the foreign key can be NULL--a valid reference to the parent table.
Consider this example:
Parent entity (Product):
Name Productid
Pencil 11
Eraser 13
Pen 14
Child entity (Order):
Orderno Productid Qty
22 11 232
23 11 100
24 13 5
Here are the six available actions.
Parent Insert: When you add a new record in the Product table, there is no additional checking required because it is guaranteed to have no child table records referring to it.
Parent Update: Here updating the name is no problem. But we are talking about the change in the Productid (primary key) itself. If you change the Productid of Pencil to, say, 20, the first two records in the child entity will become invalid. Here you have two choices, restrict such updates or apply the updated value to all affected child records. Restricting updates prevents attempts to change primary key values if there are any child records referring to it. Thus if we had changed the primary key of Pen, it would have been permissible. Updating all affected records with the new value is called an Update Cascade.
Parent Delete: If a parent record is deleted, the child record will be orphaned. Like with the updation rules, you can either perform a 'Delete Restrict' or 'Delete Cascade'. If the child table has a non-identifying relationship with the parent record, it is possible to set the value of the foreign key to NULL when the corresponding parent record gets deleted.
Child Insert: This action is restricted to ensure that the new record has a valid reference to an existing record in the master.
Child Update: This is also restricted to ensure that the updated value of the foreign key is a valid record in the parent table.
Child Delete: This action can be performed without any further checking as childdelete has no impact on the relationship. Restrict, Cascade and Set NULL are three actions possible. There can be another option called Set to Default. If a foreign key field is made empty, a default value can be specified to replace the NULL value.
No action: You may decide that you do not want to either restrict or cascade in response to the above events. However this is not generally recommended.
Appropriate usage of mapping tables
One of the common mistakes with data design is the lack of appropriate mapping tables. The basic principle is that if there is information stored in a database, the information should be self-explanatory.
Consider this table which stores information about employees contact details:
Field Purpose
Empid Employee id
Number Contact number
Type P = Phone, F = Fax, M = Mobile, E = Email
Now this design looks fine but there is a major flaw in it. Look at the implementation of this in the user interface.
Now look at the code (Visual FoxPro) which deciphers the option button and puts the actual value in the TYPE field.
m.curval = thisform.optiongroup1.value
do case
case m.curval = 1
m.type = 'P'
case m.curval = 2
m.type = 'F'
case m.curval = 3
m.type = 'M'
case m.curval = 4
m.type = 'I'
case m.curval = 5
m.type = 'E'
endcase
So what is the problem? The problem is that in order to understand that P is Phone and F is Fax, you either have to look at the documentation or read this code where the caption of each option button is programmatically mapped to the values in the data like P, F or E. This type of implicit mapping is very difficult to understand and maintain.
Another problem is that if a new type is added, you need to add one more option group, realign the form, recompile the EXE and redistribute it to all end users.
It would have been much simpler if we had added one more table which explains in English what each type means. The table would be as follows:
Type Typename
P Phone
F Fax
E Email
I Iridium
The form could be designed differently by using a combo box instead of hard-coded option buttons. The combo box, when mapped to the base-mapping table, does not require any changes even if more types were added to this table.
Create stored procedures with pseudocode
As discussed earlier, the business logic can be implemented in two different ways--within the database and outside the database using middle layer objects--a logical design of which is shown alongside.
|
