TyBase is all about simplifying data management. Whether it’s at home, in a small business environment, or in enterprise- people always have the need to collate, process, and format information. A traditional database system is often expensive and technically challenging to deploy- meaning that we often fall back to unsuitable techniques such as spreadsheets or text editors when we really need something a little more structured. TyBase provides that ‘something’.
The comprehensive visual editing tools offered by TyBase don’t attempt to force you into a particular style. You can start by designing structure, by entering data, or by designing user interfaces, then add features as you find the need. If you change your mind about something, you don’t have to rethink the whole project- just make the change. It’s a great way to prototype, and the end result can be professional-quality.
Getting Started
The best way to learn TyBase is to use it. Test-drive some of the included database templates to get an idea for what’s possible, then jump in and start building your own (or, if one of the existing templates suits your needs- just start using it and customise to your heart’s content.)
This online user manual seeks to provide a full explanation of every function in the program. It will provide the theory and clarify the details. It covers a lot of material, so we don’t recommend trying to take it all in until you’ve tried using the program for yourself.
The first thing to know is that TyBase works just like any other application on your computer- you open the app, create a new document, and get to work. Once you have something worth keeping, save it in your documents folder or in some other convenient location. A TyBase document is known as a “database”.
Databases
A database file is a collection of data, the rules and structure of that data, and the user interfaces which you create to present and edit the data. You can create as many databases as you want. You can open multiple databases simultaneously if you want to swap between them frequently. Each database may contain a single set of data, multiple sets of related data, or even multiple sets of unrelated data. It’s mostly down to personal preference, but there are two guidelines that you should consider:
1. A database cannot easily refer to data which is stored in a different database.
For a practical example of this- if you create one database containing a list of bank transactions, and another database containing a list of sales, it is not simple to directly link the two sets of data. If, however, you create them both in a single database, it is trivial to create a reference from one to the other. If you do happen to create two separate databases and later wish to merge them, just use copy-and-paste to merge the tables across.
2. Table names within a database must be unique.
This one is pretty simple. If you’re going to put two tables in the same database, they must have different names. This is common sense really, because how else are you going to tell them apart? Normally this won’t be a problem, but if you are going to merge two databases which have identically-named tables, it’s probably worth renaming one of the tables first.
Tables
Within a database, you may create any number of tables. At the simplest level, each table is like a simple spreadsheet- each row represents a specific thing (such as a ‘customer’, ‘transaction’ or ‘invoice’), and each column represents a specific piece of data describing the thing (such as ‘name’, ‘address’ or ‘description’.) Each column has a name which must be unique within the table. The intersection of a single column and a single row represents a single piece of data describing a single thing, and is called a “cell”.
More information about creating and editing tables can be found in the separate Table Editing Manual.
Forms
A “Form” presents a formatted view of a single row of a table. This allows the database author to expand the data out into a user-friendly layout, but also provides expanded information that is not possible in a single-row format such as showing related rows from other tables.
Editing a form takes place in a simple drag-and-drop style, with popup options to control sourcing of data and automatic layout functions. No programming knowledge is required to create a form.
Since a form is a layout rather than a data source, multiple forms can be created to reflect different views of the same data. This can be used to create variants for different tasks, or to create a print-friendly layout for customers which hides any in-house information and options.
More information about form editing can be found in the separate Form Editing Manual.
Calculations
Calculations within TyBase follow a common syntax, regardless of where they are used. Columns within the current table can be referenced by name (with some restrictions- column names containing spaces or special symbols will not work.) If the column is a reference, it should be followed with a period character (‘.’) and then the name of a column within the referenced table. This can be repeated as desired.
SalesInvoice.Customer.CustomerName
Mathematical operators work as might be expected on numeric data. Other operators (such as comparisons) common to many programming languages are also available. Numeric literals can be used as values with no formatting. String literals must be surrounded by the double-quote character and may use the backslash character to escape double-quotes within the string literal.
A detailed guide to using calculations is available in the separate Calculations Manual.
Scripts
Scripts allow the database author to provide custom application logic in the user interface. For example, a quote may provide a sale pricing for each line item which is automatically calculated based on the current cost of providing that item. Once the quote has been sent to the customer, these sale prices must remain fixed, regardless of any cost changes. A regular calculation column won’t provide that behaviour- it will continue to update as the costs change. What is needed is a mechanism to manually trigger the calculation at a time of the user’s choosing.
A multi-line script can be supplied for any form element. Within a script, each command ends with semicolon; the end of a line is only treated like a whitespace. The following commands are available:
- “set target = calculation;” – Perform a calculation and write the result into the specified target cell.
- “if (condition) command;” – Evaluate ‘condition’ as a boolean. If true, the specified command is performed.
- “if (condition) command; else command;” – Evaluate ‘condition’ as a boolean. If true, the first command is performed; otherwise the second command is performed.
- “for (table, condition) command;” – Evaluate ‘condition’ for each row of the specified table. Wherever the condition evaluates as true, the specified command is run for that row.
- “{ command; command; … }” – Wherever a command is expected, multiple command enclosed in brackets may be used instead. The closing bracket is not followed by a semicolon.
Shared Databases
TyBase includes an easy-to-configure database sharing system which is intended for use on your local network. While it may be possible to connect to a database over the internet, we don’t recommend trying this as it will generally be too slow. We expect to provide an official remote-access capability in a future update.
The first thing to understand about shared databases is that all sharing occurs within TyBase itself. You do not need to place the database file onto a shared folder.
The second thing to understand is that the owner of the shared database must be keep the document open in TyBase for other computers to be able to access it. If the owner closes the database, quits the TyBase application, or logs off the computer, all other users will be immediately disconnected. No real data loss will occur but the interruption of access may be inconvenient. If you’re sharing in an office environment, it may be simplest to open the database file on a server where it will not be closed by accident. (By “server” we mean any compatible computer that isn’t being accessed on a regular basis- no special hardware required.)
Optimisation
TyBase’s function syntax allows for some very extensive calculations. The time taken to perform the calculations (and thus the responsiveness of the database) depends on how many individual operations and cell lookups are required- much the same as if a human was calculating the result manually. Simple math is very fast. Queries which analyse an entire table are considerably slower, but still reasonably fast. Queries which analyse an entire table using calculation columns in that table which perform further queries on other tables can potentially be quite slow. That’s not to say that you can’t do this, but you do need to consider how much work you’re asking the app to do. The following tips can help you optimise slow calculations:
- Avoid nested unnecessary nested queries. A query which uses the results of other queries is going to be time-consuming. Don’t do this unless there are no faster options for the results that you need.
- When performing a query operation, such as ‘sum()’ or ‘count()’, conditions which can be evaluated quickly should be placed near the beginning of the expression. In many cases, this may allow TyBase to bypass later parts of the expression. For example, the calculation “count(Widgets, WidgetPrice > this.price && WidgetSaleCount > 0)” will quickly eliminate any cheaper widgets before performing any analysis of the sales history (which we assume to be a calculation rather than a stored value.) The same calculation expressed as “count(Widgets, WidgetSaleCount > 0 && WidgetPrice > this.price)” would potentially be a lot slower, because the expensive sales count calculation will be performed for every widget, and the pricing checked afterwards.