Database Structure

Introduction

Creating a database is a task when it comes to managing data effectively and securely. It plays a role in maintaining the accuracy, coherence and dependability of information. In this guide we will explore the intricacies of designing an SQL database with special emphasis, on a two tier database structure known as the Pre Database and Semi Database.

Pre-Database Structure

We utilize a database to store the unprocessed information obtained from a particular source specifically passport data in this scenario. The purpose of this database is to serve as an initial repository for the extracted data before any verification or processing occurs, within the pre-database structure, we have an algorithm, explained here, to choose the nearest Entrypoint for verification. Generally this raw data will include identifiers from the source, which will be utilized as primary keys, in our final database.

In the case of passport data, the structure might look like the following:

CREATE TABLE Pre_Passport (
    Passport_ID VARCHAR(255) PRIMARY KEY,
    Name VARCHAR(255),
    Surname VARCHAR(255),
    Date_of_Birth DATE,
    Place_of_Birth VARCHAR(255),
    Nationality VARCHAR(255),
    Sex VARCHAR(1),
    Date_of_Issue DATE,
    Date_of_Expiry DATE,
    Issuing_Authority VARCHAR(255)
);

In this structure:

• Passport_ID is the primary key and the unique identifier of each record.

• The other fields hold different attributes of each passport, such as Name, Surname, Date_of_Birth, Place_of_Birth, Nationality, Sex, Date_of_Issue, Date_of_Expiry, and Issuing_Authority.

Reserved ID

In the pre DB there is a process called the reserved ID for real estate tokenization. It involves generating a 256 bit string when a new dataset arrives in the pre base. The function accepts a value that becomes true when the data is filled. Additionally it contributes to the expansion of the pre DB.

• isfilled() --> bool

• string create_rID(rID) --> 256-bit string creation

• hold_rID() --> reserves the ID during the contract time

• add_rdID() --> adds the ID if the contract time is over, confirmed through the specific entry points signature / confirmation.

• rID() --> ID string.

Semi-Database Structure

After the data has been processed and verified by the chosen Entrypoint, it is then moved to a semi-database, where it is more structured. The semi-database holds data that has been sanitized, validated and is ready for further processing.

The structure might look like the following:

CREATE TABLE Semi_Passport (
    Verification_ID INT AUTO_INCREMENT PRIMARY KEY,
    Passport_ID VARCHAR(255) NOT NULL,
    Name VARCHAR(255),
    Surname VARCHAR(255),
    Date_of_Birth DATE,
    Place_of_Birth VARCHAR(255),
    Nationality VARCHAR(255),
    Sex VARCHAR(1),
    Date_of_Issue DATE,
    Date_of_Expiry DATE,
    Issuing_Authority VARCHAR(255),
    Verified_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this structure:

• Verification_ID is the primary key and unique identifier of each record.

• Passport_ID is a foreign key referencing the Passport_ID in the Pre_Passport table, allowing us to trace back the original data.

• The other fields are similar to those in the Pre_Passport table.

• Verified_At is a new field that holds the timestamp when the data was verified.

Making it Efficient

To improve efficiency, we need to ensure that our database design adheres to the principles of normalization, which help to minimize redundancy and dependency by organizing fields and tables.

Indexes can significantly speed up data retrieval operations on a database table. It is advisable to create an index on columns often used in the WHERE clause or used to sort or group query results.

CREATE INDEX idx_passport ON Semi_Passport (Passport_ID);

Data types should be chosen wisely, for instance, using INT instead of VARCHAR for the Passport_ID if the passport ID only contains numbers. This can reduce the storage space and speed up data retrieval.

Using a connection pool can also increase the efficiency of the interactions with the database. A connection pool is a cache of database connections maintained so that the connections can be reused when needed.

Conclusion

Designing an efficient and robust database structure is a complex task that involves careful planning and understanding of the data at hand. It's also essential to understand the SQL language and its features to leverage its full potential for managing databases. The information and examples provided here should give you a solid foundation to design your pre-database and semi-database for storing and managing passport data.