Search This Blog

August 19, 2007

Do we really need a foreign key in the table?

If you worked on database design before and now I ask you a question:
Do we really need a foreign key in the table?” , you will definitely say: Of course!
Foreign keys are important in database design. We need to follow normalization(First normal form, Second normal form and third normal from).

If you get a chance to ask Database Administrators in large companies, they might tell you: we don’t want foreign keys.

Is this the big conflict between SAs and DBAs?

Just imagine one situation:

If ABC Company has a famous website that sells many products to customers, their network traffic is very heavy. When customers place orders and want to receive their orders as soon as possible, how can ABC Company handle it?

ABC must have many database servers, but the data on the main server needs to be replicated to other servers quickly. Only 3-5 minutes, the data must be updated automatically and they can ship the products quickly. If the network traffic is so heavy and many tables have many foreign keys, the replication will be very slow and might cause failure.

Just imagine another situation:

Many companies have the daily build or weekly milestone build for developers and testers to test if their products are reliable. If there are so many tables including so many foreign keys in the database, how can DBAs setup the new build as soon as possible? If they can’t set it up immediately, many people will say: they block my task. No wonder that many DBAs say: Foreign keys are the burden of replication.

In the real world, you can have many chances to see if my opinion is right. Do we really need a foreign key in the table? Obviously, it depends on the situation.

I will say:

No replication, need foreign keys.
Need replication, no foreign keys.

No comments: