Lookup Table vs. Validation

[Dkline]

I understand the arguments againt lookup tables. I also have a need to
prevent bad data.

In lieu of a lookup table is it acceptable practice to use a validation rule
for users who may bypass forms and go straight to the table and/or import
data from spreadsheets?

We are also growing to the point where we will need to move from Access MDB
files to ADP and SQL Server. I'm about to get a crash course in SQL Server
but one question comes to mind - can you do validation in SQL Server.

 

[John Vinson]

I understand the arguments againt lookup tables. I also have a need to
prevent bad data.

Reread the arguments. Nobody objects to lookup TABLES. The objection
is to using "Lookup Fields" in a table datasheet. You're quite
correct; enforced relationships to prevent bad data are *universal* in
Access (or SQL) databases.

In lieu of a lookup table is it acceptable practice to use a validation rule
for users who may bypass forms and go straight to the table and/or import
data from spreadsheets?

Not really. A validation rule is much harder to implement and maintain
than a one-to-many relationship, relational integrity enforced, with a
table (which you can call a "lookup table" if you wish). And users
should be strongly discouraged from going to table datasheets for
ANYTHING - they are less convenient, harder to read, and much less
controllable than Forms. Give them a decent Form and they'll never
*want* to look at a datasheet!

We are also growing to the point where we will need to move from Access MDB
files to ADP and SQL Server. I'm about to get a crash course in SQL Server
but one question comes to mind - can you do validation in SQL Server.

Yes.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Nurick]

1) I don't think there are many people who would argue against lookup
tables. In fact they are pretty much fundamental to the idea of a
relational database. What causes all the trouble is Access's so-called
lookup *fields*.

2) Validation rules are always acceptable. They are a vital part of
maintaining the integrity of your data. But in the usual Access/Jet
(MDB) database there are limits to what they can do to protect your data
from users who "bypass forms", and it's much better not to let users
work directly with the table datasheets.

3) SQL Server offers much more powerful validation than Jet; your crash
course should include "triggers".

 

[Jamie Collins]

Validation rules are always acceptable. They are a vital part of
maintaining the integrity of your data.

Agreed. One shouldn't rely on the front end to maintain data
integrity.

But in the usual Access/Jet
(MDB) database there are limits to what they can do to protect your data

Are you including Jet 4.0 support for CHECK constraints? I've found
them fairly capable.

SQL Server offers much more powerful validation than Jet; your crash
course should include "triggers".

To be used only as a last resort, of course <g>. Triggers are no
substitute for DRI and 'regular' constraints.

Jamie.

--