Edgar, replacing character Primary Keys with integer ones is not
a rule of thumb or a commandment. A system with only two tables
might not benefit from changing the primary keys to integer.

One of the most important aspects in your design is the total
size of your primary keys. In fact, this is not about changing
to integer or char, but it is about your primary key design.

There are two basic types of PK design:

INTELLIGENT PK
This is the most intuitive. They mean the same for both
end-users and developers. Very simple example:

Driver
==================================
License_Number varchar(12) PK
First_Name varchar(30)
Last_Name varchar(30)


Driver_Accident_History
==================================
License_Number varchar(12) PK-FK
Accident_Date date PK
Accident_Type char(2) PK
Description varchar(250)


Driver_Accident_History_Picture
==================================
License_Number varchar(12) PK-FK
Accident_Date date PK-FK
Accident_Type char(2) PK-FK
Picture_Number integer PK
Picture varchar(250)


In the second and third tables, there are composite primary
keys. That makes sense, but might bring maintainance and
performance issues down the road.


SURROGATE PK
In order to avoid some of the problems related to composite
keys, we can resort to PKs that are meaningless to the end-users
and will work behind the scenes to make the database run faster
and ease maintainance. Those keys usually consist of just a
sequential number - that's why people talk about designing
primary keys with integer fields.

Driver
===============================
Driver_ID integer PK
License_Number varchar(12) Unique index - NOT NULL
First_Name varchar(30)
Last_Name varchar(30)


Driver_Accident_History
===============================
Accident_ID integer PK
Driver_ID integer FK
Accident_Date date
Accident_Type char(2)
Description varchar(250)


Driver_Accident_History_Picture
===============================
Picture_ID integer PK
Accident_ID integer FK
Picture blob


Notice that it becomes much easier to design relationships and
you avoid a lot of duplicate information. Also, in case you
change your License_Number to varchar(15), for example, there's
no impact on the related tables.

That said, you can easily figure out that the issue is not
exactly related to picking out varchar or integer, but to
deciding the best design to your tables.




--- Edgar_Brítez <sebaot@...> escreveu: > Hi:

>
> Please they excuse my English.
>
> A basic consultation:
>
> I have designed my data base with two tables (very important)
> with
> primary keys to "char".
>
> That way it listens that the data bases better work with
> primary keys to
> "integer".
>
> I am making some tests of comparison for the recording and
> access to
> tables with keys "to char" and another one with key "to
> integer"
>
> Example: to char = ' 00000000010000 '
> to integer = 1
>
> In my tests the recording in the table with key "to integer"
> is double
> the fastest one than with the key table "to char".
>
> It costs to me to determine if with the "select" happens
> something to
> similiar.
>
> Reason why my consultations are the following ones:
>
> a) The "Select" are also faster in tables with keys "to
> integer" that
> with keys "to char"?
>
> b) Really exists as much difference of performance between
> tables with
> keys "to char" and tables with key "to integer"?
>
> c) That it says the theory, that they recommend to me to use?
> I consult
> to them then as I already said to them designs everything with
> 2 tables
> (very important) with primary keys "to char" and if really the
> performance is extremely superior with keys "to integer" I
> believe that
> I must make the decision to change, which will consume long
> time to me
> (the development of the program this in 70 %), reason why need
> to be
> really safe of the situation.
>
> Thank you very much
>
> Edgar Brítez
> Sebaot Software
> Asunción-Paraguay
>
>
>

______________________________________________________________________

Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora:
http://mail.yahoo.com.br