Hi,
If you had to build a relational data warehouse of biblical proportions using SQL Server 2008, would you use foreign keys to enforce data integrity, or would you use some other means?
I like foreign keys because you only need to get them right once, and they are always there to protect integrity. I was thinking of going the disable, load, enable route.
Any thoughts?
Thanks in advance.
Oh, I most certainly would! What you have to remember is your database is your data store, it is not just the data store for the frontend. This is subtle difference, but it's important when you start to consider the future. Right now, you (presumably) own the management applications, but who is to say that in the future this will still be the case?
By off loading as much validation as you can onto the database, you somewhat future proof your application - at least if someone else tries to develop against your database, more of your assumptions will hold.
The drawbacks of having this on the database side is slower insertion, so you need to weigh up how heavy your application is against reading and writing. At work, we have much more demand for read than write, so referential integrity seems obvious. However, our tables are big (and freely available for importing), so we go down a multiple step import route of creating the tables, inserting the data, creating indexes, and then creating foreign keys and other constraints.
I hope this helps!
In the first place, I wouldn't build a data warehouse that (physically) conformed to a relational schema. Is the proposed data warehouse to be fully normalised, or does the word "relational" in the question simply indicate that it will be built in a SQL database?
Yes I would generally use foreign keys. That's important in any database but perhaps especially so if the warehouse is a complex one with many tables.
The reasons for using integrity constraints in the warehouse are much the same as in any other database: It minimises the risk of incorrect data getting into the database; It is very often the most economical and performant method of implementing such integrity rules; It means those constraints are available to the optimiser to improve query peformance; The constraints are also available to development tools and users who consume the data and need to interpret its structure.