Persistence Design

This is the section in which we actually come to the database part of the whole system design. This is the transformation phase from the classes to database tables. Here tables are designed taking into account the various data classes encountered in the discussion of the scenarios. The classes discussed in the scenarios may or may not be sufficient for designing of tables for a complete and consistent database. This may be due to improper or incomplete handling of the scenarios or because of scenarios, which are not clear. So in some cases it may be required to review some of the scenarios and make corrections as required and thus update the class definitions. It is better to represent different data classes as different tables, but contents of two classes may be combined to form a single table or contents of one class may be put in two tables as and when required. Any addition of tables without having base data class or merging or division of classes in tables must be explicitly stated.

List of all tables from the various class definitions must be given in this section in the following format:

[T# [Serial Number]/[Name]]

All the tables should be followed by a brief description of the purpose of the table. Cross-reference to the data classes to which they refer to should also be given.

For example:

[T# 1/PaymentDetails]\

Cross reference: [C# 2.a/clsPayment]

This table stores the details of payment made.

]]]

In the case of an object of a simple entity class (i.e., which contains no objects of another class), it is only necessary to persist (store) the properties as the methods are common to all objects of the class and the methods are a part of the executable software. We simple convert every property to a field with an appropriate datatype depending on the choice of the backend database.

For an object of a pure collection class, every individual object in the collection object needs to be stored separately. This will usually involve looping through the items of the collection. Please note that, usually, the objects in a pure collection object do not have any relationship among themselves and are just an unrelated aggregate.

For an object of a pure manager class, there are no properties to be stored and hence, no tables are required.

For container objects (i.e., objects which contain objects of other classes connected by a has-a relation), we must create a master-detail relation and should be stored in two different tables. For example, we have an invoice which has details of the objects sold. This will necessitate creation of two tables - one for the master object and another to contain the inner objects. The relation between the master object and the inner objects must be preserved using a unique identifier for the master object. This unique identifier will be the primary key in the master table and the foreign key in the details table.

In the absence of any unique property in the master object, we introduce an ID number (usually an auto incrementing long type number like the AutoNumber of MS-Access and SQL-Server). Such a scheme will be applied for the case of our invoice.

Invoice Table: InvoiceID AutoNumber (Primary key)

InvoiceDate Date

InvoicePartyID Long

InvoiceDetails Table: InvoiceDetailID AutoNumber

InvoiceID Long (Foreign key) Repeating

ItemID Long

Relationships (here we are talking of the "relations" of an RDBMS) are usually modeled as a separate entity classes. Usually these "relation" classes do not have a unique key and, therefore, require an ID field to act as an unique identifier.

Very often, we need to track the history of changes in the properties of an object. For example, a SalesItem object might have a Price property stored in a SalesPrice table. To track any changes in the Price over time, we will need to create a separate history table named PriceHistory which will contain a record of the time at which the change was made. Another example is a Salary table which stored the salary of each employee. To track salary changes, we need a separate table SalaryHistory in which we store information about the date of a salary change and the new salary. As a general practice, for every Master Table that we create, we must examine whether a corresponding history table is needed.

To create names of history tables, use the naming convention

[entity table name]+ "History".

 Using a View class

When an entity class contains a large number of properties (which correspond to a large number of columns in the entity storage table), we can consider the creation of a special class containing only partial information about the class. We will call this a "View" class for lack of a better name as it corresponds to a partial view of the table.

The objects of these classes should be used mainly in read-only fashion, i.e., no updates should be allowed in these objects. An update on a partial copy of an object could lead to an inconsistent object.

Under no circumstances should new instances of this view class be created at the user layer for storage. This will lead to inconsistent objects.

The view class must contain the primary key to facilitate retrieval of the whole object if and when required.

Corresponding collection classes must be defined.

The naming convention to be followed is to name this view class as OriginalClassVNN where NN is a number suffix.

For example, clsStudentV1 and clsStudentV1Set

These view classes can be used in the following cases:

To provide a list of alternatives for choice.

To display the results of a search.

In particular, when a search is expected to yield a large recordset, the use of a view class is highly recommended. For example, if we are searching for a student by name and/or roll number, it is sufficient to retrieve only the Roll Number, Last Name and First Name of the student. Once a selection is made from the search results set, an object of the complete entity class can be retrieved based on the key.

 DATA DICTIONARIES

[A brief one-line description about the field with typical example of values that can be taken by the field should be given]

[[[

1. Each Table must contain a serial number and the project module to which it belongs.
2. A brief description of the purpose of the table must be provided.
3. All the tables of a module should have a prefix to identify to which the table belongs, e.g., all table of EVENTDIARY module will have EN prefix.
4. The Table name must reflect its purpose. Therefore, names must be self-explanatory as far as possible but avoid creating very long names for this purpose. A balance is desired between the readability and the length of the name.
5. Avoid white spaces in Table Names as all backend DBMS systems do not support this naming convention.
6. The names of the principal designer(s) must be mentioned.
7. For each table it is necessary to mention whether it is a Master table or a Transaction table. This will be used in the stress testing phase of the software. Transaction tables will have to be tested against a much higher number of records.
8. The primary key or key combination for each table must be mentioned. If there is no key, mention NONE or NOT REQUIRED in this area.
9. Primary key fields must be shown in Bold.
10. Foreign keys must be shown in Bold + Italic. Foreign keys must have a tag showing the Table number in which they were originally defined. Details of fields appearing as foreign keys may be skipped as they appear in the original Table.
11. Each field must be given a serial number, a brief description and a name. In addition, the data type must be specified completely.
12. Redundant fields must be marked with the suffix {Redundant} and the nature of redundancy must be explained clearly. Redundancy linkages must be specified clearly.
13. Whether it is a "required" field or not must be specified.
14. Some typical values may be specified in special cases.
15. When the field can take only the values from a given set, the entire set of values must be specified.
16. Any special encoding scheme used in the value of the field must be explained in detail. For example, the last digit of an ISBN number used on books is a parity digit used to validate the number.
17. Any special Business Rules applicable to the fields must be specified here.
18. All fields of a table should preferably have the same prefix.