1. 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
2. Re: RDMS Database's and/or Record Managers
From: "Ray Smith" <smithr at ix.net.au>
To: <EUforum at topica.com>
Subject: RE: RDMS Database's and/or Record Managers
> Euman wrote:
> > You all know I wrote the tsunami record manager wrappers and I want to
> > share somethings and get some kind of feedback.
>
> Without spending much time responding I'll reply with the following
> points.
>
> I feel like youre trying to convince people that record managers
> are a powerful tool (which is fine)
Yes, Tsunami is very capable and powerfull
> but I dont believe you are giving people the full picture of what RDBMS can
> do.
Why would i? Have you even given tsunami a look Ray?
> Databases also contain mechanisms for:
> * security,=20
> * external access to the data through 3rd party tools (like ODBC),
> * powerful backup and restore features,
> * stores procedures and triggers,=20
> * referential integrity,
> * transaction support,
> * views,
> * some provide data replication and distribution,
> * and probably more things that I've never used (I'm not a big RDBMS=20
> user)
Just how much easier is this than the tsunami wrappers?
1) to learn
2) for speed
3) for security (any encryption routine will do, there are several in the RDS
archive)
*(Tsunami Pro has built-in encryption)
4) total control of your data
> The Pros for RDBMS
>
> A good RDBMS is "almost" always required for large and complex
> projects.
Is this true? Can you see anything written useing euphoria's speed for large
projects anyway?
<snip>
no comment
</snip>
> Regards,
>
> Ray Smith
> http://rays-web.com
Euman
3. Re: RDMS Database's and/or Record Managers
Hey Ray,
I think you might have missed what I was after.
RDBMS and / or Record Manager
then I gave what tsunami is "in a nut shell" and welcomed comments.
I wasnt suggesting this is better than that or whatever (well to an extent).
I appreciate your comments and welcome you to give the tsunami
wrapper a try if just to take a quick look..
I personally think that those using EDS that need more power should
try tsunami, those who need multi-user support, multiple key search's
from within each record (if needed), speed, recovery, rebuilds, only
38 commands to learn and full control over any type data, etc...
Thanks Ray,
Euman
----- Original Message -----
From: "Ray Smith" <smithr at ix.net.au>
To: <EUforum at topica.com>
Sent: Wednesday, January 21, 2004 11:11 PM
Subject: RE: RDMS Database's and/or Record Managers
> Euman wrote:
> > From: "Ray Smith" <smithr at ix.net.au>
>
> [snip]
>
> > > but I dont believe you are giving people the full picture of what RDBMS
> > > can do.
> >
> > Why would i?
>
> Because you are giving an overview of Databases vs Record Managers
> and then saying how good record managers are.
>
> >Have you even given tsunami a look Ray?
>
> I looked at the tsunami record manager in the past but not since you
> created your eu wrappers.
> I'm not saying the tsunami record manager is bad in anyway at all.
>
> > Just how much easier is this than the tsunami wrappers?
> > 1) to learn
> > 2) for speed
> > 3) for security (any encryption routine will do, there are several in
> > the RDS archive)
> > *(Tsunami Pro has built-in encryption)
> > 4) total control of your data
>
> I listed (and hence agree) that there is more work in setting up and
> mainting a RDBMS.
> >From a programming point of view SQL can give you alot of flexibility
> and power that record managers don't give you. And yes, it will take
> more time for a developer to learn these things but that time should
> be recoped in the long term by the efficiency of these powerful
> tools and technologies.
>
> > > The Pros for RDBMS
> > >
> > > A good RDBMS is "almost" always required for large and complex
> > > projects.
> >
> > Is this true?
>
> I beleive it's true.
>
> >Can you see anything written useing euphoria's speed for large projects
> >anyway?
>
> I agree, I personally don't think Euphoria can handle large complex
> systems.
> That doesn't mean that I wouldn't use a database for a Euphoria
> project though ;)
> Just like in some cases record managers "could" be used in some large
> complex systems.
>
> Every case needs to be reviewed and a decision made based on the
> features required.
>
> The only reason I replied in the first place was that "I" thought
> you gave a one sided view.
>
> Ray Smith
> http://rays-web.com
4. Re: RDMS Database's and/or Record Managers
----- Original Message -----
From: "Matt Lewis" <matthewwalkerlewis at yahoo.com>
To: <EUforum at topica.com>
Subject: RE: RDMS Database's and/or Record Managers
>
>
> > From: Euman
> >
> > Just how much easier is this than the tsunami wrappers?
> > 1) to learn
> > 2) for speed
> > 3) for security (any encryption routine will do, there are
> > several in the RDS archive)
> > *(Tsunami Pro has built-in encryption)
>
> I doubt this is what Ray meant by 'security' (because it's not what I'd
> mean if I mentioned RDBMS security :). Commercial RDMBS (Oracle, SQL
> Server, etc) allow administrators to set up users and grant permissions
> to them based on activity (SELECT, UPDATE, DELETE, etc) and by table.
Wouldnt this be the same or similar to writting custom in-code queries Matt?
You are only limited by your imagination and experience using Tsunami.
I sure would like for you to look at tsunami and make suggestions ;)
I can see that I need to complete the demo section of the wrapper docs an add
a snippet that will allow certain users, certain rights to certain data. I can
see that
a seperate table would be needed for this which would be the same for those
commercial grade RDBMS. I guess what Im saying is that I dont see need based
on my limited knowledge of DB's to have a bloated RDBMS unless perhaps its
a very large project
Also, a good idea would be to write a GUI that list users and rights.
I suppose that would be likened to having database normalization and planning
ahead.
Matt, please let me know what Im missing here..
> I suppose you could liken an RDBMS to a 'data operating system.'
>
> Matt Lewis
