The Artima Developer Community
Articles | News | Weblogs | Books | Forums
Artima Forums | Articles | Weblogs | Java Answers | News
Sponsored Link •

Weblogs Forum
Database Constraints: Use Them or Lose Them?

56 replies on 4 pages. Most recent reply: Oct 8, 2009 9:07 AM by John Zabroski

Welcome Guest
  Sign In

Go back to the topic listing  Back to Topic List Click to reply to this topic  Reply to this Topic Click to search messages in this forum  Search Forum Click for a threaded view of the topic  Threaded View   
Previous Topic   Next Topic
Flat View: This topic has 56 replies on 4 pages [ « | 1 2 3 4 | » ]
Rinie Kervel

Posts: 26
Nickname: rinie
Registered: Oct, 2005

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 26, 2009 2:44 AM
Reply to this message Reply
Advertisement
> Most
> ORM tools force you to also configure these things such
> that you treat your class model as tokens to blit your
> data stream on.
>
> Hibernate/NHibernate has all kinds of knobs you can turn
> to blit things in different ways.
>
> I'm not criticizing Hibernate or Rails or whatever. I'm
> just explaining, programmatically, how these things work
> and what happens when you use them. People get rich using
> these tools, so it's not like you can't.
I know, but why do most ORMs (and Artima discussions ;) ) assume you always start with the object not with the RDBMS? I would almost learn python for SQLAlchemy that seems to enable going back and forth between an object view and a database view.

g. strak

Posts: 1
Nickname: gstrak
Registered: Sep, 2009

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 26, 2009 6:27 AM
Reply to this message Reply
So, can anyone show how is it possible to implement integrity constraints (e.g. foreign key constraints) in application?

You should consider that we are referring to multiuser applications. How are you going to deal with the concurrency issues? How are you going to test your code for the multiuser scenarios?

You must really be top class programmers, as you seem to be suggesting that you can do it better in the application than the DBMS, that is your code does all the necessary checks, leaves the database in a consistent state, manages all the necessary locking and has better performance.

I`d be glad if anyone of you could provide some code (in your preferred language and DBMS) to prove his point...

James Watson

Posts: 2024
Nickname: watson
Registered: Sep, 2005

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 26, 2009 2:54 PM
Reply to this message Reply
> So, can anyone show how is it possible to implement
> integrity constraints (e.g. foreign key constraints) in
> application?

I don't know about everyone else but foreign keys are one type of constraint that I do use. But that's pretty basic. I don't think that's what the discussion was about. I could be wrong, I suppose.

John Zabroski

Posts: 272
Nickname: zbo
Registered: Jan, 2007

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 28, 2009 7:42 AM
Reply to this message Reply
> I know, but why do most ORMs (and Artima discussions ;) )
> assume you always start with the object not with the RDBMS?
> I would almost learn python for SQLAlchemy that seems to
> enable going back and forth between an object view and a
> database view.

You have to ask yourself why you are going back and forth between an object view and database view.

An object view is a subset of the database view. It may not be a proper subset, but it is a subset.

I think asking this isn't fruitful. You're now manually merging things as part of your day-to-day integration process. Personally what I prefer to see is the ability to sink/hoist model concepts into various tiers. Doing this wit Plain Old Objects <-> Plain Old SQL strictly isn't realistic even if possible. You'd need parsers for every language your company uses.

Here's an example of a security constraint that's not very expressive in a modern web framework like Rails or ASP.NET MVC: authorization. In ASP.NET MVC, and at least early versions of Rails (have not been paying attention recently), authorization is tightly coupled to access privileges. This is such a stupid way to encourage people to do security the bad way. See, for example, ASP.NET Action filter AuthorizeAttribute http://msdn.microsoft.com/en-us/library/system.web.mvc.authorizeattribute%28VS.100%29.aspx

The intent behind this 'filter' pattern is good, but look what it does. In order to decide whether to give access privileges to something, you have to do it at the controller or action level. What happens when you need to change your security model? Now the fact you've created a spring force relationship between your privileges and account authorization is clear. Also, to visualize what functionality Suzy Creamcheese has access to, you've got to load a compiled .NET assembly and do reflection on it and pretty-print those details to a GUI somehow.

John Zabroski

Posts: 272
Nickname: zbo
Registered: Jan, 2007

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 28, 2009 8:08 AM
Reply to this message Reply
James, I think he was referring to my comments about possibly dropping foreign key constraints due to write-heavy loads requiring extra contention at the database-level while constraints are checked to make sure the database is consistent.

> So, can anyone show how is it possible to implement
> integrity constraints (e.g. foreign key constraints) in
> application?

The FOREIGN KEY keyword is only one way to guarantee a special form of uniqueness constraint. It is confused with the much more general 'foreign key' *pattern* Codd discusses in his earlier papers. The keyword approach is by far the simplest approach and generally will have the best performance. Yet, the whole resaon we have only one FOREIGN KEY constraint is because doing constraint checks cost CPU cycles, so the "foreign key pattern" says to pick one key out of all your candidate keys to serve as the key for constraint checking.

At larger scales, to maintain the keyword approach, you can start using things like shard memberships. However, to get this right, you still need some sort of "master control process" that knows where data should be geolocated. -- Of course, if you ever need to analyze the data on two shards, you may see performance degradation and even total freeze-up of your database due to performing a distributed join. This is where most people in the real world run into performance problems: just-in-time analytics for distributed systems. "Same day service" is THE big business force pushing enterprise consumers to do various performance tricks.

Choy Rim

Posts: 3
Nickname: choyrim
Registered: Apr, 2006

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 29, 2009 12:21 AM
Reply to this message Reply
Use them if at all possible. Not only do they help protect the data, they can help improve query performance. The rdbms query optimizer generates better plans if it has better info about your data. I'm just borrowing the words of Tom Kyte (of Oracle fame). I've tried out the samples he gives and he's made a believer out of me.

Most of the time, query performance is not the real issue. The real problem is schema management over the life of the development project. Your tools will eventually dictate whether it is practical to use constraints or not. If constraints force changes to require more release cycles or make iterative changes impossible, then in all likelihood you'll abandon constraints to maintain development velocity. Then again, you could also get better tools.

Jyoti Namjoshi

Posts: 1
Nickname: jyotinamjo
Registered: Sep, 2009

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 29, 2009 12:28 AM
Reply to this message Reply
It would be useful to have an ability to specify constraints in a standardized representation (the way XML offers) that is portable across databases.

The constraints that are expected to apply across applications or that database designer wants necessarily enforced across all uses of data, should be at or closer to the database layer, as that forms the common shared layer. Any constraints that are specialized to the other software layers that retrieve or manipulate data from the database should be at respective software layers.

Bruno Vernay

Posts: 4
Nickname: brunov
Registered: Sep, 2009

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 29, 2009 4:01 AM
Reply to this message Reply
@ g. strak
You could listen to "Software Engineering Radio Episode 109: eBay's Architecture Principles with Randy Shoup" http://www.se-radio.net/podcast/2008-09/episode-109-ebay039s-architecture-principles-randy-shoup

Constraints can be delayed if they only leads to acceptable errors for some users sometimes. And the overall system will behave better with integrity checked in the application afterward.
Now, the eBay case may not be applicable everywhere.

James Watson

Posts: 2024
Nickname: watson
Registered: Sep, 2005

Re: Database Constraints: Use Them or Lose Them? Posted: Sep 29, 2009 8:21 AM
Reply to this message Reply
> James, I think he was referring to my comments about
> possibly dropping foreign key constraints due to
> write-heavy loads requiring extra contention at the
> database-level while constraints are checked to make sure
> the database is consistent.

I see. I've had this kind of issue in message oriented systems. I think most databases allow for null or non-connected foreign keys. You're on your own to link them later, of course.

Ravi Venkataraman

Posts: 80
Nickname: raviv
Registered: Sep, 2004

Re: Database Constraints: Use Them or Lose Them? Posted: Oct 2, 2009 5:41 AM
Reply to this message Reply
> > For instance, setting a
> > constraint so that the <code>salary</code> field can't
> > accept a value less than 5000 and greater than 1000000
> > will cause a database to return an error code when
>
> This value seems like a 'business logic' constraint, that
> may change in time without modifications to the database
> schema.
> (More a data entry min/max validation that should not be
> in code either).
>
This simple example can easily be handled to avoid duplication by creating a table in the database layer with columns min_salary, max_salary, effective_from and effective_thru. The application can query the database for these values, cache these values, and everything is taken care of. DRY is not violated because the fact of the minimum salary and maximum salary with applicable dates is kept in only one place, the database. The application layers don't have these values but get them from the database when needed. There is no performance issue because the data is cached at the application layer.

That said, it would be nice if the database would let me map errors resulting from check constraints to specific error messages. While Oracle does so for procedural code like triggers and packages, it does not permit this for check constraints as far as I know.

James Watson

Posts: 2024
Nickname: watson
Registered: Sep, 2005

Re: Database Constraints: Use Them or Lose Them? Posted: Oct 2, 2009 6:21 AM
Reply to this message Reply
> This simple example can easily be handled to avoid
> duplication by creating a table in the database layer with
> columns min_salary, max_salary, effective_from and
> effective_thru. The application can query the database for
> these values, cache these values, and everything is taken
> care of. DRY is not violated because the fact of the
> minimum salary and maximum salary with applicable dates is
> kept in only one place, the database. The application
> layers don't have these values but get them from the
> database when needed. There is no performance issue
> because the data is cached at the application layer.

In the context of this discussion, that's implementing the constraints in the application layer, not the database.

robert young

Posts: 361
Nickname: funbunny
Registered: Sep, 2003

Re: Database Constraints: Use Them or Lose Them? Posted: Oct 2, 2009 7:41 AM
Reply to this message Reply
> That said, it would be nice if the database would let
> let me map errors resulting from check constraints to
> specific error messages.

For databases which correctly implement information_schema (and if not, a layer for each database supported by the application; I've never worked on one that did so for more than 2), error messages for errors (with runtime parameters filled in with a function) assigned to a table. Said table is joined during querying, and said table is assigned to its own buffers, of sufficient size as to remain resident.

It's a bit of work to set up the first time, but maintenance thenceforth is simple. You never have to write message handling in any application code. Just call the function as part of queries.

robert young

Posts: 361
Nickname: funbunny
Registered: Sep, 2003

Re: Database Constraints: Use Them or Lose Them? Posted: Oct 2, 2009 8:04 AM
Reply to this message Reply
> > This simple example can easily be handled to avoid
> > duplication by creating a table in the database layer
> with
> > columns min_salary, max_salary, effective_from and
> > effective_thru. The application can query the database
> for
> > these values, cache these values, and everything is
> taken
> > care of. DRY is not violated because the fact of the
> > minimum salary and maximum salary with applicable dates
> is
> > kept in only one place, the database. The application
> > layers don't have these values but get them from the
> > database when needed. There is no performance issue
> > because the data is cached at the application layer.
>
> In the context of this discussion, that's implementing the
> constraints in the application layer, not the database.

I half agree. The issues are simply whether:
- data gets corrupted on the wire, thus application editing is not sufficient
- constraints get updated, thus both the application(s) and the database have to be maintained
- there is the ancillary issue of whether constraint changes will be applied to existing data, or only apply forward going. for this one, it has to be done in the database

Providing client side editing as a helping hand is fine and good. Such editing should be driven by the database constraints, however defined there. Generating (manually or automated) client side code to manifest those constraints leads to the conflict. The coders want the application(s) to be the constraints of record. Database wonks want them in the database, as Dr. Codd intended; this approach foreshadowed the OO design principal, which is to say data and its methods are co-located. That OO folk elide this hypocrisy is a source of constant amusement.

Should we reach the nirvana of infinite bandwidth, the coder folk no longer have a leg to stand on; we have a GUI-fied VT-100 reading the keyboard clicks, asking the database whether the click is good or bad, and proceeding accordingly. Yum.

Mark Thornton

Posts: 275
Nickname: mthornton
Registered: Oct, 2005

Re: Database Constraints: Use Them or Lose Them? Posted: Oct 2, 2009 8:29 AM
Reply to this message Reply
> constraints leads to the conflict. The coders want the
> application(s) to be the constraints of record. Database
> wonks want them in the database, as Dr. Codd intended;
> this approach foreshadowed the OO design principal, which
> is to say data and its methods are co-located. That OO
> folk elide this hypocrisy is a source of constant
> amusement.
And if your methods are too complex to be realistically implemented within the database where does that leave you?

robert young

Posts: 361
Nickname: funbunny
Registered: Sep, 2003

Re: Database Constraints: Use Them or Lose Them? Posted: Oct 2, 2009 9:02 AM
Reply to this message Reply
> > constraints leads to the conflict. The coders want the
> > application(s) to be the constraints of record.
> Database
> > wonks want them in the database, as Dr. Codd intended;
> > this approach foreshadowed the OO design principal,
> which
> > is to say data and its methods are co-located. That OO
> > folk elide this hypocrisy is a source of constant
> > amusement.
> And if your methods are too complex to be realistically
> implemented within the database where does that leave you?

Under what circumstances? For a BCNF schema, all data is simply a join away. With the SSD multi-machines of today, not an issue. Declarative constraints can do anything else I've ever needed. Remember: all data in the database is determined to be correct, either by the real world (a fact which is a "master file" fact; and not testable either in code or the data), or by other data. Declarative referential integrity.

But you're begging the question. If you have a simplistic flat-file image in the datastore, then constraints become impossible without gobs of code. But you want gobs of code, because you write code. I can't change that, except to fire you. If your management can be convinced to continue with that mode of development, then you're safe, I guess. But that light at the end of the tunnel is the Orient Express.

Flat View: This topic has 56 replies on 4 pages [ « | 1  2  3  4 | » ]
Topic: Speaking at FOSDEM Previous Topic   Next Topic Topic: Speaking At Developer Day in Boulder
Sponsored Links


Google
  Web Artima.com   

Copyright © 1996-2019 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use