This five minute technical introduction to Valentina examines moving from conception to implementation of a database system, and how following the Valentina way yields huge gains in performance and scalability. Examples rely on SQL so that they are more portable.

There are many, many database systems available for developers - developers have a lot of latitude to choose one that meets their needs in terms of cost, special features, licensing terms and specialty applications around them. Yet so many of them are virtually the same internally and because of that, 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 scalable. The database that forms a critical piece of your solution should add value to your solution.

Conceptualize Your Logical Database Structure

Before you implement your logical database structure, you need to determine what real world entities you need describe in your database. Usually it is a good idea that each entity is mirrored by a separate database table. Write down all such entities and their properties - sometimes a structure suggests itself if you see it diagramed out. This gives you what is known as a logical database structure.

First, let’s look at a very simple database consisting of two tables Person and Phone - which mirror two real world entities.

Lets conceptualize these two entities with the their defining characteristics

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

Next, determinate the relationships between 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, enough to answer the question: how many entities of object A can be connected to object B?

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 known contacts at a particular company by 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.

Select a Database Model

After you defined 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, based on a database model.

There are many existing database models - Hierarchical, Network, Relational, Object-Relational and Object-Oriented. Database Management Systems make use of only one model because they are not designed to support multiple models.

If you understand the strengths of the model that you are using, you can maximize performance based on the strengths of that model. Valentina lets you choose which database model you implement.

Build Your Physical Database Structure

Now that we know what the logical database structure should look like, 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 - Access and Filemaker are both 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 simplest route based on pre-conceptions you have from creating a database with a product like FileMaker, you may end up building in some severe limitations into your software.

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

Building Your Database the Relational Way

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 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 will keep the same value as a KEY field of the table to 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 USA? A passport number? This is not such an easy task given that there really isn’t a 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 lets 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 is beginning 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 old style HTML which muddles up display logic with content versus nice clean content modified by external CSS.

Now lets 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 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:

That is, every conceivable link between combinations must be taken into account. As your database grows in size, this table can grow enormously.

Building Your Database the Valentina Way

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 our physical structure more closely resembles our logical structure.

Traditional Relational vs Valentina Object-Relational

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

Top Five Benefits of the Valentina DB Object-Relational Model

• 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 and it follows conventions of separating what should be logic from content.
• Smaller Consumption of Resources. Versus Primary Keys. RecID fields do not consume any disk space. Compare this with a primary key with associated indexes.
• Cut Fat Indexes in Half. At worst, a BinaryLink consumes less than half the disk space of adding a third table with indexes.
• JOINs are Faster and Easier. When BinaryLinks are used to JOIN two tables, they are at a minimum, 8 times faster than using a third table. SQL queries with JOIN are also much simpler as a result.

A Final Quick Comparision of SQL SELECT Queries

Compare SQL queries you would have to write with JOIN for MS SQL Server or mySQL:

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

If you designed your database using the relational way with Valentina, you could simplify it to:

SELECT *
FROM Person JOIN PersonPhone JOIN Phone

If designed with the native Valentina Object-Relational way, you would execute the same query with Valentina:

SELECT *
FROM Person JOIN Phone

With Valentina, you can choose just how verbose your SQL queries are.

Your next steps would be to establish in your application logic how to connect the tables, that are then written into your Valentina database.