Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: What's your preferred way of ensuring complex database integrity?

news.ycombinator.com

Post reply on HN
Print view View original

31–40 of 58 posts

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#31

Post by floriol »

mistrial9 wrote: post #29
clavalle wrote: post #26

If data needs to agree PKs and FKs are a way to go. I avoid UNIQUES unless absolutely necessary since they are not structural. Reference tables for things that don't change or change very seldom are a valuable tool if data across other tables must agree. Check constraints otherwise. But that's just the bones -- the last line of defense. Having a framework that can push such constraints through the application is very…

can you say more about "reference tables" ?

I think he means the following, illustrated by example: instead of storing group_name in the users table for each user, you only reference a pk: group_id, and have a separate table groups with the actual name. So the actual value "admin" will only be there once, defending against typos etc.

So basically a normalised database

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#32

Post by clavalle »

mistrial9 wrote: post #29
clavalle wrote: post #26

If data needs to agree PKs and FKs are a way to go. I avoid UNIQUES unless absolutely necessary since they are not structural. Reference tables for things that don't change or change very seldom are a valuable tool if data across other tables must agree. Check constraints otherwise. But that's just the bones -- the last line of defense. Having a framework that can push such constraints through the application is very…

can you say more about "reference tables" ?

Sure.

I've also heard them called 'Verification Tables'. They are basically check constraints but stored in a table for reference. If you are using a check constraint in more than one place and it goes beyond a simple 'Y', 'N' it's probably worth thinking about using a reference table instead.

These tables will generally not be changed very often and can be considered 'read only' for the most part. At their most pure, an application can count on certain values to exist in a reference table and might even verify this at startup or populate them with application properties. They could be application level properties if not for the referential integrity needs.

It is often useful, however, to have some reference tables that are only 'mostly read only'. Changeable but largely static.

As a trivial example, a table like state_abbreviations. It is a finite list of data that is unlikely to change.

Data that comes from the government is a pretty good candidate for this kind of thing. It changes, but not often, and you don't want to give users the option to put whatever they want. e.g. Federal School Codes.

Other good candidates are structural aspects of your application. Say, a list of modules that users can navigate to to perform a certain job function. Perhaps you want to give them the ability to compose security roles around those and then apply those roles to users. Also, say, you want to use the same names for menus or reference them elsewhere in a consistent way, a reference table might be the way to go.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#33

Post by mindcrime »

If I'm in an environment where I'm using an RDBMS, then yeah, I mostly try to rely on the built in support for referential integrity that is supplied by the database. If there's a rule that's too complex to express that way, I would probably just write code (in Java, Groovy, Python, whatever I'm working with) to verify things.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#34

Post by chatmasta »

clavalle wrote: post #27
jhanschoo wrote: post #4

As mentioned by the other comment, it's standard to use database triggers to ensure consistency. There are two major problems with dealing with them in the application side 1. You can no longer guarantee consistency. 2. In the case where you need to do some things procedurally so that your application can no longer make a yes/no query to determine if a constraint is violated but need to make use of intermediate resul…

>As mentioned by the other comment, it's standard to use database triggers to ensure consistency. shudder Triggers are almost always the wrong level to handle consistency. It flies in the face of 'fail fast' if you need to actually handle problems of consistency and if you need 'last line' protection, why not bake it into the structure instead?

Foregoing triggers in favor of validating data in the application layer can be dangerous. The problem is that you need to re-implement data validation on every path to the database. If you've got a single application server, and it's the only client that ever writes to the database, this might work. But as soon as you add another service with write capability, you need to re-implement the validation logic. What if you forget?

If you put the validation in the database, any application can leverage it. If you want to fail fast, you can still do validation in the application layer while relying on the DB layer as the ultimate validator.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#35

Post by yashap »

I’ve yet to encounter DB level integrity problems that aren’t solved by a well designed, normalized schema combined with proper use of transactions, PKs, FKs, unique and not null constraints, etc.

These problems probably exist, but they’re very rare. Much more rare than people THINKING they have a problem like this, hacking around it with poorly implemented application code, and ending up with data that has integrity issues, when it could have been solved well using the DB properly.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#36

Post by sokoloff »

yashap wrote: post #35

I’ve yet to encounter DB level integrity problems that aren’t solved by a well designed, normalized schema combined with proper use of transactions, PKs, FKs, unique and not null constraints, etc. These problems probably exist, but they’re very rare. Much more rare than people THINKING they have a problem like this, hacking around it with poorly implemented application code, and ending up with data that has integrity…

We ran a large e-commerce company main site solely on a single (with HA) SQL Server database. (We’ve since added other DBs, and always had other reporting and analysis systems, but we were doing over $1BB/yr on a single transactional server.)

There’s no “room” there for foreign key constraints nor pervasive use of transactions. We used limited transactions in payments area only (and eventually even retired the last of those) and no foreign keys.

Reasonably careful coding, doing a lot of work in stored procedures, and “fix it if it breaks” was the order of the day. I won’t claim it was academically pure, but it sure did work based on the scoreboard (cash register).

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#38

Post by GauntletWizard »

Make your complex database error resistant. Write documents for what it means when references are missing. Do Garbage Collection rather than ON_DELETE triggers. Do everything that SQL doesn't want you to do.

SQL requires defining integrity in advance, and if your database gets corrupted, there is little hope of recover. Cleaning up your data is nearly impossible because your schemas and logic are tightly coupled.

Writing garbage collection scripts is easy if you make a practice of it, and makes you think about your schema in the ways that are important and that the ease of triggers hides from you. You can monitor and metricize it in ways that you can't with triggers.

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#39

Post by paulddraper »

clavalle wrote: post #26

If data needs to agree PKs and FKs are a way to go. I avoid UNIQUES unless absolutely necessary since they are not structural. Reference tables for things that don't change or change very seldom are a valuable tool if data across other tables must agree. Check constraints otherwise. But that's just the bones -- the last line of defense. Having a framework that can push such constraints through the application is very…

UNIQUES are definitely "structural".

In fact, your the foreign side of the FK needs to be covered by a unique constraint (including PK, which is simply a UNIQUE with slight differences).

Re: Ask HN: What's your preferred way of ensuring complex database integrity?

#40

Post by neuland »

chatmasta wrote: post #34
clavalle wrote: post #27

Earlier quoted context omitted.

>As mentioned by the other comment, it's standard to use database triggers to ensure consistency. shudder Triggers are almost always the wrong level to handle consistency. It flies in the face of 'fail fast' if you need to actually handle problems of consistency and if you need 'last line' protection, why not bake it into the structure instead?

Foregoing triggers in favor of validating data in the application layer can be dangerous. The problem is that you need to re-implement data validation on every path to the database. If you've got a single application server, and it's the only client that ever writes to the database, this might work. But as soon as you add another service with write capability, you need to re-implement the validation logic. What if yo…

It’s generally not good for services to share write privileges to a database for this very reason.

Also you can end up with services communicating between an implicit database api rather than a well established rest or rpc (or whatever) api.

Post reply on HN