At 11:14 AM 1/7/2008, Lin MacDonald wrote:
I was going to add a new table and make one of it's keys a Foreign key. That's when I discovered there are no keys in my database. I don't know if I did that deliberately 15 years ago or if maybe it wasn't an option then, or I just didn't know any better, but instead of keys, I have multiple Indexes on various tables. So, what's the best way to handle this? Should I just add keys? Do I then discard the Indexes? Do I keep both? Will adding keys make any of my forms or reports do odd things?
Lin, Here's a quick lesson on INDEXes and KEYS (Constraints) in R:BASE: INDEXes: An index provides a pointer to the location of a column value in each row of a table, which allows R:BASE to search for rows of data much faster than searching rows sequentially. In general, R:BASE processes an operation that contains an indexed column faster than it processes one without. The R:BASE index is similar to an index in a book; both indexes allow you to find information faster. Instead of searching through a book page by page, you can look up the topic in the index and find the exact page number of the topic. Similarly, you can apply indexes to columns so that R:BASE finds data faster. When you apply an index to a column, R:BASE records the location of every value in that column. Then, when you look for or sort information in the column, R:BASE uses the index to find the rows you need quickly. For example, you want to list the bonuses that employee 102 earned; if the EmpID column in the SalesBonus table is indexed, R:BASE finds and searches that column faster. Indexes are most useful when you have tables with many rows. This means that by indexing the appropriate columns, you can speed up your applications. Processes that formerly took 20 minutes or more may be completed in only a few seconds. An index on an R:BASE column speeds up access to a row of data in much the same way that an index in a book speeds up access to a page. To understand how indexes increase processing speed, it's helpful to know how R:BASE stores a database. An R:BASE database consists of four files; each file has a different file extension - RB1, RB2, RB3, and RB4. File 1 contains the definition of the database structure; file 2 contains the data in the database; file 3 contains the indexes to the data stored in file 2, and file 4 contains the large binary object data (LOB). LOBs include graphic files or large text data. R:BASE also stores forms, reports, and labels as LOBs in File 4. When you include an indexed column in a WHERE clause, R:BASE uses the index in file 3 to find the location of each row in the table in file 2. Without an indexed column, R:BASE must sequentially search each row in that table in file 2 to find the data. By sorting an index list, you give R:BASE nearly instantaneous access to the specific index reference to a column name. An indexed column improves the performance of the following commands, clauses, or operations: · CREATE VIEW · DELETE DUPLICATES · INTERSECT · JOIN · Lookups in forms or reports · PROJECT · RULES · SELECT (when it includes a WHERE clause) · SUBTRACT · UNION · VIEWS · WHERE ... Additional References: From The Edge: http://www.razzak.com/fte/ 01. Topic: Indexing Explained Date: 06/20/2007 KEYS: One of the strengths of R:BASE is to provide automatic data integrity and referential integrity using constraints. In legacy versions of R:BASE, data integrity and referential integrity had to be enforced through rules and programming. Constraints are a part of the database structure and are automatically enforced. Types and Definitions: . Primary Key . Foreign Key . Not Null . Unique Key Complete details and additional references: From The Edge: http://www.razzak.com/fte/ 01. Topic: All About Constraints in R:BASE 7.5 and Turbo V-8 for Windows Date: 08/21/2006 02. Topic: Using Enhanced CASCADE Options in R:BASE Date: 05/21/2007 Hope that helps! Very Best R:egards, Razzak
