Fog Creek Software
Discussion Board




Simple Question

I am begining to develop yet another small app for one of my relatives. While designing the database am thinking again if I must take IDs/primary keys in the MS Access tables as Text or AutoNumbers or just Numeric types (int/long). Earlier on some apps, I took PKs as text and recall having sorting problems. When I taken an auto number, I have manageability problems when some last rows are deleted.

What's the best way to go?

Sathyaish Chakravarthy
Monday, February 02, 2004

Sathyaish,

In my oppinion its best to go with numeric primary keys,
since primary keys is what joinable between tables. The rule is never make user editable fields joinable.

I use sequences (auto numbers) to avoid joining on some text field that is editable in the GUI. Also my database is case sensetive so you can get all kinds of problems using text fields.

Patrik
Monday, February 02, 2004

I most cases I'd use numeric internal identifier. Whatever its auto-number or not its up to you.

Numeric - take less space and really fast in lookups. Internal - its only reliable way of keeping them unique and protected from sudden format changes (any external or editable piece of data may change its format etc).

Autonumbers - don't really know a lot about them in MS JET, but I presume its better to use them, because you shouldn't then worry about assigning unique number within your code.

Character? I used character ID-s in certain cases (automatically generated) as when I needed to show hierarchy in the table. But previously I did R&D on possible data volumes, speed of fetching and adding records and I still had unique integer ID for each record in the table.

Vlad Gudim
Monday, February 02, 2004

If you want to use a "manual primary key generator" and a Long-type primary key, you can set the ID's semi-automatically.

You have your textbox for the primary key.  Let's call it txtPrimary.  Set the "Default Value" property for this as:

=Nz(DMax("[YOUR_ID_FIELD]","[YOUR_TABLE_NAME]"),0) + 1

If you want it to look like a "hold leading zeroes" text field, then set the Format property to "0000" or "00000" or however many zeroes you want.


Pete
PS--DMax works well only if you're using an Access backend.

pds
Monday, February 02, 2004

PPS--If this is a multiuser application, then you must check to find the "max" ID# immediately before saving the record.  In an Access form, you'd do it on:

'pseudo code
Form_BeforeUpdate(Cancel as Integer)
If Me.NewRecord Then
    're-generate the ID# here
End If


Pete

pds
Monday, February 02, 2004

Thanks guys.

"I use sequences (auto numbers) to avoid joining on some text field that is editable in the GUI"

Patrick, I didn't get this.



PDS, I am using a VB client and the Access or I must have said the Jet engine only as the backend.

Sathyaish Chakravarthy
Monday, February 02, 2004

Oops.  Well, 100% of my advice was for the Access forms functionality.


Pete

pds
Monday, February 02, 2004

Auto-number fields in Jet are somewhat limiting because it's impossible to override them.  For example, it's impossible to delete a record and later restore it with the same ID (e.g., because of Undo).  A standalone sequence generator would be much more useful, unfortunately Jet doesn't support it AFAIK.

If you don't need simultaneous multiuser support then don't use AutoNumbers.

Oren
Monday, February 02, 2004

"When I taken an auto number, I have manageability problems when some last rows are deleted."

Good grief! I don't believe I wrote that sentence. I was actually talking to someone standing at the back of my seat while writing this. I feel aweful reading it. Sorry, sorry, I am sorry, I am really sorry. Sorry for the carelessness. I must have been out of my mind not to proof read before posting.

Sathyaish Chakravarthy
Monday, February 02, 2004

Use numbers.  If you use character data, you will be tempted to do something meaningful with teh primary key, which is bad.  For example, you'll be tempted to do something short sighted like prefix priority orders with P and normal orders with O.  The primary key should be an unchanging and meaningless value.  You can have a seperate UniqueID for human readability if you must.

Richard P
Monday, February 02, 2004

*  Recent Topics

*  Fog Creek Home