Matricsoft - software development
products > Quickdb >relations
description
database creation
table creation
record creation
record retrieval
record operations
file operations
database operations
definition operations
exceptions
relations
header file

Relations

Quickdb is a relational database engine. Therefore, you can create relationships between tables.

What is a relation?

Let's consider we create two tables: company and employee.
An employee can work in a company and a company has employees. There is a relation between an employee and a company. In a database, you would define a special field in an employee record that defines which company the employee works for.

How to define relations in quickdb?

db mydb("jobs");

//first create the table company
mydb.new_table();
mydb.add_field("name", db_string);
mydb.add_field("address", db_string);
mydb.commit_table("company");

//we create the table employee,
//and we define a field as a relation...
mydb.new_table();
mydb.add_field("name", db_string);
mydb.add_field("surname", db_string);
mydb.add_field("IDcompany", "company");

//we can now start to fill-in data
//in this case, it is mandatory for each employee to work for a company

mydb.new_record("company");
mydb.set_field("name", "matricsoft");
mydb.set_field("address", "somwhere on the internet");
int record_number_matricsoft=mydb.commit_record();

mydb.new_record("company");
mydb.set_field("name", "supermarket");
mydb.set_field("address", "somewhere downtown");
int record_number_supermarket=mydb.commit_record();

//we create an employee in matricsoft and and employee in supermarket:
mydb.new_record("employee");
mydb.set_field("name", "john");
mydb.set_field("surname", "doe");
mydb.set_field_ref("IDcompany", record_number_matrcisoft);

mydb.new_record("employee");
mydb.set_field("name", "another");
mydb.set_field("surname", "dude");
mydb.set_field("IDcompany", record_number_supermarket);
mydb.commit_record();

How to retrieve the child items of a record

The table employee has a relation with the table company. We can retrieve the items of the table employee that are related with a particular record of the table company:

int nb_children=mydb.get_number_children("company", record_number_matricsoft, "employee");
for (int i=0;i<nb_children;i++)
{
    int employee_number=mydb.get_child("company", record_number_matricsoft, "employee", i);
    printf(mydb.get_field_string("employee", employee_number, "name").c_srt());
}

Notes and comments

There are important comments to make:

  • If you delete a record with children (let's say a company), all the children (of the table employee) that are related to this record will be deleted as well. If this is not what you want, you shall verify that a given record (of the table company) does not have any children...
  • If you delete a record which has a parent (let's say an employee), then the father table deletes from its memory that particular child (this way, you are sure that all the children that you get from a father table exist).
  • You can, of course have several fields "refering" to another table (for instance, an employee can work for a company, and can live in a given city, or work in a given building...).
  • When you save the database to a file, the reindexation is automatic (for instance, when you delete items a the database, the records get the status of "non exiting" and the other records do not get new indexes). For instance, if you created record 0 and record 1 for the table employee, if you delete the record 0, there will still be a record, with an index of 1.
  • If you wish to make a non-mandatory relation between tables, then you will need to create another table which may then have the correct relationship. It is easier to understand with an example:
//let's say you have the following tables:
//company
//employee (works for a company)
//boss (an employee can be a boss or a simple employee)

//definition of a company
mydb.new_table();
mydb.add_field("name");
mydb.commit_table("company");

//definition of an employee (works for a company)
mydb.new_table();
mydb.add_field("name");
mydb.add_field_ref("IDcompany", "company");
mydb.commit_table();

//definition of a boss (it is just to define a relation
//that may exist
mydb.new_table();
mydb.add_field_ref("IDemployee", "employee");
mydb.commit_table();

//creation of a company
mydb.new_record("company");
mydb.set_field("name", "matricsoft");
mydb.commit_record(); //returns 0, first record...

//creation of a normal employee
mydb.new_record("employee");
mydb.set_field_string("name", "john doe");
mydb.set_field_ref("IDcompany", 0);//works for matricsoft
mydb.commit_record();//returns 0

//creation of an employee which is a boss
mydb.new_record("employee");
mydb.set_field("name", "another dude");
mydb.set_field_ref("IDcompany", 0);//works for matricsoft
mydb.commit_record();//returns 1

mydb.new_record("boss");
mydb.set_field_ref("IDemployee", 1);//the employee 1 is a boss
mydb.commit_record();

//now if you want to know which employee is a boss, 
//you can get the children of all the records of the boss table:
int nb_records_boss=mydb.get_number_records("boss");
for (int i=0;i<nb_records_boss;i++)
{
    if (mydb.record_exist("boss", i))
    {
        int nb_children=mydb.get_number_children("boss", i, "employee");
        for (int j=0;j<nb_children;j++)
        {
            int employee_index=mydb.get_child("boss", i, "employee", j);
            //this employee is a boss:
            printf(mydb.get_field_string("employee", employee_index, "name").c_str());
        }
    }   
}