Advice needed for a SQLite design

Databases
21

An heritage tree construct may solve most cases, like:

id_person              int not null primary key
id_father              int
id_mother              int
national_document_id   text
name                   text
surname                text
birth_date             text
sex                    text

This double linked construct allows to know who are your declared parents (if any, even the grandparents and beyond) and also all your children (and theirs…)

Father and Mother are also Persons

22

with the link_type (“type de lien” field in french) you can detail if it is mother or step-mother, child, adopted…etc
more versatile in our modern societies…

23

Everything depends on the exact needs, that’s why I said “may” and “most”, because I know it does not solve “any”. :wink: Your approach with proper labeling with codes, not texts, of the relation, from a table of relations, may be a good one, but observing your argument I only see reason for pointing just if the parent is parent or stepparent, the gender is on the person table, a child is someone at the “enfant” id, if he/she is stepchild you can get observing if parent is “parent” or “stepparent”.

24

It is not so simple, nowadays. I do not even know if they were thinking so far / if I am thinking too far.

I will check with them by tomorrow.

The UI (no db code done yet) is nearly done. This night was clear as a day !!! (and today, it’s raining above Strasbourg, France, so a bit dark :grinning:).

Thanks a lot people.

BTW: now some times ellapsed, my memory flagged me that I used First Name + Familly Name + Birth Date as links to a People’s TABLE and Meeting reports / Information TABLE. Worked fine. That was 5 years ago.
I also recall having used an ID (integer, as Unique ID) to link song titles to LPs… and that was probably 15 years ago. And I forgot the details; not sure how to implement the db code today.

Not using something leads to holes in the memory / unability to use it. Oldster speaking here.

25

Using Integers to identify a record is not the best method, especially if you might have to deal with data import / merging in the future. Merging ciontents of two tables, both with unique (integer) IDs starting at 1 is not really something nice…

I generate UUID (strings with 32 characters) as identifier for a record. If you own a license of MBS, you can use UUIDMBS.ValueHexString, otherwise there are declares to use system funtions…

26

OK. Thank you.

Searching in the documentation: forget.

Reading books: they waste chapters talking about how long are integer, long integers etc. and all common knowledge.
I even have a SQLite for dummies that does not talk about the use of CREATE … IF EXISTS …

And I do not talk about tutorials who may have worked, eventually…

I also recall having used an ID (integer, as Unique ID) to link song titles to LPs
In that specific case, I had a finite list of recorded songs by an artist and build the LPs from these. It is very blury in my memory (details lost, material and software in a storage bin).

27

using

as a link ID will NEVER work well … always use a unique integer or a UUID

2 Likes
28

+ foreign key relationship

29

Things like 2 different Gabriel Martin born in January 1, 2003 happens.
And the Index tree stored will be unnecessarily large.
So no. :wink:

If you don’t need unique global objects, just use the default id, an Integer, an int64 can store all the Earth population more than 1 billion times without repeating codes.

The UUID vs the default sequential 64 bit Integers :

The UUID has uses, but they are kind of very specific not reaching the regular user, 99% of the times they don’t need to be used (specially in the small business kind of applications) and they have some disadvantages.

Pros

Using UUID for a primary key brings the following advantages:

  • UUID values are unique across tables, databases, and even servers that allow you to merge rows from different databases or distribute databases across servers.
  • UUID values do not expose the information about your data so they are safer to use in a URL. For example, if a customer with id 10 accesses his account via http://www.example.com/customers/10/ URL, it is easy to guess that there is a customer 11, 12, etc., and this could be a target for an attack.
  • UUID values can be generated anywhere that avoid a round trip to the database server. It also simplifies logic in the application. For example, to insert data into a parent table and child tables, you have to insert into the parent table first, get generated id and then insert data into the child tables. By using UUID, you can generate the primary key value of the parent table up front and insert rows into both parent and child tables at the same time within a transaction.

Cons

Besides the advantages, UUID values also come with disadvantages:

  • Storing UUID values (16-bytes) takes more storage than integers (4-bytes) or even big integers(8-bytes).
  • Debugging seems to be more difficult, imagine the expression WHERE id = 'df3b7cb7-6a95-11e7-8846-b05adad3f0ae' instead of WHERE id = 10
  • Using UUID values may cause performance issues due to their size and not being ordered.

So, using some IDs as UUIDs depends on your design and goals. It’s not a rule.

2 Likes
30

Thank you Rick.

1 Like