RDMS Database's and/or Record Managers
Hello Database coders,
You all know I wrote the tsunami record manager wrappers and I want to share
somethings and get some kind of feedback.
Tsunami is a record manager, not a Relational DataBase Management System
(RDBMS), just as Btrieve is not a RDBMS.
However, relating data with a record manager is not difficult and can actually
prove to be more advantageous at
times given the total flexibility provided by a record manager.
A general discussion of relational databases should probably begin with a set of
terms and definitions to be used
in the discussion. The terms defined here will be those relevant to using
Tsunami, but will contain references to
terms used by RDBMSs where appropriate.
DATABASE
A logically coherent, structured collection of related data with inherent
meaning, built for a certain application,
containing one or more tables. Some databases store all tables in a single
physical file. Tsunami uses a separate
file for each table.
FILE (TABLE)
A set of named columns and an arbitrary number of unnamed rows. In Tsunami, a
table is simply referred to as a file
containing records that are each optionally comprised of fields.
RECORD (ROW)
A single unit of data made up of structured, fielded information. Typically, all
records in a file will have exactly
the same structure, but this is optional when using Tsunami.
FIELD (COLUMN)
A homogenous section of a structured record. That is, a field is a segment of a
structured record that contains the
same "type" of data found in the same field of all records in that file.
KEY
The contents of one or more fields upon which data is indexed for fast record
retrieval.
The following simple illustration helps to visualize the above definitions. This
entire illustration represents a
table (file). This table and any other tables needed by an application would
make up the database. Each row of
information in this table represents a record and each column in the table
defines a field within each record.
In this example, the Emp_ID column would most likely be defined as a key, and
the combination of
Employee_Last_Name + Employee_First_Name might also be defined as a key.
+--------+----------------------+----------------------+---------+
| Emp_ID | Employee_Last_Name | Employee_First_Name | Dept_No |
+--------+----------------------+----------------------+---------+
| 000001 | SMITH | JOHN | 100 |
+--------+----------------------+----------------------+---------+
| 000002 | ADAMS | JAMES | 200 |
+--------+----------------------+----------------------+---------+
| 000003 | JONES | JANE | 100 |
+--------+----------------------+----------------------+---------+
A number of rules are typically enforced by an RDBMS. A record manager such as
Tsunami is actually a much lower
level tool than an RDBMS, giving you a more direct path to (and more control
over) your data. This can be very
powerful, but does require your application to do some of the work typically
done by an RDBMS, if you want rules
enforced.
For example, most RDBMSs will enforce a unique key rule so that each row
(record) is uniquely identifiable.
Tsunami does not require a unique key. Although you can certainly enforce unique
entries with Tsunami, all records
in a Tsunami file may be duplicates, if necessary. Also, an RDBMS will usually
mandate a fixed number of columns
(fields) for each row (record) in each table (file). Tsunami does not require
structured records of fixed length.
Fielded data is usually a good idea, but is optional for Tsunami records.
Given the "loose" nature of a record manager when compared to an RDBMS, some
will see relating files with a tool
like Tsunami as intimidating, while others will see it as liberating. It will
often depend on your level of
experience with databases and the level of control you wish to retain. In
reality, a record manager like Tsunami
could easily be used as the core engine behind an RDBMS.
Relating Files
--------------
In most relational databases, relations between tables are normally managed by
the RDBMS directly and integrity of
those relations is also handled by the RDBMS. You can easily accomplish the same
results with a record manager.
This is best explained by example...
Our extremely simple database example will consist of three files...
Customer File
+---------+----------------------+----------------------+
| Acct_No | Customer_Last_Name | Customer_First_Name |
+---------+----------------------+----------------------+
| 0001001 | WILLIAMS | SANDRA |
+---------+----------------------+----------------------+
| 0001002 | KLEIN | TERRENCE |
+---------+----------------------+----------------------+
| 0001003 | EVANS | THOMAS |
+---------+----------------------+----------------------+
Invoice File
+---------+---------+-------------+-------------+
| Invc_No | Acct_No | Orig_Amt | Balance_Due |
+---------+---------+-------------+-------------+
| 0050001 | 0001002 | 400.00 | 250.00 |
+---------+---------+-------------+-------------+
| 0050002 | 0001002 | 250.00 | 250.00 |
+---------+---------+-------------+-------------+
| 0050003 | 0001001 | 500.00 | 300.00 |
+---------+---------+-------------+-------------+
Payment File
+---------+---------+-------------+-------------+
| Invc_No | Acct_No | Payment_Amt | Pymnt_Date |
+---------+---------+-------------+-------------+
| 0050001 | 0001002 | 50.00 | 2001-10-14 |
+---------+---------+-------------+-------------+
| 0050001 | 0001002 | 100.00 | 2001-11-16 |
+---------+---------+-------------+-------------+
| 0050003 | 0001001 | 200.00 | 2001-10-22 |
+---------+---------+-------------+-------------+
The numbers in bold represent fields that have been defined as keys. Other
fields in this example might also be
defined as keys for fast record retrieval, but for the purposes of this example
we'll only index on the Acct_No
and Invc_No fields that are being used to relate these sample data files.
When a customer is inserted into the Customer File, they receive a unique
identification number, or account number.
Every invoice created for that customer will have a unique invoice number
assigned to it, and the customer's account
number will also be included in the invoice record. Each payment made against
that customer's open invoices will
produce a record that's inserted in the Payment File. A payment record would
include the invoice number and the
customer's account number.
Setting up your files in this manner allows you to "relate" records from the
different files based on the contents
of the Invc_No and Acct_No key fields and could then be used to produce customer
statements as follows...
Customer Statements
Read records from the Customer File...
Use the Acct_No to read the Invoice File...
Use the Invc_No to read the Payment File...
Print all current invoices and payments on a statement, calculating a new total
balance due.
Your code is responsible for enforcing integrity rules in this example, such
as...
An invoice record should never contain an Acct_No that doesn't exist in the
Customer File.
A payment record should never contain an Invc_No that doesn't exist in the
Invoice File.
A payment record should never contain an Acct_No that doesn't exist in the
Customer File.
A user should never be allowed to delete a record from the Customer File if it's
Acct_No exists in the Invoice
or Payments files.
A user should never be allowed to delete a record from the Invoice File if it's
Invc_No exists in the Payments
File.
I'm sure most of you will agree, handling relational integrity becomes a very
intuitive thing when coding a
database application. It's no more difficult or unusual than enforcing data
entry rules for on-screen record
creation... when the user clicks "Save", you make sure all the required fields
have been entered and that
certain fields contain the proper types of information. The same applies to
inserting/deleting records in a
database... you simply make sure a few rules are satisfied before allowing the
transaction.
As a rule, Granted, it requires more coding on your part, but no SQL engine in
the world, no matter how well it's optimized,
could ever consistently produce the kind of performance that custom written,
in-code queries can.
Let me know what you think....
Euman
|
Not Categorized, Please Help
|
|
