A Technical Introduction to Valentina

There are many database systems available for the developers. It gives them a lot of latitude to choose the one which would meet their needs in terms of cost, special features and specialty applications around them.

Yet so many of them are virtually all the same internally and that is the reason why it's rare to see any real innovation in databases - especially at the engine level. That's where Valentina is different. As a developer, you need to look underneath the surface of a pretty interface to make your application more responsive, useful, innovative and even scalable. The database that forms a critical piece of your solution should add value to make it more responsive, useful, innovative, scalable - and modern.

This technical introduction to Valentina examines moving from conception to implementation of a database system that works with an address book application. It also presents how following the Valentina way yields huge gains in performance and scalability. Examples rely on SQL so that they are more portable.

When you start designing your database structure, first of all you should determine what real world entities you want to describe in your database. Usually it is a good idea that each entity should be mirrored by a separate database table. Write down all such entities and their properties - sometimes a structure suggests itself if you see it diagrammed out. That gives you what is known as a logical database structure.

Let's look at a very simple database consisting of two tables Person and Phone - which mirrors two real world entities. Here is a first step example:

Person { FirstName, LastName, BirthDate }
Phone { Kind, AreaCode, NUMBER }

The second step in designing database structure is to determine relationships between the entities and the kind of relationships they can have. In database terms, these include: ONE to ONE, ONE to MANY or MANY to MANY. It is very easy actually, it`s enough to answer the question: how many entities of object A can be connected to object B?

For our example:

How many Phones can a Person have? The answer is MANY - work, home, mobile, fax and more.

How many Persons can be associated with a Phone? The answer is MANY.

Think about general phone numbers at specific office locations of companies, where everyone is on an exchange. You might want to be able to look up all the known contacts at a particular company by the phone number. These entities then, Person and Phone, must be linked together in a MANY to MANY relationship. In database notation, this is referred to as M:M.

After defining all entities (objects), their properties and all relations (links), you have the logical database structure - the logical way to connect all data. But it doesn't stop there, because next we have to develop a physical database structure or implementation.

There are many existing database models - Hierarchical, Network, Relational, Object-Relational and Object-Oriented. A DBMS usually is based only on one model, and if you understand the strengths of the model that you are using, you can maximize performance from the very beginning of the development.

Now that we know the logical database structure, we need to transform it to the physical world: this is the process of expressing objects, properties and links of the logical model into database structures.

The Relational model is familiar to anyone who has been involved in database development of almost any sort - MS Access, Filemaker, Oracle, MS SQL, Postgre SQL, mySQL - all tare relational databases.

Valentina supports the Relational model, but also the Extended Network and Object-Relational models. Why? It's all about performance as you scale your solution. If you take what may first appear to be the easiest and the simplest route based on pre-conceptions you have got from creating a database with a product like FileMaker, you may end up building within some severe limitations into your software.

Let's compare the above simple example with two tables to be expressed in a relational model and in comparison, the unique object-relational model of Valentina. We will show how much easier and natural work is with the modern Object-Relational (OR) model of Valentina.

The relational model suggests using tables which have columns and rows. If we did this the relational way, we could create this database with two tables in SQL:

CREATE TABLE Person(
FirstName VARCHAR(128),
LastName  VARCHAR(128),
BirthDate DATE )
 
CREATE TABLE Phone(
Kind      BYTE,
AreaCode  INTEGER,
NUMBER    INTEGER )

Next, we link records of the Person table to the records of Phone table. The relational model uses an additional table to contain links. So we need to add KEY fields into our tables at this point. A KEY field is a field that has a unique, NOT NULL value in the table scope.

Note: Relational key field links use a “pointer by value” method; in other words such fields keep the same value as a KEY field of the table at which they point.

So how do you pick a unique key for the Person table? Social security number? But what if you are not in the USA? A passport number? This is not such an easy task given that there is no universally common way to track people. Some DBMS offer an auto-increment integer field - it assigns a unique value into this field when you insert new records into the table - usually just adding a “1” to differentiate it from the last record. So let`s correct our tables in SQL and add in a primary key:

CREATE TABLE Person(
Person_ID  INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName  VARCHAR(128),
LastName   VARCHAR(128),
BirthDate  DATE )
 
CREATE TABLE Phone(
Phone_ID  INTEGER PRIMARY KEY AUTOINCREMENT,
Kind      BYTE,
AreaCode  INTEGER,
NUMBER    INTEGER )

This is a shame. The beauty of our logical structure begins to fade - our tables now have some “garbage” fields and values inserted into them in a rather illogical way by having these keys. My web developer friends liken this to the old style HTML which muddles up display logic with content versus nice clean content modified by external CSS.

Now let`s return to the M:M linking. We still have not solved this task, but only made some initial steps.

Now that we have ID fields in both tables, we can apply a classic Relational Model solution to get a M:M link between the two tables: we create a third helper table with two fields:

CREATE TABLE PersonPhone(
                Person_ptr INTEGER INDEXED,
                Phone_ptr  INTEGER INDEXED )

This table will contain information about links of Person records to Phone records. For example if Person with ID = 1 is linked to Phones with ID 2, 5, 77, this table will have three records:

Now let's solve the same task using the Valentina Object-Relational model. We are still using SQL to generate our database.

Valentina does not require the creation of KEY fields - you can avoid the headache of filling in unique values. Each Valentina Table has a special RecID field, which provides a unique number for a record of this table. Taking this into account we can make our tables as simple as:

CREATE TABLE Person(
    FirstName VARCHAR(128),
    LastName  VARCHAR(128),
    BirthDate DATE )
 
CREATE TABLE Phone(
    Kind      BYTE,
    AreaCode  INTEGER,
    NUMBER    INTEGER )

To establish the all important M:M link between these two tables, Valentina offers a very special feature: the BinaryLink.

CREATE BINARY LINK PersonPhone(Person, Phone) 
    AS MANY TO MANY

And there it is. We created links but we haven't introduced any garbage fields into our tables - and remarkably, it means that our physical structure more closely resembles our logical structure.

Okay, in the relational model we have additional tables and in OR-model we also have an additional object in schema - this BinaryLink. Is this some kind of a naming game? Not really, and this is where you see the benefits.

• Cleaner Schema. Using BinaryLinks, one Valentina developer told us that they were able to reduce an 800 table solution (using a traditional relational database) to about 500 tables. Your needs may not be this complex … today.

• Easier Transformation. The process of transforming your logical model into a physical implementation is much more straight forward.

• Smaller Consumption of Resources Versus Primary Keys. RecID fields do not consume any disk space. Zero bytes! In contracts, a ULONG primary key with associated index eats in average (4 + 8*1.5) = 16 bytes per record, i.e. 16Mb for each million records.

• Cut Fat Indexes in Half. At worst, a BinaryLink consumes less than a half of the disk space of adding a third table with indexes.

• JOINs are Faster When BinaryLinks are used to JOIN two tables, they are at a minimum 8 times faster than using a third table.

• SQL Queries are Simpler As a result SQL queries with JOIN are also much simpler.

Working with a RDBMS, you would have to write SQL query with JOIN by SQL Standard:

SELECT *
FROM Person JOIN PersonPhone ON Person.id = PersonPhone.person_ptr
            JOIN Phone       ON PersonPhone.phone_ptr = Phone.phone_id

If designed the Valentina way, you would execute the same query with Valentina:

SELECT *
FROM Person JOIN Phone