Re: generators

A Generator is simply a tool that generates a number. A common use for
such a number is in a field that will be all or part of a record key.
You don't exactly say if that is what you want to do with the generated
number.

You don't need a generator or even a number to create a key for your
records. You can just use some other field for your key field. When I
create an artificial number like this to represent a record I like to
call it "DeptNumber" or just a general "ItemNr" for
consistency across
tables. I save the names "ID" and "Identifier" for
character
identifiers, since few things have numbers for identifiers. I think you
should have a style for naming fields and shouldn't have to know if it
will be part of a key before you name them. You will eventually add or
remove fields from keys anyway.

They Primary Key itself gets a name in the database, and I name it with
the table, PK_tablename. I think you said you use Database Workbench,
and that will do that for you - automatically if you set the naming
convention you want.

For most "Fixed Data" tables, an ID may be better. In Waynes example,
Order.CustomerID would be a generated number (or surrogate key). To get
that data there you will have to do a little more processing, because
the user isn't going to enter it. It can be done, I do it - sometimes.
You need a reason for the extra work. Otherwise, just use the real ID.

Now for Generators. This is preference here, but I like separate
generators whenever I need them.  They also get a name in the database,
something like "GenerateDepartmentNumber". Just my preference. But,
lets see I must have a reason. You can see which generator is used by
which table. You can set (or reset) the generator value for individual
tables.  I was going to say you may have to drop a generator to make a
change, but I can't think of how you would right now - maybe not.

And finally, I like these surrogate keys as Integers when I know they
will never exceed that limit. Integers work better in more places than
Large Numerics. I had a posting recently about working around that
problem in ClientDataSets, IBX Stored Procedure parameters or something.

Have fun. 

-- 

"Sergio Gonzalez" <shg_sistemas@yahoo.com.ar> wrote in message 
news:47dadd0e$3@newsgroups.borland.com...
>
> Should I create one generator per table? Now I have a generator for tables

> with (more or less) fixed data, and other for the tables that has 
> movements (a lot of records) Is there any known problem with that? I'm 
> starting a new project and want to do it as good as possible, regarding 
> with the database design...

You can create any number of generators you want, however generators are 64 
bit numbers, so very little chance you will run out, and it is safe to use a 
single generator for all tables. Make your PK field able to hold that large 
a number (numeric(18,0) will give you a 64 bit integer field).

> Also, is there any rule to name the generators?

Anything you want. <g>  If you use just one then call it something generic

like NewID.

> The PK fields should be always integers? Any rule to name them?

See above for type. I own pattern is to name each table's PK field as simply 
ID. In other tables that reference it, I include the table name. For 
example:

Customer
  ID numeric(18, 0),
  surname varchar(40),
  .etc.

Order
  ID numeric(18, 0),
  CustomerID numeric(18, 0),
  .etc.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

Hi! it's me again with silly questions...

Should I create one generator per table? Now I have a generator for tables 
with (more or less) fixed data, and other for the tables that has movements 
(a lot of records) Is there any known problem with that? I'm starting a new 
project and want to do it as good as possible, regarding with the database 
design...

Also, is there any rule to name the generators?
The PK fields should be always integers? Any rule to name them?

Thanks in advance,

-sergio

In your InvoiceHeader table the InvoiceID is the logical key. I would
probably use that. If you want to create a surrogate key by creating a
integer field populated by a generator, you can do that. Some people
always do that. It requires a little more processing for all your
maintenance features. In my opinion, the extra work doesn't always get
you more benefit. A surrogate key does enable the user to change the ID
easier - which you may also have to protect against.

Next, in your InvoiceDetail table, you should have either the Number or
ID that is the key in your InvoiceHeader, plus some identifier for the
detail line. That can be a character identifier (like LINE1), or it can
be a number representing the detail line (1) or it can be just a unique
number - generated by a Generator. The best may be the last option,
where you use a generator to generate a new number every time you enter
a new InvoiceDetail record. This is the easiest because you don't have
to figure out which line number is being added - you just generate a
number. You will have deleted line numbers on your invoices.

Just some ideas. There is more than one way to do this. I think the
important considerations are functionality and ease-of-use. Computer
efficiency should not be your concern. Have fun, and I hope others will
give you their ideas.

-- 

Thanks Patrick & Wayne!! Very usefull coments!!
I have just one more question:

> You don't need a generator or even a number to create a key for your
> records. You can just use some other field for your key field.

I allways wander wich way would be better for the case of, let's say, an
invoice:

in the table "INVOICE_HEADER" I have an INVOICE_ID.
in "INVOICE_DETAIL", I have again the INVOICE_ID, and here is my
doubt:

It is better to have a INVOICE_DETAIL_LINE and make the PK as
INVOICE_ID + INVOICE_DETAIL_LINE?
or should I create a INVOICE_DETAIL_ID and make it my PK?.

I guess that the last option is better, but I'm not sure about it. I have
the idea that is better to have just one number as PK always. But as Patrick
said is his post, is there no problem using any other kind of field? or even
a set of fields to make my PK ? Hope I'm clear!! Sometimes is very hard to
express myself in English!

Thanks again!!!

-sergio