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 |
#11
|
|||
|
|||
Validation of text
Interesting idea, but I still am not sure how to deal with db level
validation. Any pointers there would be appreciated. "onedaywhen" wrote: Klatuu wrote: After 30 years in this business, there is very little, at the conceptual level, that is new. My first encounter with the concept of enforced data integrity was in a COBOL/ISAM shop where all IO was done using canned procedures that enforced the data rules. I'm just about 'wise' enough to have used pre-SQL ISAMs. That's why I just love thing like CHECK constraints in the database: simple, flexible, powerful. They do port to other SQLs quite well, noting that I have to dumb-down when porting to SQL Server because Jet's are better implemented. I certinly have enjoyed our discussion. You make some good points worth serious consideration. Likewise ;-) Jamie. -- |
#12
|
|||
|
|||
Validation of text
Klatuu wrote: Interesting idea, but I still am not sure how to deal with db level validation. Any pointers there would be appreciated. Well, if we're talking about database validation then let's just consider Jet's CHECK constraints and only worry for now about ANSI wildcard characters: % for multiple characters and _ (underscore) for a single character. The way I see it there are two flavours of Jet CHECK constraint: row level and table level (but in reality they are the same animal). Access's Validation Rules are considered to be either at the 'column' level or at the 'table' level, though I'd argue the latter is actually at the *row* level because one can reference multiple columns in the same row but not other rows in the table. I think the differentiation is based on the Validation Text property more than anything. Basic validation of values to enforce business rules, like the one that started this thread: "must start with a the letter 'H' followed by a numeric value of varying length". I also assume that H01 is illegal because it is considered equivalent to H1 and that H0 is illegal because it is effectively 'zero'. Jet allows use to define multiple CHECK constraints per row/table, therefore we can follow Celko's advice in the article (up thread) and split these into multiple rules. It would be good to set a Validation Text property for each but because it is tied to closely to Access we're only allowed one per *table*. The best we can do is use a meaningful name. Remember it is our intention that we will trap input errors with validation in the front end application so the user being exposed to these in error messages is a 'last resort': CREATE TABLE Employees ( employee_nbr VARCHAR(11) NOT NULL PRIMARY KEY, CONSTRAINT employee_nbr__basic_pattern CHECK (employee_nbr NOT LIKE 'H%[!0-9]%'), CONSTRAINT employee_nbr__no_leading_zeros CHECK (employee_nbr NOT LIKE 'H0%') ); At this point, before we start building the front end, we should test each rule e.g. values I expect to pass: INSERT INTO Employees (employee_nbr) VALUES ('H1'); INSERT INTO Employees (employee_nbr) VALUES ('H900'); values I expect to fail: INSERT INTO Employees (employee_nbr) VALUES ('F111'); INSERT INTO Employees (employee_nbr) VALUES ('H1A3'); INSERT INTO Employees (employee_nbr) VALUES ('H0'); INSERT INTO Employees (employee_nbr) VALUES ('H05'); [Just to prove this is a worthy exercise, my first insert inexplicable failed. A quick look at my code revealed a typo [!9-0], which could have been tricky to debug later.] Validation using multiple values on the same row. For our payroll table, let's model periods of earnings history in the recommended way using closed-open representation with start_date and end date pairs, a null end date signifying the current pay period. Basic validation of the dates is that the start date will be midnight and the end date will be the smallest granule (for Jet this is one second) before midnight. Note that although end_date can be NULL there is no need to explicitly test for NULL in validation. This is due to the nature of nulls in SQL DDL (data declaration language e.g. table design). Whereas in SQL DML (data declaration language e.g. queries) an UNKNOWN resulting from a comparison with a NULL value causes rows to be removed from a resultset, in SQL DDL the UNKNOWN cannot be known to fail the rule therefore it is allowed to pass. This makes sense when you think of our NULL end data as being a placeholder for a date which will certainly be known at some time in the future, so it is right to defer validation until the value is known. I think this is mainly due to pragmatics though i.e. without this implicit behaviour, validation of nullable columns would *always* have to explicitly handle NULL. An obvious domain rule, yet one that is often missed in database validation rules, is that the end date cannot occur before the start date in the same row. Similarly, salary cannot be negative. Another obvious one is that each employee there should only be a maximum of one row with a null date, which is best enforces with a UNIQUE constraint, because the end dates should not be duplicated either for an employee. Start dates are similarly unique for an employee and, because they are not nullable, makes a good compound natural key: CREATE TABLE EarningsHistory ( employee_nbr VARCHAR(11) NOT NULL REFERENCES Employees (employee_nbr) ON DELETE NO ACTION ON UPDATE CASCADE, start_date DATETIME DEFAULT DATE() NOT NULL, CONSTRAINT earnings_start_date__open_interval CHECK( HOUR(start_date) = 0 AND MINUTE(start_date) = 0 AND SECOND(start_date) = 0), end_date DATETIME, CONSTRAINT earnings_end_date__one_granule_closed_interval CHECK( HOUR(end_date) = 23 AND MINUTE(end_date) = 59 AND SECOND(end_date) = 59), CONSTRAINT earnings_dates_order CHECK (start_date end_date), salary_amount CURRENCY NOT NULL, CONSTRAINT earnings_salary_amount__value CHECK (salary_amount = 0), UNIQUE (employee_nbr, end_date), PRIMARY KEY (employee_nbr, start_date) ); Once again, test immediately. Rows I expected to pass: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-01-01 00:00:00#, #2004-12-31 23:59:59#, 10000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2006-01-01 00:00:00#, NULL, 12000.00) ; Rows I expected to fail: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-01-01 00:00:00#, #2005-12-31 00:00:00#, 11000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-01-01 14:29:39#, #2005-12-31 23:59:59#, 11000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-12-01 00:00:00#, #2005-01-31 23:59:59#, 11000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-01-01 00:00:00#, #2005-12-31 23:59:59#, -99.99) ; [Once again my first test insert revealed an error: a copy and paste resulted in me testing start_date in my rule for end_date!] The data integrity of our payroll table is quite good. If we can't get bad values in using direct INSERTs then no front end application can. However, the constraints are not complete to my satisfaction. For example, this spoils the data: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-11-01 00:00:00#, #2006-02-28 23:59:59#, 11000.00) ; It passes all the existing constraints yet it creates bad (illogical) data. Note end_date can be null, signifying the current date, so we'll replace it with the current date in queries: SELECT employee_nbr, #2006-01-01# AS report_date, salary_amount FROM EarningsHistory WHERE #2006-01-01# BETWEEN start_date AND IIF(end_date IS NULL, NOW(), end_date); According to the data, the employee was earning two different amounts simultaneously. This could result in some tricky situations so a further constraint is required. To write this kind of constraint I usually start with some data that fails the rule, write a query that identifies all the bad rows, then turn it into a constraint. First some more bad data: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-03-01 00:00:00#, #2006-04-01 23:59:59#, 15000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-07-01 00:00:00#, #2004-07-31 23:59:59#, 16000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2006-05-01 00:00:00#, #2006-12-31 23:59:59#, 17000.00) ; As everyone knows, if they think about it, that a period overlaps a later period if its end date occurs before the later period's start date (I think...I'm doing this all off the top of my head!) Therefore, I think this should identify the bad data: SELECT * FROM EarningsHistory, EarningsHistory AS E2 WHERE EarningsHistory.employee_nbr = E2.employee_nbr AND EarningsHistory.start_date E2.start_date AND ( E2.start_date IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) OR IIF(E2.end_date IS NULL, NOW(), E2.end_date) IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) ) I then start deleting the bad rows individually. The resultset should only return zero rows when all the bad data has been removed, so I run the query after each single row deletion: DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2004-11-01 00:00:00# ; DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2004-03-01 00:00:00# ; DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2004-07-01 00:00:00# ; DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2006-05-01 00:00:00# ; OK, only after the final delete did the resultset return empty. I'll assume the logic is sound and convert it to a constraint: ALTER TABLE EarningsHistory ADD CONSTRAINT earnings_history__no_overlapping_periods CHECK (0 = ( SELECT * FROM EarningsHistory, EarningsHistory AS E2 WHERE EarningsHistory.employee_nbr = E2.employee_nbr AND EarningsHistory.start_date E2.start_date AND ( E2.start_date IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) OR IIF(E2.end_date IS NULL, NOW(), E2.end_date) IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) ) ) ); Try the inserts again and they should all fail. Some further business rules spring to mind. Contiguous periods were the salary amounts are the same is useless information indicative of an error: ALTER TABLE EarningsHistory ADD CONSTRAINT earnings_history__contiguous_periods_salary_must_c hange CHECK (0 = ( SELECT COUNT(*) FROM EarningsHistory, EarningsHistory AS E2 WHERE EarningsHistory.employee_nbr = E2.employee_nbr AND DATEADD('s', 1, IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date)) = E2.start_date AND EarningsHistory.salary_amount = E2.salary_amount ) ); Another rule could disallow gaps between periods (if they still employed but aren't being paid then add a period where the salary is zero): ALTER TABLE EarningsHistory ADD CONSTRAINT earnings_history__periods_must_be_contiguous CHECK ( 0 = ( SELECT COUNT(*) FROM EarningsHistory AS E1 WHERE EXISTS ( SELECT * FROM EarningsHistory AS E2 WHERE E1.employee_nbr = E2.employee_nbr AND E1.start_date E2.start_date) AND NOT EXISTS ( SELECT * FROM EarningsHistory AS E2 WHERE E1.employee_nbr = E2.employee_nbr AND DATEADD('s', 1, E1.end_date) = E2.start_date ) ) ); Then there are CHECK constraints that can reference rows in other tables... It should be obvious by now that writing validation rules in the database layer is as easy as writing a query. Jamie -- |
#13
|
|||
|
|||
Validation of text
I guess my question wasn't clear.
The creation of the rules is not a problem. What I am not clear on is how to recognize and deal with a validation violation at the application level. For example, using Form level validation for an End Date, I woul use the control's Before Update event, check to see that the End Date is Start Date, and if not, present a message box to the user and cancel the Update. What is the process using database validation rule to accomplish the same thing and not have the user see one of Access' totally meaningless error messages? BTW, don't be so quick to put a 0 constraint on salary. You haven't seen my pay check "onedaywhen" wrote: Klatuu wrote: Interesting idea, but I still am not sure how to deal with db level validation. Any pointers there would be appreciated. Well, if we're talking about database validation then let's just consider Jet's CHECK constraints and only worry for now about ANSI wildcard characters: % for multiple characters and _ (underscore) for a single character. The way I see it there are two flavours of Jet CHECK constraint: row level and table level (but in reality they are the same animal). Access's Validation Rules are considered to be either at the 'column' level or at the 'table' level, though I'd argue the latter is actually at the *row* level because one can reference multiple columns in the same row but not other rows in the table. I think the differentiation is based on the Validation Text property more than anything. Basic validation of values to enforce business rules, like the one that started this thread: "must start with a the letter 'H' followed by a numeric value of varying length". I also assume that H01 is illegal because it is considered equivalent to H1 and that H0 is illegal because it is effectively 'zero'. Jet allows use to define multiple CHECK constraints per row/table, therefore we can follow Celko's advice in the article (up thread) and split these into multiple rules. It would be good to set a Validation Text property for each but because it is tied to closely to Access we're only allowed one per *table*. The best we can do is use a meaningful name. Remember it is our intention that we will trap input errors with validation in the front end application so the user being exposed to these in error messages is a 'last resort': CREATE TABLE Employees ( employee_nbr VARCHAR(11) NOT NULL PRIMARY KEY, CONSTRAINT employee_nbr__basic_pattern CHECK (employee_nbr NOT LIKE 'H%[!0-9]%'), CONSTRAINT employee_nbr__no_leading_zeros CHECK (employee_nbr NOT LIKE 'H0%') ); At this point, before we start building the front end, we should test each rule e.g. values I expect to pass: INSERT INTO Employees (employee_nbr) VALUES ('H1'); INSERT INTO Employees (employee_nbr) VALUES ('H900'); values I expect to fail: INSERT INTO Employees (employee_nbr) VALUES ('F111'); INSERT INTO Employees (employee_nbr) VALUES ('H1A3'); INSERT INTO Employees (employee_nbr) VALUES ('H0'); INSERT INTO Employees (employee_nbr) VALUES ('H05'); [Just to prove this is a worthy exercise, my first insert inexplicable failed. A quick look at my code revealed a typo [!9-0], which could have been tricky to debug later.] Validation using multiple values on the same row. For our payroll table, let's model periods of earnings history in the recommended way using closed-open representation with start_date and end date pairs, a null end date signifying the current pay period. Basic validation of the dates is that the start date will be midnight and the end date will be the smallest granule (for Jet this is one second) before midnight. Note that although end_date can be NULL there is no need to explicitly test for NULL in validation. This is due to the nature of nulls in SQL DDL (data declaration language e.g. table design). Whereas in SQL DML (data declaration language e.g. queries) an UNKNOWN resulting from a comparison with a NULL value causes rows to be removed from a resultset, in SQL DDL the UNKNOWN cannot be known to fail the rule therefore it is allowed to pass. This makes sense when you think of our NULL end data as being a placeholder for a date which will certainly be known at some time in the future, so it is right to defer validation until the value is known. I think this is mainly due to pragmatics though i.e. without this implicit behaviour, validation of nullable columns would *always* have to explicitly handle NULL. An obvious domain rule, yet one that is often missed in database validation rules, is that the end date cannot occur before the start date in the same row. Similarly, salary cannot be negative. Another obvious one is that each employee there should only be a maximum of one row with a null date, which is best enforces with a UNIQUE constraint, because the end dates should not be duplicated either for an employee. Start dates are similarly unique for an employee and, because they are not nullable, makes a good compound natural key: CREATE TABLE EarningsHistory ( employee_nbr VARCHAR(11) NOT NULL REFERENCES Employees (employee_nbr) ON DELETE NO ACTION ON UPDATE CASCADE, start_date DATETIME DEFAULT DATE() NOT NULL, CONSTRAINT earnings_start_date__open_interval CHECK( HOUR(start_date) = 0 AND MINUTE(start_date) = 0 AND SECOND(start_date) = 0), end_date DATETIME, CONSTRAINT earnings_end_date__one_granule_closed_interval CHECK( HOUR(end_date) = 23 AND MINUTE(end_date) = 59 AND SECOND(end_date) = 59), CONSTRAINT earnings_dates_order CHECK (start_date end_date), salary_amount CURRENCY NOT NULL, CONSTRAINT earnings_salary_amount__value CHECK (salary_amount = 0), UNIQUE (employee_nbr, end_date), PRIMARY KEY (employee_nbr, start_date) ); Once again, test immediately. Rows I expected to pass: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-01-01 00:00:00#, #2004-12-31 23:59:59#, 10000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2006-01-01 00:00:00#, NULL, 12000.00) ; Rows I expected to fail: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-01-01 00:00:00#, #2005-12-31 00:00:00#, 11000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-01-01 14:29:39#, #2005-12-31 23:59:59#, 11000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-12-01 00:00:00#, #2005-01-31 23:59:59#, 11000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2005-01-01 00:00:00#, #2005-12-31 23:59:59#, -99.99) ; [Once again my first test insert revealed an error: a copy and paste resulted in me testing start_date in my rule for end_date!] The data integrity of our payroll table is quite good. If we can't get bad values in using direct INSERTs then no front end application can. However, the constraints are not complete to my satisfaction. For example, this spoils the data: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-11-01 00:00:00#, #2006-02-28 23:59:59#, 11000.00) ; It passes all the existing constraints yet it creates bad (illogical) data. Note end_date can be null, signifying the current date, so we'll replace it with the current date in queries: SELECT employee_nbr, #2006-01-01# AS report_date, salary_amount FROM EarningsHistory WHERE #2006-01-01# BETWEEN start_date AND IIF(end_date IS NULL, NOW(), end_date); According to the data, the employee was earning two different amounts simultaneously. This could result in some tricky situations so a further constraint is required. To write this kind of constraint I usually start with some data that fails the rule, write a query that identifies all the bad rows, then turn it into a constraint. First some more bad data: INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-03-01 00:00:00#, #2006-04-01 23:59:59#, 15000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2004-07-01 00:00:00#, #2004-07-31 23:59:59#, 16000.00) ; INSERT INTO EarningsHistory (employee_nbr, start_date, end_date, salary_amount) VALUES ('H1', #2006-05-01 00:00:00#, #2006-12-31 23:59:59#, 17000.00) ; As everyone knows, if they think about it, that a period overlaps a later period if its end date occurs before the later period's start date (I think...I'm doing this all off the top of my head!) Therefore, I think this should identify the bad data: SELECT * FROM EarningsHistory, EarningsHistory AS E2 WHERE EarningsHistory.employee_nbr = E2.employee_nbr AND EarningsHistory.start_date E2.start_date AND ( E2.start_date IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) OR IIF(E2.end_date IS NULL, NOW(), E2.end_date) IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) ) I then start deleting the bad rows individually. The resultset should only return zero rows when all the bad data has been removed, so I run the query after each single row deletion: DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2004-11-01 00:00:00# ; DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2004-03-01 00:00:00# ; DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2004-07-01 00:00:00# ; DELETE FROM EarningsHistory WHERE employee_nbr = 'H1' AND start_date = #2006-05-01 00:00:00# ; OK, only after the final delete did the resultset return empty. I'll assume the logic is sound and convert it to a constraint: ALTER TABLE EarningsHistory ADD CONSTRAINT earnings_history__no_overlapping_periods CHECK (0 = ( SELECT * FROM EarningsHistory, EarningsHistory AS E2 WHERE EarningsHistory.employee_nbr = E2.employee_nbr AND EarningsHistory.start_date E2.start_date AND ( E2.start_date IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) OR IIF(E2.end_date IS NULL, NOW(), E2.end_date) IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date) ) ) ); Try the inserts again and they should all fail. Some further business rules spring to mind. Contiguous periods were the salary amounts are the same is useless information indicative of an error: ALTER TABLE EarningsHistory ADD CONSTRAINT earnings_history__contiguous_periods_salary_must_c hange CHECK (0 = ( SELECT COUNT(*) FROM EarningsHistory, EarningsHistory AS E2 WHERE EarningsHistory.employee_nbr = E2.employee_nbr AND DATEADD('s', 1, IIF(EarningsHistory.end_date IS NULL, NOW(), EarningsHistory.end_date)) = E2.start_date AND EarningsHistory.salary_amount = E2.salary_amount ) ); Another rule could disallow gaps between periods (if they still employed but aren't being paid then add a period where the salary is zero): ALTER TABLE EarningsHistory ADD CONSTRAINT earnings_history__periods_must_be_contiguous CHECK ( 0 = ( SELECT COUNT(*) FROM EarningsHistory AS E1 WHERE EXISTS ( SELECT * FROM EarningsHistory AS E2 WHERE E1.employee_nbr = E2.employee_nbr AND E1.start_date E2.start_date) AND NOT EXISTS ( SELECT * FROM EarningsHistory AS E2 WHERE E1.employee_nbr = E2.employee_nbr AND DATEADD('s', 1, E1.end_date) = E2.start_date ) ) ); Then there are CHECK constraints that can reference rows in other tables... It should be obvious by now that writing validation rules in the |
#14
|
|||
|
|||
Validation of text
Klatuu wrote:
I guess my question wasn't clear. The creation of the rules is not a problem. What I am not clear on is how to recognize and deal with a validation violation at the application level. For example, using Form level validation for an End Date, I woul use the control's Before Update event, check to see that the End Date is Start Date, and if not, present a message box to the user and cancel the Update. What is the process using database validation rule to accomplish the same thing and not have the user see one of Access' totally meaningless error messages? You put in the database rule and ALSO use your BeforeUpdate code to prevent the ugly message or else use the Form's Error event to replace the built in message with your own. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#15
|
|||
|
|||
Validation of text
Klatuu wrote:
What I am not clear on is how to recognize and deal with a validation violation at the application level. Don't ask me; that's the front end guy's problem g. For example, using Form level validation for an End Date, I woul use the control's Before Update event, check to see that the End Date is Start Date, and if not, present a message box to the user and cancel the Update. For example, using Form level validation for an End Date, I woul use the control's Before Update event, check to see that the End Date is Start Date, and if not, present a message box to the user and cancel the Update. What is the process using database validation rule to accomplish the same thing and not have the user see one of Access' totally meaningless error messages? I would guess you continuing doing everything you were already doing. If one of the CHECK constraints bites it indicates there is a bug in the front end. I think you should be handling data engine error rather than directly exposing them to the user e.g. (aircode): ' Step over Access' totally meaningless error messages rowsAffected = 0 errored = False On Error Resume Next ' Would really do this with ' ADO Command and Paramters con.Execute _ "EXECUTE AddNewSalary 'H1'," & _ " #2006-06-30#, 15000.00", rowsAffected errored = CBool(Err.Number 0) On Error Goto 0 If errored Then ' Provide totally meaningful error message End if If rowsAffected = 0 Then ' Handle failure here End If etc Thanks for getting me to finish typing up my notes. Actually, it's still a WIP because I have to write the section on inter-table CHECK constraints by re-writing my existing ones using joins on a Calendar auxiliary table... Jamie. -- |
#16
|
|||
|
|||
Validation of text
So what is the point, then, of doing the database level validation if I still
have to do the same checks at the form level to be able to handle the errors? This is my primary aversion to db level validation. It is not as easy to handle incorrect input this way. "Rick Brandt" wrote: Klatuu wrote: I guess my question wasn't clear. The creation of the rules is not a problem. What I am not clear on is how to recognize and deal with a validation violation at the application level. For example, using Form level validation for an End Date, I woul use the control's Before Update event, check to see that the End Date is Start Date, and if not, present a message box to the user and cancel the Update. What is the process using database validation rule to accomplish the same thing and not have the user see one of Access' totally meaningless error messages? You put in the database rule and ALSO use your BeforeUpdate code to prevent the ugly message or else use the Form's Error event to replace the built in message with your own. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#17
|
|||
|
|||
Validation of text
Klatuu wrote:
So what is the point, then, of doing the database level validation if I still have to do the same checks at the form level to be able to handle the errors? Because your front end is NOT the only way the data could be manipulated. You should think of the database and the application as two separate projects. Job 1 is to have a proper database with good data integrity rules. Then you can think about how your apop interacts with that database. This comes naturally to people like myself who almost exclusively work with server databases, many of which existed long before I came along to add an Access interface to it. With an all Jet application it is easy to consider the tables as a simple subset of objects in the total application, but the database should properly be able to stand completely on its own. This is my primary aversion to db level validation. It is not as easy to handle incorrect input this way. Proper database application design is not always about what is "easy". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#18
|
|||
|
|||
Validation of text
Thanks for the input, Rick. I see your point. In a larger database
environment where multiple applicatioins could be using the database, I'm with you. I'm sure you have more knowledge and experience with server databases than I. I have mostly been on the application side. I have studied and had training in relational database theory, so it is not an unfamiliar environment. My intent on this thread was to get opinions and knowledge. That I have done. There is one statement you made I object to: Proper database application design is not always about what is "easy". I find that rude and condescending. Nothing in life is always about what is easy, but it make so sense perform useless tasks just so you can think of yourself as a hero. "Rick Brandt" wrote: Klatuu wrote: So what is the point, then, of doing the database level validation if I still have to do the same checks at the form level to be able to handle the errors? Because your front end is NOT the only way the data could be manipulated. You should think of the database and the application as two separate projects. Job 1 is to have a proper database with good data integrity rules. Then you can think about how your apop interacts with that database. This comes naturally to people like myself who almost exclusively work with server databases, many of which existed long before I came along to add an Access interface to it. With an all Jet application it is easy to consider the tables as a simple subset of objects in the total application, but the database should properly be able to stand completely on its own. This is my primary aversion to db level validation. It is not as easy to handle incorrect input this way. Proper database application design is not always about what is "easy". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#19
|
|||
|
|||
Validation of text
Klatuu wrote:
There is one statement you made I object to: Proper database application design is not always about what is "easy". I find that rude and condescending. Nothing in life is always about what is easy, but it make so sense perform useless tasks just so you can think of yourself as a hero. It was not my intent to be rude. If you took it that way I apologize. "Easy" was your word after all, not mine. I suppose I disagree with your term "useless tasks". It is not useless to have good data integrity. It could be argued though whether avoiding engine error messages is worth the extra trouble. For the most part I don't bother with "user friendly" error messages. Most users only have to see "Duplicate Key Error" a handful of times before they know what it means. If not, then they can ask someone. If the designer of the front end makes the decision that engine errors need to be replaced with something more descriptive to the user then that is the effort I would consider "extra". Not necessarily "useless", but definitely "extra". That decision in my opinion is completely separate from the those taken to keep the data clean with the latter having much more priority. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#20
|
|||
|
|||
Validation of text
I respect your opinion and agree that both need serious and complete
consideration. I would not mind the "extra" because coming from 30 years of writing user interface applications, I feel it is very important that users receive information that is meaningful to them. My problem here is not being familiar with how to trap and handle database level errors. Any enlightenment would be appreciated. It doesn't have to be detailed, conceptual is fine. "Rick Brandt" wrote: Klatuu wrote: There is one statement you made I object to: Proper database application design is not always about what is "easy". I find that rude and condescending. Nothing in life is always about what is easy, but it make so sense perform useless tasks just so you can think of yourself as a hero. It was not my intent to be rude. If you took it that way I apologize. "Easy" was your word after all, not mine. I suppose I disagree with your term "useless tasks". It is not useless to have good data integrity. It could be argued though whether avoiding engine error messages is worth the extra trouble. For the most part I don't bother with "user friendly" error messages. Most users only have to see "Duplicate Key Error" a handful of times before they know what it means. If not, then they can ask someone. If the designer of the front end makes the decision that engine errors need to be replaced with something more descriptive to the user then that is the effort I would consider "extra". Not necessarily "useless", but definitely "extra". That decision in my opinion is completely separate from the those taken to keep the data clean with the latter having much more priority. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
|
Thread Tools | |
Display Modes | |
|
|