GNI - RdbConv2

RdbConv2

Representing tables from a relational database in ISIS. See RdbConv for representing ISIS data in a relational database.

a standard example

Assume the following textbook example of a very simplistic orders database:

CREATE TABLE Cust (
	custid number,
	name text
);

CREATE TABLE Order (
	orderid number,
	custid number,
	title text,
	price number
);

CREATE TABLE OrderPos (
	orderid number,
	pos number,
	itemid number,
	quantity number
);

the standard mapping

There is absolutely nothing wrong with mapping this one-to-one:

Create three ISIS "tables" (usually called databases), one for each RDB table.
Every record in the ISIS tables holds one record of the RDB table, and has one field (e.g. using tags 10,11,...) for every field of the RDB. Fields which are "NULL" in the RDB record are omitted in the ISIS record.
Set up the FSTs to make an index entry for every occurence of orderid and custid.

Writing the joins of customers to orders and orders to positions in the ISIS formatting language is not as convenient as in SQL, still executing them is as efficient as in an RDBMS.

resolving 1:n relations

Back in the old days, an order used to be "stored" on one piece of paper. A RDB is not as flexible, as it does not allow an entity like an order to have a variable number of attributes (although some modern RDBMS feature extensions towards that).
In ISIS it is much more natural to collapse the order together with all it's positions into one record:

to the order record, add a repeatable field "pos"
in every "pos" field use subfields p, i and q to hold position, itemid and quantity

That way you

access the complete order at once
save one table, one index and the storage to put the orderid on every pos
can even eliminate the pos number, since the order's "pos" fields will keep the order in which they where entered
save the need to put updates to a position and the order total price within some kind of transaction, since there is only one record affected

However, there are a couple of reasons to not put all the orders into the customers record in the same way:

since an order already is using a repeated field with subfields, it can not as easily be wrapped up into a single field
the customer record might become very large over time and the complete record would need to be updated for every new order

using MFNs

If you are starting your own new little business and expect the number of customers to not grow that big and/or the typical customer to stay for a long time, you might want to assign customer ids starting from one, so a customer's id is just the MFN of the cust record. (Maybe add some large number on printed orders :)
That way you can avoid the need for an index on custid. From every order you can directly access the customer record by MFN, which is extremely easy and efficient. (Most RDBs support some kind of "rowid" which is similarly efficient but usually does not survive a backup/restore and thus can only used in addition and with caution).

One might do the same to the orderids. However, there is one consideration: Order records will probably be deleted after a year or so, but if the order ids have to be ever increasing MFNs, the old unused MFNs will still consume up some space (typically 4 to 8 byte per MFN).

using variant records

Andrew Robert Giles-Peters proposed using variant records, as e.g. known from the PASCAL programming language:
Use only one ISIS table (database) holding all kinds of records. In every record use an additional switch field indicating the "type of record" (like the character at offset 6 in a MARC leader). Depending on this type is the actually allowed set of fields.

While this will not appear as a very natural approach to everybody (and switching on the type of record during data entry and validation is a little bit more complicated), there are some clear advantages:

the resulting database is self contained
if different types of records share some characteristics like having some "name", you can use the same field tag for those and print them using the same print format just as if they where objects derived from the same base class

$Id: RdbConv2.txt,v 1.1 2004/06/10 15:00:53 kripke Exp $