This topic discusses the optimum design of table/column definitions, particularly those derived from file/data definitions. It deals with a number of areas where Reality and SQL data structures are different, resulting in an inefficient SQL catalog when converted from a Reality database, and describes a number of ways in which the SQL catalog can be modified in order to produce an effective data model.
Techniques are described for:
Handling Multivalues and Subvalues.
Choosing Data Types.
Choosing Primary Keys.
SQL is designed to work with a relational database using data structures designed to reduce data duplication and inconsistent data dependencies. The process of designing an SQL catalog in this way is called 'normalisation'.
Reality and SQL data structures are significantly different in this respect. The major difference is that SQL is designed to work efficiently with a normalised relational database, whereas Reality is much more flexible and can be efficient and useful even when the data is non-normalised.
SQL is much more strict in specifying the type of data that can be entered into a column. An SQL column definition defines precisely both the type and size of data that can be entered, whereas a Reality data definition only defines the display width of the data, and is completely flexible with respect to the type.
The problem is that the direct conversion of an adequately efficient Reality database, using SQLM or WinSQLM, may result in an inefficient and cumbersome SQL catalog. It then becomes necessary to change the structures of the newly converted table definitions in order to create an effective SQL data model.
The automatic process run by SQLM and WinSQLM uses a “best fit” technique to generate SQL column and index definitions from existing Reality data and index definitions. It is reasonably accurate in simple cases, but cannot be guaranteed to generate an efficient SQL table in all circumstances.
In particular, it is recommended that you check the column definitions of the newly created, table that: