Is adding an autonumber field as a Primary Key makes perf. faster? 1

[inso18]

Hi Everyone!

I have a database with a commonly used table of about 500 records. This tables includes a 40-char text field of object's name with unique values. The field is called ObjectName, and it is currently my Primary Key for this table.

My question is, should I add an Autonumber field called ObjectId and make it the Primary Key for this table instead of ObjectName? Would it make searches and other Access activities faster with this table?

regards, inso18

 

[RoyVidar]

This area of database design contains lot of opinions and preferences - in other words - a can of worms.

I think it depends. If this table is the "parent" table of only one child table, so that this primary key is propagated as foreign key to only one other table, I'm not sure you'll notice (probably depends on number of child records)

In a reply about lookups, Allen Browne states it makes sense to use such approach

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/8162d9a4992570d0

"There is a debate about whether the lookup table should have a numeric field as its primary key (in which case your foreign key field will also be a Number), or whether you should use a natural key (Text type.) If you have a lookup table for categories, each category name will be unique. If they are also relatively brief (just a couple of dozen characters), it makes sense to me to use that as the natural key instead of introducing an artificial key (such as an AutoNumber.)"

Of course join operations on text fields will be slower than join operations on numeric columns, and a length of 40 is a bit much, but I think you need quite a large number of records in the child table for it to really matter. But it will be slower.

Searches will probably be faster. When you keep the object names as foreign key, you don't need to join the tables, and lookup the search value in the parent table, you only look in the child table.

I'll usually always add an extra column to act as primary key for my lookup tables. Sometimes an autonumber. My reason is not performance, but that the name/description/category sometimes change, and I'm not very fond of relying on cascading updates in a production system with concurrent users ;-)

Perhaps the best suggestion, would be for you to test, and see what fits best in your situation ;-)

Roy-Vidar

 

[inso18]

Roy-Vidar,
thanks for giving me several arguments and what are the benefits and disadvantages of each method.

I'll test both methods myself, and see which one is more speedier for me.

Can cascading system fail?

Regards,
inso18

 

[RoyVidar]

> Can cascading system fail?

I don't know. I've never seen it, but then I very seldom use it ;-)

I've seen some people claim it can fail, without notification, but I really don't know.

Roy-Vidar

 

[inso18]

Thanks for your assistance.