Typical constraints in DDLs

View previous topic View next topic Go down

Typical constraints in DDLs

Post  kclark on Tue Oct 02, 2012 7:31 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Typical constraints in DDLs

Post  ngalemmo on Tue Oct 02, 2012 9:44 pm

What kind of model? Normalized or Dimensional?

What kind of constraints? Value, key?

Are you using surrogate keys assigned during ETL?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Typical constraints in DDLs

Post  kclark on Tue Oct 02, 2012 10:23 pm

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!

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Typical constraints in DDLs

Post  ngalemmo on Wed Oct 03, 2012 2:06 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Typical constraints in DDLs

Post  kclark on Wed Oct 03, 2012 6:14 am

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!

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Typical constraints in DDLs

Post  ngalemmo on Wed Oct 03, 2012 12:32 pm

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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Typical constraints in DDLs

Post  kclark on Wed Oct 03, 2012 7:07 pm

Thanks! This really helps!

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

Re: Typical constraints in DDLs

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum