not using foreign keys?
space...@mailinator.com
i have a question -- is the practice of using FKs to maintain
referential integrity an argued (for/against) practice? im now on an
enterprise banking project, and the db designer does not use FKs, he
feels it creates a "closed system", and prefers to have the coders
manage constraints via procs.
id never seen this before, but he said its an age-old argument (w/
merits) and one he adheres to. is this so, or is it an unusual
personal preference?
i dont do much of the db work, but im just curious.
thanks
sm
fitzj...@cox.net
The developers cannot do as well in managing 'constrained' data as the
Oracle db engine can, period. The read consistency model prevents
session-level code from 'seeing' uncommitted inserts/updates/deletes
thus hindering the abilities of any developer to properly 'constrain'
data.
As far as I'm concerned there are no 'merits' to letting developers
attempt to 'manage' referential integrity as it only creates a mess
too convoluted to undo at a later time when the folly of this
'decision' is realised.
David Fitzjarrell
Frank van Bortel
Hash: SHA1
Fire him
- --
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFGZFtDLw8L4IAs830RAoghAJ4/nc2HV8Jg7cq6m3uHETiETZL0zQCfcdzq
h42w+fWu9kPha2oMkRBsUkI=
=TpE8
-----END PGP SIGNATURE-----
Walt
>> i have a question -- is the practice of using FKs to maintain
>> referential integrity an argued (for/against) practice? im now on an
>> enterprise banking project, and the db designer does not use FKs, he
>> feels it creates a "closed system", and prefers to have the coders
>> manage constraints via procs.
>>
>> id never seen this before, but he said its an age-old argument (w/
>> merits) and one he adheres to. is this so, or is it an unusual
>> personal preference?
>>
>> i dont do much of the db work, but im just curious.
> The developers cannot do as well in managing 'constrained' data as the
> Oracle db engine can, period. The read consistency model prevents
> session-level code from 'seeing' uncommitted inserts/updates/deletes
> thus hindering the abilities of any developer to properly 'constrain'
> data.
>
> As far as I'm concerned there are no 'merits' to letting developers
> attempt to 'manage' referential integrity as it only creates a mess
> too convoluted to undo at a later time when the folly of this
> 'decision' is realised.
Agreed. This *should* be a settled argument but people, being people,
will persist in doing things the wrong way. One of our developers went
to Microsoft dot net training recently, and the trainer recommended not
having any constraints in the database because it slows down the
development process.
Idiot.
Fortunately, our developer knew better than this and didn't listen to
this startlingly bad advice.
Use constraints. Apply them early in the development process (i.e.
before you write any code). The best way to prevent messes is to not let
bad data get into your db in the first place. Leaving data integrity up
to the app is a sure recipe for acquiring bad data.
//Walt
space...@mailinator.com
Frank van Bortel wrote:
> Fire him
actually, hes my boss :)
i may diagree w/ the decision, but at this point i just have to work
with it.
sm
Walt
No you don't. You're not an indentured servant are you? You can always
find another job. Maybe even working for someone who knows what he's
doing.
//Walt
sybr...@hccnet.nl
wrote:
> Maybe even working for someone who knows what he's
>doing.
My impression is those dinosaurs will be very hard to find soon, as
current IT personell only learns to hit buttons, and never learns the
theoretical concepts.
Edsgar Dijkstra is turning himself in his grave everyday, but that
doesn't help.
--
Sybrand Bakker
Senior Oracle DBA
joel garry
See http://www.dbazine.com/oracle/or-articles/jlewis19 and
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/data_int.htm
so you know what to do when your boss gets fired.
May we know which bank so we can avoid it?
jg
--
@home.com is bogus.
DST problems were worse outside the db. Bad enough as they were:
http://www.signonsandiego.com/uniontrib/20070602/news_1b2oracle.html
joel garry
> On Mon, 04 Jun 2007 17:18:46 -0400, Walt <walt_ask...@SHOESyahoo.com>
> wrote:
>
> > Maybe even working for someone who knows what he's
> >doing.
>
> My impression is those dinosaurs will be very hard to find soon, as
> current IT personell only learns to hit buttons, and never learns the
> theoretical concepts.
> Edsgar Dijkstra is turning himself in his grave everyday, but that
> doesn't help.
>
The sad thing is, it really won't make much difference. As long as it
seems to work, and the fraud stuff is kept mostly out of sight.
We just have to laugh as billions of dollars get lost.
jg
--
@home.com is bogus.
"If being a pest results in being poisoned, then I don't have long to
live." - San Diego City Attorney Michael Aguirre
space...@mailinator.com
> No you don't. You're not an indentured servant are you? You can always
> find another job.
i dont plan on quitting my job over this, thanks. some markets are
tougher than others, and its not a deal breaker for me.
sm
space...@mailinator.com
> May we know which bank so we can avoid it?
well its not a public facing system, just an app for helping our
people process business paperwork. important to us, but not in the
grand scheme of things.
sm
space...@mailinator.com
> @home.com is bogus.
> "If being a pest results in being poisoned, then I don't have long to
> live." - San Diego City Attorney Michael Aguirre
...used to live in san diego until recently. what is your sig in
reference to? just curious.
sm
sybr...@hccnet.nl
So much for your professionalism.
Martin T.
Hmmm ... if you have a halfway decent standing with this boss person,
you might want to show him this thread. Maybe he will see the
light :-)
cheers,
Martin
Martijn Tonies
Tell your boss that the ONLY way to avoid get invalid data
into your database is to have proper constraints.
It IS the way to go.
--
Martijn Tonies
Database Workbench - development tool for Oracle, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
space...@mailinator.com
> >i dont plan on quitting my job over this, thanks. some markets are
> >tougher than others, and its not a deal breaker for me.
>
> So much for your professionalism.
lol - give me a fucking break. youre seriously expecting me to quit my
app dev job (as stated, im not the dba) because of a project im
working on that doesnt use FKs? you need to get out more.
i work to live, and my life has beautiful things in it that require a
steady gig. your mileage may vary.
sm
space...@mailinator.com
> Hmmm ... if you have a halfway decent standing with this boss person,
> you might want to show him this thread. Maybe he will see the
> light :-)
or maybe he'll get pissed. or maybe just tell me not to worry about
it, since hes been here 12 years and ive been here a few months, and
its not my realm of ownership. yep, those things wouldnt happen in an
ideal world, but neither would war, famine, industrial pollution,
etc..
....just looking for the answer to a technical question, for my own
curiosity, and not life pointers from randoms :). thanks tho, people.
sm
Malcolm Dew-Jones
: hello,
: i have a question -- is the practice of using FKs to maintain
: referential integrity an argued (for/against) practice?
In addition to anything else, FK's and other constraints are an excellent
way to document the rules and relationships of the system.
Much easier to read and understand than the code that interacts with a
user.
sybr...@hccnet.nl
>lol - give me a fucking break. youre seriously expecting me to quit my
>app dev job (as stated, im not the dba) because of a project im
>working on that doesnt use FKs? you need to get out more.
>
>i work to live, and my life has beautiful things in it that require a
>steady gig. your mileage may vary.
>
Let's phrase it this way: If you, in a job interview, would admit to
have been on a job where they didn't use foreign keys, I definitely
wouldn't hire you.
Other than that, I believe those not using foreign keys need to be
summarily shot. It would make people like me less suffer from their
incompetency.
Frank van Bortel
Hash: SHA1
joel garry wrote:
>
> We just have to laugh as billions of dollars get lost.
>
They always seem to get lost "in the wrong direction".
Never into my accounts :(
- --
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFGZbWbLw8L4IAs830RAhNRAKCLb+PmYYczies32USLYMopKzp4mgCfRHBc
h1EHRzTXcTl3Vwq6brfQV1k=
=ETl4
-----END PGP SIGNATURE-----
joel garry
http://www.signonsandiego.com/news/metro/braun/20070603-9999-1m3braun.html
:-)
jg
--
@home.com is bogus.
Now if I could just get rid of the squirrels in my yard... and the
bunnies... and the weasels... and the coyotes... and the strange
things that look like langostino that crawl into my spa and die when
it rains...
Brian Peasland
Sounds like its time to get someone else on the project. Always let the
Oracle engine enforce referential integrity through FK constraints.
Doing so with a stored proc is not the way to go. How does the developer
guarantee that the proc gets run when a row of data is INSERTed into the
table? With a FK constraint, you don't have to worry about that.
A worse approach would be to let the application handle the referential
integrity. But don't get me started on that....
HTH,
Brian
--
===================================================================
Brian Peasland
d...@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
--
Posted via a free Usenet account from http://www.teranews.com
Robert Klemme
:-) Sybrand, you are an idealist!
Kind regards
robert
PS: don't get me wrong, I didn't say this is a bad thing.
sybrandb
> On 05.06.2007 20:58, sybra...@hccnet.nl wrote:
>
> > On Tue, 05 Jun 2007 08:06:06 -0700, spacemar...@mailinator.com wrote:
>
> >> lol - give me a fucking break. youre seriously expecting me to quit my
> >> app dev job (as stated, im not the dba) because of a project im
> >> working on that doesnt use FKs? you need to get out more.
>
> >> i work to live, and my life has beautiful things in it that require a
> >> steady gig. your mileage may vary.
>
> > Let's phrase it this way: If you, in a job interview, would admit to
> > have been on a job where they didn't use foreign keys, I definitely
> > wouldn't hire you.
> > Other than that, I believe those not using foreign keys need to be
> > summarily shot. It would make people like me less suffer from their
> > incompetency.
>
> :-) Sybrand, you are an idealist!
>
> Kind regards
>
> robert
>
> PS: don't get me wrong, I didn't say this is a bad thing.
Almost true: I'm an idealist AND a perfectionist, and I can't stand
the idiots that don't know how to do things RIGHT.
Robert Klemme
We should probably take this to alt.philosophy but... Isn't a
perfectionist a special kind of idealist? Or is it the other way round?
Hm... I'll have to think that over.
Btw, I find people even more annoying who do not know how to do things
right and /who do not care/. In this particular case it seems that SM
(what a nice pair of initials, might be part of the explanation :-)) is
not in the position to change things although he would like to. Please
give him a break.
Kind regards
robert
bugbear
> On Tue, 05 Jun 2007 08:06:06 -0700, space...@mailinator.com wrote:
>
>> lol - give me a fucking break. youre seriously expecting me to quit my
>> app dev job (as stated, im not the dba) because of a project im
>> working on that doesnt use FKs? you need to get out more.
>>
>> i work to live, and my life has beautiful things in it that require a
>> steady gig. your mileage may vary.
>>
> Let's phrase it this way: If you, in a job interview, would admit to
> have been on a job where they didn't use foreign keys, I definitely
> wouldn't hire you.
Odd. What about somebody who was "on a job where they didn't use foreign keys",
and decided (after a few days) that this was dreadful and quit.
Sounds like a positive recommendation for the somebody ;-)
BugBear
bugbear
> hello,
>
> i have a question -- is the practice of using FKs to maintain
> referential integrity an argued (for/against) practice? im now on an
> enterprise banking project, and the db designer does not use FKs, he
> feels it creates a "closed system", and prefers to have the coders
> manage constraints via procs.
>
> id never seen this before, but he said its an age-old argument (w/
> merits) and one he adheres to. is this so, or is it an unusual
> personal preference?
Without causing major privacy issues, what can you tell
us about this "db designer"?
Background?
Age?
Training?
Because this (as others have said) is MOST ODD.
BugBear
Walt
> space...@mailinator.com wrote:
>> i have a question -- is the practice of using FKs to maintain
>> referential integrity an argued (for/against) practice? im now on an
>> enterprise banking project, and the db designer does not use FKs, he
>> feels it creates a "closed system", and prefers to have the coders
>> manage constraints via procs.
>>
>> id never seen this before, but he said its an age-old argument (w/
>> merits) and one he adheres to. is this so, or is it an unusual
>> personal preference?
>
> Sounds like its time to get someone else on the project. Always let the
> Oracle engine enforce referential integrity through FK constraints.
> Doing so with a stored proc is not the way to go. How does the developer
> guarantee that the proc gets run when a row of data is INSERTed into the
> table?
There are ways. Create procs that handle insert/update/delete and
grant execute rights to the app, without giving the app
insert/update/delete rights to the table itself. With this approach,
the only way for the app to insert/update/delete is through the procs.
(Of course, this doesn't prevent the table owner or a DBA from breaking
the data integrity rules, just the app - perhaps this is what SM's boss
is alluding to with the "closed system" phrase: he wants to be able to
break the rules if it suits him)
Now, this doesn't argue against FK, it's just a good approach when there
are integrity rules you want to enforce that go beyond what you can do
with the standard built-in constraints.
In particular, procs and triggers cannot see uncommitted data in other
sessions, so in a multi-user environment they are not enough to ensure
integrity.
For instance: User A wants to insert a child record that references a
parent and the proc checks to make sure it's there - so far so good.
User B wants to delete a parent record, and the proc checks to make sure
it has no dependent records. If both of them are doing this at the same
time for the same parent, both checks will pass, but you'll wind up with
a child with no parent. A FK prevents this (one of the users will
receive an error on commit) , all the procs in the world won't.
Without the FK constraint, you have a conduit for *bad data*;
single-user testing will never produce the bad results, it will only
happen with multiple users (and since far too many apps are released
without multi-user testing, this problem may not appear until it's in
production. oops. ) And /when/ (not if) it happens the coders will
scratch their heads and say "but that can't happen, we have code that
prevents it, watch this test...".
SM, to put it bluntly, your boss doesn't understand data design. You
are in a world of hurt. Best of luck, you'll need it.
> A worse approach would be to let the application handle the referential
> integrity. But don't get me started on that....
Agreed. Don't get me started either....
//Walt
DA Morgan
> A worse approach would be to let the application handle the referential
> integrity. But don't get me started on that....
What we somehow need to educate these front-end developers about is that
front-ends are technically incapable of enforcing referential integrity.
They give the appearance of doing so but actually do not and can not.
No front-end designable is capable of keeping someone accessing the
data with any other tool from destroying it be that batch loads with
SQL*Loader, DBAs with SQL*Plus, or a host of tools that use ODBC or JDBC.
It is really a question of education and we need to educate them about
those factors we think of as basic starting with the fact that data has
value.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Walt
> Brian Peasland wrote:
>
>> A worse approach would be to let the application handle the
>> referential integrity. But don't get me started on that....
>
> What we somehow need to educate these front-end developers about is that
> front-ends are technically incapable of enforcing referential integrity.
>
> They give the appearance of doing so but actually do not and can not.
> No front-end designable is capable of keeping someone accessing the
> data with any other tool from destroying it be that batch loads with
> SQL*Loader, DBAs with SQL*Plus, or a host of tools that use ODBC or JDBC.
Well said.
I think in this case we're looking at a data designer who is *planning*
on wanking the data with one of those tools. Iron clad data integrity
rules make for a "closed system" that you can't bend to your whim using
sql*plus or your chainsaw of choice.
What's needed here is a DBA who understands the value of data, and also
understands that he shouldn't be allowed to break the rules of data
integrity any more than the lowly applications programmers.
//Walt
joel garry
Playing devil's advocate for a moment, I see that there is some
relatively low level of rule where it becomes simply beyond the
capabilities of the database to constrain. So, wouldn't there be some
design value to not having two completely different constraint
mechanisms (one in the database that needs to be undone at times
anyways, and the one that can handle more complex rules)?
(Lest anyone misunderstand, I'm with the advocates of "as much in the
db as possible." However, not 5 minutes ago I was working on some ETL
stuff that requires the same data to be viewed with both historical
and current integrity constraints. Yes, it is wanked with
sql*loader.)
jg
--
@home.com is bogus.
Torture that data until it gives you what you want! Bwahahaha!
DA Morgan
> Yes, it is wanked with > sql*loader.)
Pretty much says it all doesn't it. <g>
Galen Boyer
> We should probably take this to alt.philosophy
alt.databases.oracle.church is my vote for the name of this group.
--
Galen Boyer
Martijn Tonies
> >lol - give me a fucking break. youre seriously expecting me to quit my
> >app dev job (as stated, im not the dba) because of a project im
> >working on that doesnt use FKs? you need to get out more.
> >
> >i work to live, and my life has beautiful things in it that require a
> >steady gig. your mileage may vary.
> >
> Let's phrase it this way: If you, in a job interview, would admit to
> have been on a job where they didn't use foreign keys, I definitely
> wouldn't hire you.
> Other than that, I believe those not using foreign keys need to be
> summarily shot. It would make people like me less suffer from their
> incompetency.
You could give him credit for trying to pursuade his boss though.
I would say it's a step in the right direction.
Ed Prochak
Then why isn't the Historical data in a data warehouse instead of in
production?
To some extent I see where you are coming from, but that case just
shows that, even as flexible as Oracle is, there are limits. And that
is a different issue: managing changes in requirements over time.
We had a similar issue in our database recently in the code to purge
old old data. There was a program which deleted customers and their
vehicles if there was no activity after a certain period of time.
Trouble was the Vehicle data was needed to reprint the invoices. Our
system is fairly large and no one knew which application deleted the
vehicles. So in our latest release, I installed the FK constraint from
invoice header to the vehicle table. So we stopped the errant
application in its tracks. None of the programmers that have been here
a long time believed there was a bug in the apps.
So I say FK constraints provide a much larger benefit than they do a
hindrance.
Ed
(little improvements here, slowly but surely)
joel garry
Funny, you put your finger right on it! I'm not entirely sure
(actually, the purge routines are broken for the amount of data and
users don't have a performance issue so I haven't fixed them), but at
one point I got the gist that Oracle had tried to sell them a DW
solution right after totally messing them up with licensing (I think
an O partner had not paid O and just kept the money, among other
things). So I wasn't allowed to mention DW, and wound up writing an
ETL routine that basically creates them on demand for any given
floating time window. And of course, since they actually are given to
just a few secretaries to run, it eventually became "push this button
and several hundred spreadsheets will magically come out ready to
email." The DSS people have other issues and wind up stovepiped
anyways.
Also, you may recall around the turn of the century everything was
moving towards centralization as the distributive database movement
petered out, and Oracle was selling how new features would help
centralize everything to one db. HAHA! Now of course the next big
thing is Corporate Performance Management Working Against A Single
Source Of Truth That Spans From Past To Present And Has The Latest
Forecasts And Actions Plans For The Future With Access To On-Demand
Reports With Up-To-The-Moment Timeliness And Accuracy. Somehow gotten
through periodic MS-SQL sucks into cubes. The existing routines
continue with minor mods until that all gets worked out.
>
> To some extent I see where you are coming from, but that case just
> shows that, even as flexible as Oracle is, there are limits. And that
> is a different issue: managing changes in requirements over time.
>
> We had a similar issue in our database recently in the code to purge
> old old data. There was a program which deleted customers and their
> vehicles if there was no activity after a certain period of time.
> Trouble was the Vehicle data was needed to reprint the invoices. Our
> system is fairly large and no one knew which application deleted the
> vehicles. So in our latest release, I installed the FK constraint from
> invoice header to the vehicle table. So we stopped the errant
> application in its tracks. None of the programmers that have been here
> a long time believed there was a bug in the apps.
>
> So I say FK constraints provide a much larger benefit than they do a
> hindrance.
Actually, I have the worst, some constraints in the app, some in the
db, some in modifications to the app, some not there at all and can't
be because they'd break the app, and conflicting requirements.
Doesn't everybody? :-)
I recently went through an interesting "reprint invoices" exercise
too. The app had been modified to include what is currently on
backorder, reprinting later would have different stuff. Not so much
of a problem when the old one is in a file cabinet in the next room,
but when ordering and invoicing get separated by many miles and the
order people have to handle the customers inquiring about backorders
on the invoices... sorry, no flashback on this one. One of the
constraints is whether a customer is allowed to have backorders, which
of course can change over time and is subject to exceptions including
by line on the order... not to mention, ambiguity over what a
backorder really is...
>
> Ed
> (little improvements here, slowly but surely
The ratio of steps forward to slides backward keep changing... :-)
jg
--
@home.com is bogus.
"...food at the county fair will include fried chicken sandwiched
between Krispy Kreme donuts."
"That can't be real."
"It is."
"Someone is growing their own."
"Tomatos?" - heard on radio.
William Robertson
Agreed, the self-documentation aspect (and the ability to use tools
that can read constraints and present users with related look-up
queries, ERDs etc) surely makes it easier to bring new developers and
analysts up to speed, saving the business time and money and reducing
mistakes.
Also worth mentioning is the fact that the optimizer can make use of
constraints in certain query transformations, and so constraints can
potentially improve query performance. I think Jonathan Lewis has
written about this.
Mark Harrison
> It is really a question of education and we need to educate them about
> those factors we think of as basic starting with the fact that data has
> value.
This is so strange. As a developer I love the idea that there
are some consistency guarantees. For example, when looping
over a table with FK constraints, I can grab the referred FK
row without having to have worry about the error-case that
the referred row is not there, saving such tons of code clutter.
But maybe these are the developers that wouldn't check for
that anyway. :-/
Cheers,
Mark
--
Mark Harrison
Pixar Animation Studios