Recursive or How?

From: Sly <slamcaz_at_yahoo.com>
Date: 19 Dec 2001 07:22:21 -0800
Message-ID: <7d8e274a.0112190722.74bd8447_at_posting.google.com>

I'm working on developing a new database for a bug tracking system.

I have a Customer table ( main office ), a Location table (branch office) and
a Call table ( calls that are being logged ). A one to many relationship exists between the Customer and their Locations.

Each record in the Call table may optionally have eighter a Customer or a Location as Parent.

Now, here's my problem: Does this mean that I will always have a NULL value in one of the two foreign keys in the Call table?. Is this good design?

Am I missing something here?

The only other way I can think of is by using a single table with a recursive
relationship. The Customers and their locations would then be stored in the same table and only two tables with a one-to-many relationship for the Calls would be necessary. A recursive relationship might involve other considerations that I'm not aware of during the development of the client app...

In case it's useful for you to know, I am implementing this on SQL Server 2000.

Thanks for help. Received on Wed Dec 19 2001 - 16:22:21 CET