Typical constraints in DDLs

Hi All,

I'm a little confused...I am architecting a datamart. I have created the conceptual, logical, and am now producing the physical. Part of the physical is adding constraints. What are some best practices in producing a physical model with constraints?

Thanks,
Krystal

What kind of model? Normalized or Dimensional?

What kind of constraints? Value, key?

Are you using surrogate keys assigned during ETL?

HI

Dimensional. Constraints so far are primary keys, foreign keys, and null. Yes I will have surrogate keys assigned during etl and some dimensions are slowly changing type 2.

Hope that answers your questions. Thanks!

My opinion (as others disagree) is, if you are assigning surrogate keys, you have already done the work that database key constraints do. So, there is no need to implement key constraints (PK's and FK's) in the the database. Implementing them only serves to slow down the load, and requires index structures that may or may not be useful (particularly with PK's on fact tables).

Most DBMS's will allow you to define FK's and PK's without enforcing them. They become documentary declarations (rather than constraints) that BI tools can use to determine joins and stuff. You need to check you doc's on the appropriate syntax.

Generally speaking, value constraints, other than allowing or not allowing nulls, are not used very much. Value checking and adjustment is usually handled in ETL.

Ah I see. Ok awesome. I wasnt planning on enforcing the FKs. But I was planning on enforcing the PKs. Your suggestion is to implement that data check in the ETL...like a error handler. Is that correct?


Thank you!

PK enforcement on dimensions is not a big deal, dimensions are usually much smaller than facts and much lower volume, so impact is minor. I would not declare a PK on facts.

And, yes, do data checking and cleansing in ETL. It allows you to control what happens if data is quite right. Value constraints in database simply cause an error and stop the update.

Thanks! This really helps!