I have this museums app main table with longish keys - most often 9-16 characters. The users decide what the key values should be, or rather it's decided by the writing on the label on the museums objects they register. So the key contains "real" information - I mean in contrast to something like a sequence counter key. But I guess the app would run better if it did have a sequence counter key. Most clients have thousands or tens of thousands of rows, so the average key length would drop significantly. The table has a lot of indexes, and many typical queries return a lot of hits.

But if I changed, the label id that I now use for a key still needs to be unique. What is the best way to make certain that the value of a field is unique?

Is there anything else I should consider? What I like about having the label id as a key (in addition to maintaining uniqueness) is it's much easier to see if your data/indexes/foreign keys/etc are correct. A sequence number doesn't tell much.

- Oystein -

Oystein,

I have spoken with alot of users that have used the numerical date value joined with the numerical time value. Is this something that you can use?

KEY=date():"-":time()

You can keep it like this or add to it as you wish.

Good Luck…

Blaise

Read the record. If it's there, then the key has been used before.

Or you can be gutsy and just use

@ID=RND(9999999999999999999999999999999999999999)

and just write without checking.

Gary Gnu

Blaise,

Thanks, but I'm after something a little bit different. What I mean is that the user may enter any value she likes, as long as that value is not already in the database.

The field is meant to contain the unique id of a museums object. Most often the objects already have an id written on them, or at an attached label. When the user catalogs an object in the database I want to make sure that it's not already cataloged, by a different user (not following procedures), or by the same (forgetful) user. And I want to catch objects that accidentally have the same id written on them.

Sometimes the objects don't have an id beforehand. The id is made at data entry time. Also then I need to make the same id isn't used twice, because we still want the physical objects to have unique ids, and fairly simple ones at that.

In principle it's of course easy to check if the id is already used. But if the field is unindexed the table will have to be searched sequentially. that will take too long time. And if it is indexed, the index might not be trusted. (I've had too many cases of wrong indexes.) I really need a method that is both foolproof and fast.

- Oystein -

In principle it's of course easy to check if the id is already used. But if the field is unindexed the table will have to be searched sequentially. that will take too long time. And if it is indexed, the index might not be trusted. (I've had too many cases of wrong indexes.) I really need a method that is both foolproof and fast.

If they enter in a key, couldn't you just read it to see if it's there? No reason to use indexes. You have the key, it's what the user entered.

Also, even though Gary was probably just being silly, jamming in a rather large random number might not be a bad idea. Generate one, read to see if it's there, and try again if it's not.

You'll catch IDs that are already in use since when they are entered, they'll bring up record.

[email protected]

Sprezzatura, Inc.

Oystein,

I'm not certain that I am following your question that well.

However, if you are asking when a numerical ID (sequential) should be used instead of a descriptive ID then here a some guidelines that I go by:

1. Use a numerical ID if this ID will be used in multiple places other than the main table. For instance, a customer ID will also be used in a Sales Order table and an Invoice table. Always having to type out the entire name in every case is maddening.

2. In general, use a numerical ID if the number of records will be fairly large. First because it's easier to have a default key ID system without fear of using an existing key and second it avoids the need to "tweak" the descriptive ID to guarantee uniqueness. For instance, a Customer table my have a record for a particular company for each office in the country. If you were to use a descriptive ID (most likely the name) for each office then you would always have to append some distinct description (most likely the city or store number) to make each ID unique. This just complicates the advantage of having a descriptive name.

3. If the database is relatively small, then descriptive ID are great but make sure there is a very quick way to retrieve these ID's without having to type them. Popups and combo boxes work well in these situations.

If I think about it a little longer I could come up with some more pointers but I'll wait and see if this is something you really needed.

[email protected]

SRP Computer Solutions

Oystein,

If the item is unique and has a string or numeric value less than 20 or so characters/digits, then use the item id as the record key.

Cameron Purdy

[email protected]

Aaron, Cameron, Don,

Thanks for the advice, Cameron. I assume that you mean the following:

- With this field as a key I get the uniqueness checking stuff for free.

- If I have it in a regular field instead I get the advantages of having a shorter key (a seq key).

- But the advantages are probably outweighed by the problems I get with checking for uniqueness.

But could you please verify that this is really what you meant?

Don - your pros and cons are appreciated, but do not answer my question about checking for uniqueness on a non-key field.

Aaron - you missed my question too.

- Oystein -

Oystein,

If it isn't the key, you have to index it. It is rather simple … you either have it as the key of the data table or the "key" (figuratively speaking) to the index. If it isn't too big, use it as the key.

Cameron Purdy

Revelation Software

Cameron,

Sure, but as I tried to say earlier I'm not happy with being dependent on indexes. I quote myself :

"In principle it's of course easy to check if the id is already used. But if the field is unindexed the table will have to be searched sequentially. That will take too long time. And if it is indexed, the index might not be trusted. (I've had too many cases of wrong indexes.) I really need a method that is both foolproof and fast."

- Oystein -

View this thread on the forum...