Re: Unique Constraint with Multiple NULLS

> Rather than create two identical tables you could just replicate the
> constrained columns and enforce uniqueness through a nullable foreign key
> constraint.

I like this idea! I need to review our design but I think it might work out. The next hurdle will be taking care of performance issues. Our main table will have 20+ possible joins on it!

In case your wondering why so many joins, we are building an application to manage addresses and keep them in sync with some legacy systems. The legacy systems have almost no constraints on the data and over time, well..., lets just say the data is a little dirty and out of sync with the business. The 20+ joins are for all the attributes and legacy system codes associated with an address, mostly lookup tables. The easy solution would be to use the legacy system codes as primary keys and foreign keys to avoid frivolous joins, but we are trying to stay away from that, the volatility of the codes is forcing us to use surrogate keys instead.

I have not worked on a project of this magnitude before, is 20+ joins ridiculous? Over the years I have tried to stick with 5 or fewer joins but we are having a hell of time trying to do that with the requirements we were given.

Thanks again for all your help Dave!

Adam Received on Fri Jan 02 2004 - 06:57:41 CET