- Mapping Models to Tables
- An Example
An example illustrates use of the mapping rules. Figure 6 shows an excerpt of the airline model from Article 3.
Figure 6 Model for excerpt of airline example.
Figure 7 shows RDBMS tables that implement the model, based on our recommended rules.
Figure 8 shows Oracle SQL code that I wrote according to the model. This code is typical of what you should expect to receive from a tool. The uppercase-lowercase distinction is not significant for SQL, but is intended to increase readability; uppercase denotes keywords, and lowercase denotes names of constructs. The unique clause is the SQL notation for enforcing a candidate key.
Figure 8 SQL code for Figure 6.
The FrequentFlyerAccount table has an implicit index created by the primary key constraint. The data types and lengths are arbitrary; for example, I assumed that 20 characters would be sufficient for the account number. I also assumed that the account number might contain characters, so I used the Oracle string data type (varchar2). FrequentFlyerAccountID is not null because it is the primary key; I assumed that accountNumber and accountStartDate were also required.
The Activity table has a foreign key of frequentFlyerAccountID that is not null. The discriminator activityType is also not null, and the constraint ensures that it is one of two possible values: FlightActivity or OtherActivity. The on delete cascade clause is an advanced SQL feature; on deletion of a FrequentFlyerAccount record, all corresponding Activity records are automatically deleted.
The FlightActivity and OtherActivity tables reference the primary key of Activity. If an Activity record that is a flight activity is deleted, the corresponding FlightActivity record is automatically deleted. Similarly, if an Activity record that is another activity is deleted, the corresponding OtherActivity record is automatically deleted. OtherActivity also has a foreign key reference to Company. The no action clause prevents the deletion of a Company that is referenced by an OtherActivity. (Actually, Oracle deviates from the SQL standard and does not support the no action clause, but omitting the clause still provides the same behavior.)
The unique constraint for the Company table enforces the candidate key on name.
You can use the airline example to check the quality of code generated by your database design tools. Make sure that the tools generate the proper indexes and foreign-key commands. In my applications I have seen little (if any) performance degradation from an RDBMS enforcing foreign-key statements.