| Subject | Re: [ib-support] Design database question |
|---|---|
| Author | Paul Schmidt |
| Post date | 2001-12-03T17:41:49Z |
On 2 Dec 2001, at 14:08, zifnabbe@... wrote:
the database and how many separate databases you will have, you
can work something out.
1) Assign each database a range within a generator, a generator is
an INT64, so you could easily assign a range, say 2,000,000,000
values to each database meaning you could have up to
~2,000,000,000 ranges, when they get merged, you start at the
next range above all of the other assigned ranges, and continue on.
However if you don't guess the range size well, you end up having
to sign additional ranges. Up until the merger time.
2) Assign each database a database ID, the database ID gets
included with the generator in the primary key, again at the merger,
you assign the merged database it's own ID. With this, you need
to manage multiple fields,
3) If the number of databases are small, then you can wait, once
you get to the merger you take the generator value multiply it by
the number of databases, then add 0 to the first, 1 to the second, 2
to the third, etc. You will again get a nice layer of unique numbers,
but wasting fewer values. However, it may be difficult to do this
using SQL, so you might need software to manage it, rather then
being able to use a pump utility.
4) Instead of using one, use another value for the GEN_ID
command, greater then the number of databases, for example if
you have 5, you could make this 8 or 10, then add a factor into it.
So for example you end up on the third of five with
NEW.ID = GEN_ID(generator,5) + 2; This is the most automated,
but requires slightly different SQL for each copy of the database.
Paul
Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com
> Hi,There are really three options, depending on how big a "load" is on
>
> I'm working on a database which now, will be used in several places,
> but not as a central database, but as a seperate database at every
> place. Within a few years, it is quiet possible that the seperate
> databases will merge into 1 (thus a central database). I use a lot of
> Autogenerated unique keys, but when the seperate databases will merge,
> the key will not be unique anymore... how can I avoid this? How can I
> design this database so it will be easy to merge into 1 at a later
> stage, but with the uses of an autogenerated key (thus a primary key
> of 1 field?)
>
the database and how many separate databases you will have, you
can work something out.
1) Assign each database a range within a generator, a generator is
an INT64, so you could easily assign a range, say 2,000,000,000
values to each database meaning you could have up to
~2,000,000,000 ranges, when they get merged, you start at the
next range above all of the other assigned ranges, and continue on.
However if you don't guess the range size well, you end up having
to sign additional ranges. Up until the merger time.
2) Assign each database a database ID, the database ID gets
included with the generator in the primary key, again at the merger,
you assign the merged database it's own ID. With this, you need
to manage multiple fields,
3) If the number of databases are small, then you can wait, once
you get to the merger you take the generator value multiply it by
the number of databases, then add 0 to the first, 1 to the second, 2
to the third, etc. You will again get a nice layer of unique numbers,
but wasting fewer values. However, it may be difficult to do this
using SQL, so you might need software to manage it, rather then
being able to use a pump utility.
4) Instead of using one, use another value for the GEN_ID
command, greater then the number of databases, for example if
you have 5, you could make this 8 or 10, then add a factor into it.
So for example you end up on the third of five with
NEW.ID = GEN_ID(generator,5) + 2; This is the most automated,
but requires slightly different SQL for each copy of the database.
Paul
Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com