lundi 10 août 2015

SQLITE text foreign key: Making a database human readable

I have an sqlite lite database and I want it to be possible to insert values manually or read the database without being annoying to do. For example, let say you have the following 1 to many relationship

Form Table

pk  name            fk_script
1   view_monster    1

Form Block table

pk  fk_form name        type    block_order display_name           display_type help    select_query
1   1       creature    query   1           List of monsters        recordset   NULL    SELECT * FROM creature
2   1       modifier    input   2           Insert values to modify NULL        NULL    NULL
3   1       single      query   3                                   record      NULL    SELECT * FROM creature WHERE pk = $pkOFcreature

As you can see, if I want to insert a new block in a form, I need to check the form table, note it's primary key, then get back the the block table and use that PK in fk_form. That would be the most efficient way to implement it, but it makes it harder to read. Since each time you encounter a foreign key, you need to check the appropriate table.

Now what I was thinking was to replace Foreign keys with TEXT Foreign Keys to make links between tables. For example, the form block table above would become something like:

pk  fk_form             name        
1   view_monster        creature
2   view_monster        modifier
3   view_monster        single

In that case, I am using the name of the form instead of it's primary key. The primary key could remain to make sure each field is unique or it could also be removed.

The advantage would be human readability, and also remove the need of a "name to key" conversion. For example a script could contain the line of code:

open_form ("view_monster");

In order to get the form blocks, I need query the form table to get the primary key before querying the form block table. But using name as a key allow me to query the form block directly.

I do understand that there could be performance issue. But in that case specifically, it is used to create a form. So it reads the tables once, generate the form objects and then it's done. But if there is a huge amount of data, it might still be slow.

The other issue I can see is misspelling the foreign key during insertion.

As for key duplication, I will force users to have unique names (like in MS access form names for example) so it should not be an issue. The name will have to be unique.

I am trying to weight the pro and cons, so I was wondering:

is there any other issues I am not aware of?

I am not sure if it's worth the jump since in the end there should be a user interface to manipulate those data. So the user should not bother about keys themselves. But there are still many data format, especially document database, that do not use keys. So I thought the same could be done here.

Thank you for any insight.

Aucun commentaire:

Enregistrer un commentaire