Board index » delphi » Re: My Boss Hates Foreign Keys

2003-10-28 11:22:22 PM
delphi255
"coderx" <XXXX@XXXXX.COM>writes
...
It's called "referential integrity" for a reason! Your reports of already
orphaned data tells the tale.
I bet your boss used to be a Clipper programmer*... it sounds like he's
trying to use SQL Server as a collection of super-DBFs! ;)
--
Ray Marron
*I'm allowed to say that because I was/am one. Substitute FoxPro if
appropriate.
 

Add my vote to the others for foreign keys. Your boss is a danger to your
company's data and therefore to your company. When you leave, make sure your
boss's boss knows why.
Regarding tinyint, I don't know the interals of MS SQL Server, but in some
languages it is fastest to use the CPU's native size for integers, which is
usually 32 bits, so the unused bits don't need to be zeroed out before
comparisons and arithmetic operations. The database might be slightly
smaller with tinyints, but it might also be slightly slower.
A more consequential issue is column nullability. Some authorities (Joe
Celko comes to mind) discourage nullable columns because they complicate SQL
queries, especially conditional logic in queries. I use them for dates and
times, because I prefer null over an incorrect value, but I generally use
NOT NULL DEFAULT '' for strings and NOT NULL DEFAULT 0 for amounts. What
kind of situation is it where you want to allow nulls?
Richard
"coderx" <XXXX@XXXXX.COM>writes

Have him READ the HIPPA regs....
IMHO: He's up to something. I have built Health Claims processing systems and
by removal of data integrity constraints allows manipulation of data and
records outside of the standardized process which allows for possible
embezzlement and for denial of claims after the fact. both bad for business.
CYA Dept: Keep records of emails requesting the changes. This has nothing to
do with data design.
Keep in touch.
Doug
 

coderx writes:
You've already got a lot of good replies but I still feel the need to put my
2 cents in...
and unless you are going way overboard you are not going to notice the
difference in updates/inserts/deletes, and FKs are *supposed* to make it
harder to delete!
business rules belong. My answer to that is that there is a distinction that
can be made in most if not all cases between "business" rules and
"integrity" rules. IMO integrity rules belong in the database, business
rules (normally representing processes, not just simple constraints) belong
in the application or middle tier. Business processes are then simplified by
being able to depend on the database's integrity rules.
Counter to one other reply, I will say even in huge systems such as data
warehouse examples, integrity rules still belong in the database and are
even more crucial. While for the most part this consists in column
attributes (default or checked values, null constraints) and PKs/FKs, it
certainly can include triggers.
For stored procedures, I use them where there is a clear and meaningful
advantage for simplicity and/or performance, but I start by coding such
processes in the application and then moving them only if warranted.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

the place then don't use FK constraints!
If you really want to have some fun why not post your message over in
microsoft.public.sqlserver.programming Hopefully Joe Celko will see it <g>
--
Jim
 

"Jim Elden" <x@yz>writes 3f9ebdef$XXXX@XXXXX.COM...
not yet from Joe.
Cheers,
Ignacio
--
The strange part isn't so much that he had an accent. No accent was
detectable. It was just sounds and burbs and gurgles coming from him. He
was a like a chubby, old R2-D2.
- La Üter
 

XXXX@XXXXX.COM says...
to enforce proper logical rules, when it is so much easier to do it
oneself?
indexes a foreign key) where you get index skewing and it *_can_* be
better to enforce referential integrity using triggers and stuff, but
*_normally_* it is better to let the db do it.
BTW, keep those emails!
difficult) to delete records which will leave other data ophaned.
job better than the likes of Oracle/Microsoft/Borland who have spent
years writing systems to do this automatically - my hat goes off to him.
days before RDBMS's? How old is he?
that's a foreign key in a table Person (Name varchar(20), Gender foreign
key references Sex in Gender); and your RDBMS automatically indexes
foreign keys, and you have virtually everybody in as a male, this can
lead to a skewed index which can cause a performance hit.
Paul...
--
plinehan__AT__yahoo__DOT__com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.
 

Hi Ignacio,
Yes I see it now.
Thanks,
--
Jim
 

forseeable future.
1. You should print out the email where you asked (and implied you wanted)
foreign keys and for what reason. Keep it safe in some place.
2. You should determine wether or not to see your bosses boss (is this
correct english?).
Apart from that I have no direct advise.
hth,
Martin
 

XXXX@XXXXX.COM says...
community.borland.com/article/0%2C1410%2C27569%
2C00.html#Subtopic10
Paul...
--
plinehan__AT__yahoo__DOT__com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.
 

He has got some pretty good points. I am not sure if SQLServer has any
performance hit with foreign keys but some databases do as compared to just
having and index on the column. Interbase for example
(community.borland.com/article/0%2C1410%2C27569%2C00.html)
He is right that in a *pure* multitier system the database is really just a
data store and business logic should be in the middle tier. I often still
put some business logic in SP's and Triggers because it is often possible to
get greatly enhanced performance this way. I generally handle deletion logic
in the middle tier as well rather than the database. If someone wishes to
delete a record that would have a foreign key then they are required to
supply a replacement value or it cant be deleted. This is a much better
solution I think than a foreign key that just pops up and error and scares
the user into calling me (bad!). I have seen many ligitimate examples of
people wanting to delete records with a foreign key.
In summary though, I think there is no right or wrong, it just depends on
what suits you and the project while still giving good performance. You will
probably get much different answers if you post this to the OOP newsgroup
than the SQLServers one.
Craig.

If you want it then I see no problem in having constraints in the DB as a
backup in case the logic in your middle tier fails. I just wouldn't only put
the logic in the database. In a two tier client/server system though, there
is more arguement to put business rules in the database.
Craig.
 

On Tue, 28 Oct 2003 12:02:31 -0500, Richard Biffl writes:
column. This column would referer to the customer table and should be
nullable. This tells me that during a phone call the operator (using the
software) might create a new customer, identify an existing customer, or
get no customer information at all.
create table incomming_call (
incomming_call_id int identity not null,
customer_id int null references customer(customer_id),
...
);
In fact, this is almost the exact kind of data relationship my boss
doesn't want. He would prefer:
create table incomming_call (
incomming_call_id int identity not null,
customer_id int not null default 0,
...
);
 

Hi Robert,
implemented with indices, but not all systems do so.
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
www.upscene.com
 

Is there a customer with ID = 0? If not ->you're having invalid data.
Again. :-)
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
www.upscene.com