Storing Objects and Relationships
For the most part, storing objects is straightforward. The attributes of the object usually map to columns in a table. An object is stored as a row in a database table. Complications only arise if the attributes are complex in some way, say because they are objects. The trick then is to break the complicated attributes out into separate tables and link the tables together with keys The fun begins when we try to implement associations, which must be represented by relationships. Objects need to refer to each other. They usually do this
by keeping attributes that somehow reference other objects. When objects are just
held in the computer memory, quite often the programming language takes care
of most of the worries. When objects are stored in a database, the linkages need to
be more explicit. The designer then needs to invent special attributes to implement the links, and these will ultimately be the keys stored in database tables. 4 Database conferences used to be filled with many esoteric papers defining new normal forms and discussing the mathematics of normalization. Such is the stuff that academic dreams are made on but for most practical purposes these are not often used.
In a one-to-many association, one object can be linked to many objects. There is no easy way for the ‘one’ object to record the ‘many’ objects it is linked to. In a programming language, it would be possible to keep an array at the ‘one’ end of the relationship. In a database, it makes sense to record the relationship at the ‘many’ end. So the table used to store the object at the ‘many’ end has an extra column (or set of columns) added to store the key of the object at the ‘one’ end of the relationship.
Figure 14.3 shows two one-to-many associations. The Customer-Order associate is implemented by inventing a key (customer number) for the Customer and creating a Customer table as in Table 14.5. We would then store the customer number in the table that is used to store the Order class, as in Table 14.6, with a key to link back to the customer table The order table has also got its key, called order number MANY-TO-MANY RELATIONSHIPS Many to many relationships present a greater problem. To record the number of records linked to a table, some complicated data structure would be needed, and this is not permitted in a relational database. Therefore an additional table needs to be introduced. The additional table consists of pairings of the keys from the linked tables.
shop (eg. long-term illness), then the shop number is not valid. One solution is for a null value to be entered in the shop number in the employee record. The alternative is to create a separate table for the relationship, as in Table 14 11, and for the shop number from the employee table as in Table 14.12. Then if a salesperson is temporarily not allocated to a shop, the record matching shop with the removed employee is deleted, but the employee record remains
1435 ONE-TO-ONE RELATIONSHIPS
One-to-one relationships offer the broadest range of possibilities. The link can be made by putting the key of one object in the record and recording the other object. This can be done on either side of the one-to-one relationship.
143.6 DATABASE SCHEMAS
It is rather slow and tedious to describe a database by drawing out the table-struck rules. Thus we have a shorthand notation for describing the database structure. The structure of the database is known as a ‘schema’. We describe a database table by writing the table name and then listing in brackets the fields (or columns) in the table, separating the fields using commas. We then underline the key. A simple ordering database might then be described in Figure 14.5.
14.4 Querying – SQL
Although we shall not consider the programming language Metalwho in the construction phase, it is useful to understand a little about database query languages. For many years now, the Structured Query Language (SQL) has been a standard.
