Unique ID

[nikeloeleven]

Hi there, i'm going to have primary fields for my database fields such as "Staff ID" where the IDs will be something like "S01" "S02" "S03" etc.... what datatype would you recommend using for this type of field???

I'm guessing Char because the field sizes will consistently be the same...?
Thanks

 

[alvechurchdata]

I'm guessing Char because the field sizes will consistently be the same...?

Char(3) might be too restrictive. Can you guarantee that you will never need "S100" or even "S1000"?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[nikeloeleven]

Well this system is for a school. I'm assuming there will never be more than 99 staff members in a school. Shall I therefore use Char(3) or is there another datatype you recommend Geoff?

 

[nikeloeleven]

Also, can you please explain to me where a Char should be used instead of Varchar...

and shall i use Varchar instead of Char when the field description doesn't have "not null"?

 

[alvechurchdata]

I'm assuming there will never be more than 99 staff members in a school

There might only be 30 staff in a school but you'll keep on using up IDs as these staff leave and new staff enter.

Do you really need the "S"? My default is to use an integer key (I've never yet run out of integers<g>). If the user needs a prefix like "S" or "Inv" then I just add it to the ID whenever I display it.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[nikeloeleven]

can you explain to me how you make and use an integer key.... it would be helpful if it auto incremented like in Access...

 

[alvechurchdata]

can you explain to me how you make and use an integer key

Not within the time and space available. I support Rudy from your other thread - you really must get yourself a manual.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[svanels]

It looks like your using Interbase, there is an Interbase Forum.

I recomend an integer field, and Interbase has the capability of building generators which keep track of the numbering. These generators you connect to triggers that fire whenever an event happens inside the database, like before or after posting a record.
A before post event is used to obtain an unique number.
a code snippet:

CREATE TABLE SURVEYS 
(
  SNUMBER	TAUTOINC,
  START_DATE	TIMESTAMP NOT NULL,
  END_DATE	TIMESTAMP,
  REMARKS	COMMENTS,
 PRIMARY KEY (SNUMBER)
);
SET TERM ^ ;


/* Triggers only will work for SQL triggers */

CREATE TRIGGER SURVEY_INSERT FOR SURVEYS 
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
  NEW.SNUMBER = GEN_ID(SURVEY_NUMBER_GEN,1);
END

An after post trigger can be used to notify other connected clients of table updates or generate a log..

Steven van Els
SAvanEls@cq-link.sr