If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup Table vs. Validation
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. |
#2
|
|||
|
|||
On Thu, 21 Oct 2004 14:37:47 -0400, "Dkline"
wrote: 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 |
#3
|
|||
|
|||
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". On Thu, 21 Oct 2004 14:37:47 -0400, "Dkline" wrote: 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 Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#4
|
|||
|
|||
John Nurick wrote ...
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. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Lookup Table info from a from | Sue | Using Forms | 7 | November 17th, 2004 11:12 AM |
Problem designing a table that pull data from lookup on another ta | Simon@BSC Tech | Database Design | 3 | August 10th, 2004 12:41 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |
Location of Validation Text | lkb | Database Design | 8 | June 30th, 2004 10:45 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |