Table index performance. options about choosing primary key
dear all,
i have questions about choosing a table's primary key. assume that we have 3 related tables as described below:
TBL_A
{
'A1' varchar (64), // assume this is the unique attribute.
'A2' varchar (64),
}
TBL_B
{
'B1' varchar (64), // assume this is the unique attribute.
'B2' varchar (64),
}
TBL_C
{
'C1' varchar (64), // assume this is the unique attribute.
'C2' varchar (64),
}
relationships:
between TBL_A and TBL_B as Many to Many,
between TBL_AB and TBL_C as ONE to Many
the questions are:
1/ which one is better: OPTION A: set the unique attribute of each table to be the table's primary key? or OPTION B: create another attribute to be an id (auto number) and set the table's unique attribute with unique constraint,.? (will be applied to all those three tables).
2. then TBL_AB will need to copy both primary key from TBL_A and TBL_B as referenced key. the question is again like my previous one, should we keep those two referenced key to be TBL_AB's primary key,.? or it is much better to create a new unique attribute (auto number) to be the TBL_AB's primary key and make the two referenced attributes with unique constraint,.?
3. because od TBL_C will refers to TBL_AB's primary key, of course the option we choose in the two previous questions will take effect. if we choose the first option, there will be two referenced attributes, but if we choose the second option we will have only one referenced attribute. what do you think,.?
the idea is when we are in a search situation, will querying an integer (or numeric) type primary key perform faster than varchar type primary key,.? which one is better,.? and of course if you have the 'why',.
thank you for every answer and suggestion,. regards,