Which field of table is suitable for primary key?

[masoud_sedighy]

Actually my question is in 2 parts:

• If we have one table like below that we are sure 1 field of this table always is unique we have to select that as primary key or we have to add one auto number field for primary key?

Table:

Doc no (PK), Text type
Title
Originator


Field I have selected for primary key is text type and some times around 50 characters. Now in my database that I have built before speed of some queries are very slow.
I do not know the problem is for primary key or no?

• If I have to add new auto number field to my related tables and define new primary keys how I have to that, because each of table has a lot of records?

Best regards.

 

[RainLover]

There is no Rule that says one is better than the other so the choice is yours.

Personally I prefer to use Autonumber as the Primary Key.

If you decide to change from your Natural Key to a Surrogate Key (Autonumber) you could run an Update query on your tables to add the Autonumber as the Foreign Key.

That is assuming you have a correct Relationship to start with.

 

[masoud_sedighy]

Is it possible explaining more how I can do that? For example for my table that I defined like below​

Doc no (PK)​

Title​

Originator​

​

I want to add primary key (auto number) doc id:​

Doc id (pk)​

Docno​

Title​

Originator​

​

Best regards​

 

[RainLover]

Please explain your Table structure properly. Include Table Names.

If the above as you described is Two Tables then your design is incorrect.

 

[Galaxiom]

There is no Rule that says one is better than the other so the choice is yours.

I strongly disagree because:

Field I have selected for primary key is text type and some times around 50 characters.

Long text fields make poor keys because they are much, much slower to process than a numeric or a couple of text characters.

 

[masoud_sedighy]

I have 3 tables.​

Table 1:​

Doc no (Primary Key) text (50)​

Title text (100)​

Originator text​

-------------------------------------------------​

Table 2:​

Trans no (Primary Key) text (15)​

Date date​

----------------------------------------------​

Table 3:​

Doc no (Foreign key) text (50)​

Trans no (Foreign key) text (15)​

Size text​

With this design my queries are slow so if it has good effect on my database, I like to remove primary key for old ones and add auto number fields to each table like below:​

Table 1 (new):​

Doc id (Primary Key) auto number​

Doc no text (50) required (yes) no duplicate​

Title text (100)​

Originator text​

--------------------------------------------------------​

Table 2 (new):​

Transid (Primary Key) auto number​

Trans no text (15) required (yes) no duplicate​

Date date​

Table 3 (new):​

Doc id (Foreign key) auto number required (yes)​

Trans id (Foreign key) auto number required (yes)​

Doc no text(50) required (yes)​

Trans no text (15) required (yes)​

Size text​

​

Sample values of my doc no are like this: DW-8408205-999-0060-0001​

Sample values of my Trans no are like this: 8288-MT/FT-TT-0005

​

Best regards​

 

[Galaxiom]

Foreign Keys should be Numeric - Long Integer.

First: Back up your database.

Add the new key fields to each table.
Then use an update query with joins on the old key to update the new FKs to the matching PKs on their parent table.

Delete the original relationships.
Make the new relationships.

Compact and Repair.

 

[RainLover]

Galaxiom,

Are you sure you STRONGLY disagree?

Consider this.

Both AutoNumber and Text when used as a Primary key are Indexed. So all operations are based upon the same type of Data. Namely Binary.

Searching etc is not done on the actual AutoNumber or the Text. In both cases it is done on the Index.

There are some Minor differences in speed when Updating, but then it is so small it would be hard to measure. If you happen to have a Test DB that measures the difference then it would be good to see.

This of course does not apply to Text Fields that are not Indexed. Text would in that situation be slower.

 

[Galaxiom]

Both AutoNumber and Text when used as a Primary key are Indexed. So all operations are based upon the same type of Data. Namely Binary.

Searching etc is not done on the actual AutoNumber or the Text. In both cases it is done on the Index.

I would be interested to know how it can manage that on text. Access is case insensitive, so with 50 characters there could be as many as one trillion different binary renditions of the value.

 

[RainLover]

Galaxiom,

Good Morning.

Before I respond I would like to clarify a couple of points.

Firstly I never suggested that a Text Field was better. All I said was that there is no rule.

Also I never suggested that one was faster than the other and still have not.

What I did say was that Autonumber would be my preference.

Let me now add that I feel it is important for designers to understand the use of the various types of Primary Keys. Namely, Autonumber which is a Surrogate key, Text which is a Natural Key, e.g. Tax File Number, and a Composite Primary Key which is a combination of two or more fields.

When developing a Database of my own I always use Autonumber but there has been many situations where I have had to make alterations to Database written by others which have had Primary Keys of the latter two types. I particularly hate composite Keys but can live with Text.

I would be interested to know how it can manage that on text. Access is case insensitive, so with 50 characters there could be as many as one trillion different binary renditions of the value.

I do not know the inner workings of Access or more correctly Jet but I do know that all Primary keys are Indexed and all search operations are on that Index not on the Data contained within the Primary Key Field.

Indexing does not convert the Data from the PK into Binary. It adds an additional Field, (Field is not strictly correct but will suffice for this explanation). This Index is strictly binary and bears no resemblance to what it is contained within the PK. So Data such as “cgert^7jsn ef” if it was the only record would be ZERO. The next record world be ONE. If the first record was deleted then the Table would be reindexed and the second record would become the first and reindexed with Zero.

Access reindexes the complete table every time an update is performed. So this is where speed is slower on Text rather than Number. Slower when saving, not on searching.

 

[Galaxiom]

So it would appear that for the index, each value is changed to a case insensitive binary representation of the original value (a hash) regardless of its datatype and matching is performed against that hash as a whole so there is no penalty for searching text in an index.

I expect that inside the index the binary value is held against the page and record number of the corresponding record.

Thanks for clearing up that.

I guess the indexing process is slow on the text because the records are first put in alphanumeric oreder (slow compared to numeric order) before beng hashed. I assume that is what is meant by the ascending and descending in the properties of the index.

Speed of indexing during updates and inserts would still be a good reason to avoid a long string as an index.

 

[RainLover]

Thanks for the reply.

Indexing is more complicated that what I have touched on but for now it appears we are singing from the same Hymn book.

As a by the way. Another form of indexing is the manner in which documents are saved on the Hard Drive. When a document is requested the hard drive is not searched. Instead it looks into the FATs (File Allocation Tables) to find the location of the document and then proceeds to retrive the Document from a stored address in the FAT.

Enough for now.

Have a Merry Christmas.