TurboDB Engine Documentation

Automatic Linking

Previous  Top  Next

Very often tables are linked the same way in all queries. E.g. items are linked to the invoice they belong to, authors are linked to the books they have written and so on. Therefore TurboDB allows you to specify different links from one table to other tables in the table itself.

Imagine you have an invoice table where the records contain the date of the invoice, the customer no, the invoice no and other invoice-related information. The items are in another table that has columns like article no, price, total amount and others. How do you link the item to the corresponding invoice it is part of? The traditional way is to have an additional column in the item table that designates the invoice no of the invoice the item belongs to. Every query that respects the invoice-item relation has to contain the following condition: ...where "ITEM.invoice no" = INVOICE.no...

What is it?

Even if you can still do this the traditional way with TurboDB, the preferred way of doing it is a little different. Rather than having an invoice no in the ITEM table you would use a pointer to the INVOICE table called link field. Because the default in TurboDB is to have a (unique) record id in every table the link column in the item table just holds the record id of the invoice it belongs to. Because the definition of the link column contains the information that the values in this column point to table INVOICE, the database now knows about this relation and will by default assume it in every query. This way of linking tables has some great advantages:

Doing queries with linked tables is easy because the system "knows" how the tables have to be linked. Queries can be much faster, because a record id is just a number while secondary keys often are much more complex. Changing indexes, column names or types does not affect the link at all. It is very easy to access the record of the master table with a special link notation.

You can look at link fields as an object-oriented way to work with database tables. They do not strictly conform to the relational paradigm but bring the feeling of pointers and references into the game. The item "knows" to which invoice it belongs. This link is given by the nature of things and will not probably change very often.

Another advantage is that link and relation fields need not display the purely technical AutoInc values to the user. If you assign an indication to the AutoInc column, link and relation columns will display this information instead of the numerical one. Here is an example:

CREATE TABLE DEPARTMENT (Name CHAR(20), Id AUTOINC(Name)

CREATE TABLE EMPLOYEE (LastName CHAR(40), Department LINK(DEPARTMENT))

The query

SELECT * FROM EMPLOYEE

will display a list of last names and department names, because the department name is defined as the indication for the AutoInc column.

How is it done?

While link columns introduce easy 1:n relations (one invoice has many items), this object-oriented concept makes as ask for a m:n relation i.e. a list of pointers in one table pointing to another table. TurboDB relation fields are the answer to this. A table containing a relation field to another table links every record to a number of records in the other table and vice versa. Taking again books and authors as example, inserting a relation column in the BOOK table would take care of the fact that a book can be written be more than one author and that one author might contribute to more than one book.

As you might suspect, relation fields are not so easy to implement as link fields. M:n relations have to be realized by an additional table in between that has one record for every link between the tables. This is exactly what Turbo Database does when you define a relation column for your table pointing for example to table AUTHOR. TurboDB will create a hidden intermediate table containing a link column to table AUTHOR and another link column to table BOOK. This is what you had to do if you worked in the traditional way. But with TurboDB the intermediate table is created and maintained automatically and transparently to you.

Compatibility Information

This feature is only partly supported in TurboDB Managed. TurboDB Managed currently supports link columns but not relation columns.