"GGerard" <gg*****@nbnet.nb.ca> wrote in
news:In*********************@ursa-nb00s0.nbnet.nb.ca:
Hello
I'm trying to find the best way to set indexes and primary
keys on MSAccess tables
What are the advantages and disadvantages of indexes and
primary keys? What fields should be indexed?
Should I index more than one field per table?
What fields should have the primary key?
and so on
Is there any good reading material on this available?
Thanks
G Gerard
The primary key to a table is a field or combination of fields
which uniquely identify a row in that table. You can often use
an autonumber field as a surrogate primary key. Some people will
say this is correct, others will claim that the natural primary
key is best. Personally, being an old dBase III programmer, I
avoid autonumbers. You may have database designs where you have
multiple candidates for primary key. These should be indexed.
As to general indexes, the idea is that if you use a field to
sort the table, or a combination of fields, these fields or
groups should be indexed, even if they are not the primary key.
You should also index fields and groups that are used to filter
the table.on a regular basis.
Some claim that the foreign keys in a table should be indexed
too. Foreign keys are the references used as the relation to
another table's primary key. Access apparently does this
automatically when you set a relation, but I don't have any
reason to believe that this is important. .
For reading material, any good textbook on relational database
design will discuss these topics in greater detail, try your
public or university library.
This web article is also good:
http://www.databasejournal.com/sqlet...le.php/1469521
--
Bob Quintal
PA is y I've altered my email address.