In a database schema it is generally necessary to use a primary key for each relation. These keys often only exist in the database and aren't displayed to the user as they have no contextual meaning. While you may be able to use something that should be unique to each row as a primary key (think social security numbers), I follow the practice of creating an explicit column for each relation as the primary key. Going back to social security numbers, what if you needed to store information about someone who, for some reason, did not have one? Maybe you could build your primary key out of someone's name and birthday or maybe phone number- but again, there may be an instance where some of this information is missing or possibly even incorrect!

The problem with using a separate column is that it often needs to be referenced but it's not something you want to show to a user of software. I will discuss this from my experiences working on the Fire Department Management System in which I make use of this schema design method as well as the Qt framework's QTableView. In this project I have a search window which displays the results in a QTableView, a simple grid made up of rows and columns. Each column is an attribute I choose to display to the user and each row is a result.

An example of the QTableView

In this interface, when a user double clicks on one of the rows a new window opens up with detailed information about that row. Herein lies the problem: I want to ensure that the new window is loaded with the true primary key, not just the "ID" shown. While the "ID" column has meaning to the user, it may not be unique and, as shown in the last row, might not even be there at all!

Thankfully, there's a simple way to do this. The QTableView is linked with an underlying model, in this case the QSqlQueryModel. Because of the separation of the view and the model, we can load and use data that isn't shown to the user.

// Set up the query
QSqlQuery exampleQuery;
exampleQuery.prepare("SELECT primarykey,ID,lastname,firstname FROM firefighters");
exampleQuery.exec();
 
// Set up the model
QSqlQueryModel *exampleModel= new QSqlQueryModel;
 
// Link the model to the query
exampleModel->setQuery(exampleQuery);
 
// Link the view to the model
ui->someQTableView->setModel(exampleModel);
 
// Hide the primary key!
ui->someQTableView->hideColumn(0);

Now the interface looks like the one I have shown above, but the primary key is still accessible through the model- we can use a QModelIndex to access the data. Continuing with the idea of a double click event:

// The window constructor
wndExample::wndExample(QWidget *parent,
                     ) :
        QMainWindow(parent),
        ui(new Ui::wndSearch)
{ 
    // Connect the double click event to the function where we access the primary key
    connect(ui->someQTableView,SIGNAL(doubleClicked(QModelIndex)),this,SLOT(tableDoubleClicked(QModelIndex)));
}
 
void wndExample::tableDoubleClicked(QModelIndex tmp){
    // Use the triggered QModelIndex to get the data in the 0th column of its row
    QString primarykey=tmp.sibling(tmp.row(),0).data().toString();
    // Even though the sibling isn't shown in the view, QModelIndex::sibling utilizes the model to get data
}

This is one of many reasons the MVC paradigm is wonderful and I was glad to see how easy it was to implement this functionality with Qt. Now my users don't need to see any irrelevant data and I don't have to rely on relevant information that "should" be unique or present.