* * *

Author Topic: ID Philosophy in Firebird  (Read 686 times)

Wargan

  • New member
  • *
  • Posts: 44
    • 'This way' site
ID Philosophy in Firebird
« on: September 11, 2017, 07:40:56 am »
Hello, friends.

Today, as a beginner in the tricky and mysterious world of Firebird, I started to think about a very important question - how safe to use the ID field as INTEGER in databases?

Well, for example

Code: MySQL  [Select]
  1. Worker_Id INTEGER PRIMARY KEY # is the primary identifier

As far as I understand, the numbering of the ID field through the Integer type is cool: you sort, as well as auto incrementing the step using the built-in SEQUENCE (GENERATOR) capability.

In my old programs I used for Id ... drum roll ...

GUID. Yes, yes, the same. Just VARCHAR in the table.
Generation of GUID was done in code (CreateGuid procedure), and a string was written to the database.

Do you think this approach is safer?
In addition, there is absolutely no chance that within one GUID database will be repeated, which can lead to a data read error.
Lazarus 1.6.4 stable + FPC 3.0.2.
Windows XP SP3, RAM 1 Gb - testing and commissioning programs for old computers.

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 5827
Re: ID Philosophy in Firebird
« Reply #1 on: September 11, 2017, 08:25:32 am »
The risk of this is that you make yourself dependent on the quality of createguid of the platform, and creategui depends on the state of the machine rather than the DB, (which could be a problem when you work with e.g. VMs, and is not very nice as design principle).

signed 32-bit sequences roll over with 2 billion, iow not anyday soon, and you are not dependent on which system your client runs, and if it might be a copy of a VM that was used to put records in the db before. Maybe use 64-bit ints for fastmoving tables.
« Last Edit: September 11, 2017, 08:27:07 am by marcov »

taazz

  • Hero Member
  • *****
  • Posts: 4415
Re: ID Philosophy in Firebird
« Reply #2 on: September 11, 2017, 08:29:16 am »
In my old programs I used for Id ... drum roll ...
GUID. Yes, yes, the same. Just VARCHAR in the table.
Generation of GUID was done in code (CreateGuid procedure), and a string was written to the database.

Do you think this approach is safer?
In addition, there is absolutely no chance that within one GUID database will be repeated, which can lead to a data read error.
Safer? No. Less Safe? No. Different kind of information. For example a GUID borows info from the network adapter, motherboard, current date and time of the users locale and more, to create a hard to replicate unique ID. it is a bit long though for the purpose. Any 64bit can be convert to
1 byte for the country.
1 byte for the brunch in that country
1 byte for user of that brunch.
1 byte for the year
4 bytes for the auto increment
and you have created your own GUID in far less space. I doubt you will find any table that has more than 4 billion entries per year from a single user. That includes bot users too (bot user is the user used to automatically import data in to your application, financial statements etc) well stock exchange data might hit that boundary but you just add a new user for each day of the week and boom problem solved.
As for the no chance well there is "virtually no chance", its not the same as no chance. The thing is why would want to limit your self to string comparison for your indices, which is slower than an integer comparison, and use twice the space, for an ID that has no reason to be unique outside your application?
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Wargan

  • New member
  • *
  • Posts: 44
    • 'This way' site
Re: ID Philosophy in Firebird
« Reply #3 on: September 11, 2017, 10:40:50 am »
So ... it means still using GUIDs as a bad option. Okay, better to use the bundle of INTEGER + GENERATOR? Why it's better?. I want to learn more :)
Lazarus 1.6.4 stable + FPC 3.0.2.
Windows XP SP3, RAM 1 Gb - testing and commissioning programs for old computers.

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 5827
Re: ID Philosophy in Firebird
« Reply #4 on: September 11, 2017, 10:51:32 am »
As said generators/sequences are managed by the database, while the reliability of createguid depends on the platform that where you generate it.

It is better to keep anything related to the DB integrity under control of the  DBMS.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus