| Subject | Re: [firebird-support] Re: Primary Keys - [was] Database File Size |
|---|---|
| Author | David Johnson |
| Post date | 2004-05-24T05:57:06Z |
Of course I am - my comments only apply to C/S and not to all of them
either.
I offer my apologies for not recognizing your awareness. I can only plead tiredness because we have both written on this before.
I do not contest the use of non-integer PKs. I merely comment that there are
people thinking, here and via question to me regarding FBReplicator, that
integers are no good because of their ultimate lack of scale... After I
point out the scale of integers (especially Int64) to them and the likely
demands they will have by their application, that integers are indeed a
valid choice for PK.
The 8TB+ database that I use at work is mostly integer keyed. Integers offer a performance and storage space advantage for centralized and single tier upstream oriented systems.
PS - I have mobile applications (Palm) which function for prolonged periods
in the absence of comms with the central server. They generate -tive PKs
locally. When they do sync, they replace their negative PKs with server
supplied +ive PKS and the detail records (foreign keys) are replaced, also,
in the same operation. This method works well and relies only on integer
values. So integers can operate here equally well.
But your mobile app is still dependent on the assumptions that the remotes (Palms) are not merging with each other, and that they are talking up only one level of heirarchy to a centralized server. The +/- scheme offers exactly one level of upstream merging, and no other merge capability, and has the cost that the primary keys (and any records referencing thos keys) must be changed when the slave database is merged. On the other hand, it allows you to use roughly 10% of the space for the primary key that you would need if you used the fully expanded CHAR(38) GUID that I prefer, which is critical in the world of the Palm.
Integer based keying breaks down as when you take away the concept of a centralized or master database, when you need to merge parallel levels of database across different corporations, or if you have a heirarchy of servers merging at more than one level. On the other hand, it offers noticable performance and space savings where it can function.
[Non-text portions of this message have been removed]
either.
I offer my apologies for not recognizing your awareness. I can only plead tiredness because we have both written on this before.
I do not contest the use of non-integer PKs. I merely comment that there are
people thinking, here and via question to me regarding FBReplicator, that
integers are no good because of their ultimate lack of scale... After I
point out the scale of integers (especially Int64) to them and the likely
demands they will have by their application, that integers are indeed a
valid choice for PK.
The 8TB+ database that I use at work is mostly integer keyed. Integers offer a performance and storage space advantage for centralized and single tier upstream oriented systems.
PS - I have mobile applications (Palm) which function for prolonged periods
in the absence of comms with the central server. They generate -tive PKs
locally. When they do sync, they replace their negative PKs with server
supplied +ive PKS and the detail records (foreign keys) are replaced, also,
in the same operation. This method works well and relies only on integer
values. So integers can operate here equally well.
But your mobile app is still dependent on the assumptions that the remotes (Palms) are not merging with each other, and that they are talking up only one level of heirarchy to a centralized server. The +/- scheme offers exactly one level of upstream merging, and no other merge capability, and has the cost that the primary keys (and any records referencing thos keys) must be changed when the slave database is merged. On the other hand, it allows you to use roughly 10% of the space for the primary key that you would need if you used the fully expanded CHAR(38) GUID that I prefer, which is critical in the world of the Palm.
Integer based keying breaks down as when you take away the concept of a centralized or master database, when you need to merge parallel levels of database across different corporations, or if you have a heirarchy of servers merging at more than one level. On the other hand, it offers noticable performance and space savings where it can function.
[Non-text portions of this message have been removed]