3

There are a lot of manuals. I am trying to create an database to hold information of these documents. But, there is a small problem.

How can I give meaningful id to the manuals? Are there any standard or logic behind the giving meaningful id to the documents? If there is no standard, can you tell me how I should do that?

example: 

 table :

         manual id | manual name

EDIT:

Not Meaningful ID 

     1        or  M1        or   foo
     2            C2             bar
     3            P123           name
    ...           ...            ...
     (i)          (ii)            (iii)

(i) Not meaningful for me because if some item deleted, there can be gap. ex 1 33 100.

(ii) random character can be confusing when one try to give a name to new manual

(iii) Why giving name is not preferred is because finding a name to the manual as ID is hard after 500 manuals.

Meaningful :

New ID

* Can be easily produced even if after 1000 manuals
* Should not be so complicated
Improve this question
3
  • 1
    define meaningful.
    – yannis
    Jun 22, 2012 at 8:15
  • @YannisRizos see Edit
    – user12278
    Jun 22, 2012 at 8:24
  • try to come up with natural key, the simplest one would be datetime. however, use this only as a last option.
    – Yusubov
    Jun 23, 2012 at 3:44

6 Answers 6

Reset to default
14

Usually, one might first try to find a natural key, coming from the problem domain; e.g. an EAN/UPC code for products or the SSN for people.

When there is no obvious natural key, the artificial ID is just a number counting up, and those gaps after deleting are not considered problematic, because the ID is meaningless anyway.

Hint: Meaningfulness cannot be created artificially.

Improve this answer
1
  • You might want to mention UUID primary keys - they are somewhat controversial, but an accepted solution for artificial primary keys among many, and one way of preventing replication problems.
    – tdammers
    Jun 22, 2012 at 14:30
3

What's the problem with gaps in your database when using IDs from 1 to n? If you really mind the gaps, you could add new manuals inside those. (In your example the next new manual would get the ID 2, 3, ... , 32, 34). Or you could save a list/stack of the IDs from the deleted manuals and every time you add a new you pop the next ID from the list/stack. This would save you some time.

Also, an ID is not meant to be meaningful, it's just to have a fast way to distinguish between different manuals, so IDs should be unique. You said every manual has a name which should be pretty meaningful already and if two manuals have the same name they at least have different IDs.

Improve this answer
1

The definition of "meaningful" or even "not complicated" is far from being objective, IMHO. Also meaningful could very well be dependent on the data you're storing in your db.

As stated by mcwise, the purpose of an id is to be unique and not meaningful. Do you really have to use a manual id ? Auto-generated id prevent you from even knowing there are gaps, and will always ensure ids are unique.

If you absolutely want a unique manual and meaningful ID, try finding either a field that will always be unique in your base (for example emails for users in a web site) or generate one from several fields where you know that the combination will be unique (for example firstname-lastname-birthdate-birthplace)

Improve this answer
1

A human-meaningful identifier and a machine-meaningful identifier can be two separate things - both require uniqueness because of the inherent nature of the problem: 

Given X, how can I find the manual I need?

Identifiers should really be given to humans, rather than generated by humans. Humans are bad at creating consistently unique identifiers, but computers are good (or at least quick) at it.

Ideally, you'll have some sort of search mechanism when you are looking for a manual. You won't need to remember some random 16-byte number or QR-Code, because the computer will generate a list of all currently saved manuals for you when you search for "requires assembly" or "batteries not included", and provide links to them for you.

Having said that, it is often an advantage to have a unique key which has no meaning at all. Call it "UniqueID", "MagicKey" or whatever takes your fancy, but be aware that it has no meaning outside of your database. This way, if the meaning of something to do with the manual ever changes in a way you don't expect, you don't have to change the key.

This is why Username is a poor choice for a foreign key in a database. If a user ever changes their Username (if it's based on their surname and they get married, they may want to change it), you have a lot of work to do in your database.

If you chose a meaningless "UserID" instead, and the user changes their username, you only have the change to make in one place, and everyone is happy.

Improve this answer
0

Meaningful and unique hardly coexist in a single place. For me I just use a system generated auto incremental no as ID when it comes to 1000's of records.

In the case of using an incremental number as an ID have an extra field that determines if the manual exist or is being deleted.All you have to do is in the select statement mention another condition in where clause.
Other advantage of using this is you need not bother about what ID is to be assigned to a new manual entry the system will take care of it.
Speaking about he logic to use for assigning ID's it solely depends on the programmer. The logic which is easier for him.

Improve this answer
0

Your concern about missing numbers, and meaningful IDs is misguided.

The ID number should be computer generated one-up number. Asking the user to pick a number invites too many problems. There can be a typo, you could skip a number or duplicate a number. You can write software to look for these problems but it is easier to have the database take care of it on record creation.

If a manual is destroyed because it is not longer needed you don't want to recycle the number. Imagine if a somebody had a procedure where it said get manual 7 and complete the task on page 99 so the computer backups can be restored. But manual 7 is now about the smoke alarms, and only has 10 pages. If one is replaced you should have a field telling the user it is gone, and that the updated manual is now number 50.

You will be using the real titles of the manuals as a field in the database. That way when there are hundreds of them they can be searched by key words. Imagine how hard it would be to talk about books if there were no titles, only the ISBN numbers.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.