Re: Should I use database foreign keys and indexes

Alex,

Thanks for your input. You are right that web application development isn't the proper problem area. I have referred to the group as application database designers (then they build a web application to interface with it)

What I meant by a table index being a: "Unique reference to the table so that a change to a datafield doesn't require a change to all tables that reference it" Is that if you have a table that uses business data as a primary key and the business data changes then you have to update the business data in all tables that might reference it. For example:

For example, Lets say you have the table: Employee and use their name as the primary key. For the sake of argument lets assume that we don't have to worry about duplicate names. We will also have another table that defines the organization structure and another table that defines salaries and uses the name to reference the employee. If the employee name changes then all of the other tables will have to be changed also. If after the application development has begun it is decided to add another table that references employee you will have to update all of the code that updates an employee name to reference the new table. That might night be so bad in a simple application but in more complex applications it could be a maintenance nightmare.

In general do you use foreign keys and indexes?

• Carl (Software Engineer)

"Alex Petrov" <master.db_at_mail.ru> wrote in message news:<ce4f0fc2d221b8c9a41ebc04a957ac1a.54296_at_mygate.mailgate.org>...
> Hi,
>
> >I recently had a discussion with several web application developers
> >where I work about the use of foreign keys and table indexes.
> >...I was suprised that they felt that foreign keys and indexes
> >add unnecessary complexity to a database and that they never use
> >foreign key constraints and rarely use indexes as a primary key.
>
> It's not the problem area that should be reffered to Web developers (or
> you should call them some other way, say Database developers/analysts).
> Web developers have to write application/middle-tier code/scripts.
> (It's mostly style of low-cost/weak-design/cheap-simple software
> companies that employ -all in one- people.)
>
> >Foreign Keys Constraints:
> > Pro:
> > Provide referential integrity
>
> Ok.
>
> > Con:
> > May have to disable when you are doing mass updates
> >
>
> In fact, depends on the DBMS you use.
> * Slows down INSERTs. (It's obvious because DBMS requires some time to
> validate any of such an insert operation.)
>
> >Table Indexes as Primary Key:
> > Pro:
> > Unique reference to the table so that a change to a datafield
> >doesn't require a change to all tables that reference it
>
> (Something strange you are about. Would you explain the above one?)
> * In general, indexes are intended to speed up queries (mostly SELECT,
> but also UPDATE and INSERT queries).
>
> > Con:
> > May have to do a table join to search on a particular table field
>
> Indexes should be rebuilt to maintain their accuracy (sometimes this
> should be done in real time manner --slows down the performance).
>
> > CONCLUSION:
>
> Yes, you should if you really need them (PK/FK constraints and Indexes).
>
> Good luck
>
> Alex
>
> System Analyst/Architect, DB Progarammer
> mcdba,ocp
Received on Thu Jul 11 2002 - 15:09:51 CEST