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
|
|||
|
|||
Use a variable number of fields
Jamie
Thanks for your again detailed reply. this is making my head hurt, I am impressed. Your brain must obviously work in a very abstract way I could not work in that environment I need to see things graphically. I feel like I am running to catch a bus and falling behind. to recap my tables work like this: - Attendees captures all demographic data for participants (firstname surname etc.) Events Store information on the courses such as startdate, enddate, type of course registration this is where partcipants get booked on to courses Events Location this stores individual dates of the courses e.g. day 1 day 2 etc, plus location, themes etc. From what you are saying I need to add Number of days field (1,3,5) and a day field 1,2,3,4,5 etc and I can add this to the existing tbleventslocation, will this column autocomplete from what would be your classes table or do I type it each time. How do I then set up the validation rule. Sorry I had difficulty following SQL Thanks for explaining the sequence table, I presume I should add extra numbers so that if I ever ran a 6 day course in two years time and wondered why it didnt work. How do I change the tables that I already have to replcate something similar to your example. is it possible dont foget i am constrained by the GUI. thankyou for your time and effort it is really appreciated, thanks Phil "Jamie Collins" wrote: Phil wrote: To implement this in my database your [Classes] table is where I presume I would put the number of days e.g. 1,3,5 and could call the table something like EventTypes which would be used to limit the number of days in each course The enrolment Table would be my already existing tbleventslocation which has Surely you'd want to ensure the number of days for the course corresponds to the course's dates in tbleventslocation? Therefore, you may want to put the 'number of days' column (with an appropriate name) in the same table. The reason for doing this is the same as repeating the Classes.seating_capacity column in the enrolment table in my example and that is because the two flavours of Access Validation Rule (disregarding CHECK constraints) are column-level and row-level. In other words, in order to ensure the seat number is not higher than the maximum possible seat number using a row-level Validation Rule, every row must contain the value for the maximum seat number. Consider this example (I hope you can follow the logic of the SQL code, if not the complete syntax): CREATE TABLE Happenings ( happening_ID CHAR(9) NOT NULL UNIQUE, happening_start_date DATETIME NOT NULL, CONSTRAINT happening_start_date__first_time_granule CHECK(HOUR(happening_start_date) = 0 AND MINUTE(happening_start_date) = 0 AND SECOND(happening_start_date) = 0), happening_end_date DATETIME NOT NULL, CONSTRAINT happening_end_date__last_time_granule CHECK(HOUR(happening_end_date) = 23 AND MINUTE(happening_end_date) = 59 AND SECOND(happening_end_date) = 59), CONSTRAINT happening___date_order CHECK(happening_start_date happening_end_date), happening_duration_days INTEGER NOT NULL, CONSTRAINT happening___duration_days__dates__interaction CHECK(happening_duration_days = DATEDIFF('d', happening_start_date, DATEADD('s', 1, happening_end_date))), UNIQUE (happening_duration_days, happening_ID) ); Once again the CHECK constraints can all be replaced by Validation Rules, notably the one that checks that the duration in days corresponds to the start and end dates. The temporal nature of the data can significantly increase the complexity of the design. For example, to check there are no overlapping periods for courses for the same location you almost certainly wouldn't be able to use a Validation Rule I dont know why you have the sequence table, would i need it, what does it do. An auxiliary Sequence table of integers is a standard SQL trick and has a number of uses e.g. see: ASP FAQ: Why should I consider using an auxiliary numbers table? http://www.aspfaq.com/2516 In my example I am generating sequences of seat number between 1 and the maximum possible seat number and a Sequence table is ideal for this e.g. using the example, this: SELECT C1.class_ID, C1.seating_capacity, S1.seq AS seat_number FROM Classes AS C1, [Sequence] AS S1 WHERE S1.seq BETWEEN 1 AND C1.seating_capacity ORDER BY C1.class_ID, S1.seq; uses the Sequence table to generates all seat numbers for all courses with exceeding the course maximum. I have only ever used code in forms and cannot see how you implement this in tables. I have looked at the individual tables and database and cannot find any code in design view, i did find one validation rule. what code do I need and where do I put it. I cannot see standalone code in your post and would not know where to put in tables You don't need code to reproduce my example. There are more than one ways to skin a cat g. I prefer to create tables, constraints, etc using SQL DDL code. Others prefer to use the Access GUI tools which I personally find hard work: table design view for tables, columns, Validation Rules, PRIMARY KEY, UNIQUE (Index No Duplicates), etc; Relationships window to drag-drop create FOREIGN KEY (enforce integrity = yes); etc. It is sure easier for me to produce VBA code that will create the DB rather than describe the steps required ('open this, click here, answer 'No' when prompted," etc) using the GUI (even if I could remember them g). Jamie. -- |
#12
|
|||
|
|||
Use a variable number of fields
Phil
I'm quite certain there is no one "correct" answer. I was seeing if Jamie has a recommendation to use a "middle" layer for the business rules, to keep them abstracted from both the back-end data and the front-end screens/forms. It would be simpler to use form-level validation, since there are user-interface tools that assist. That doesn't necessarily make it "better". One approach to limiting choices to 1, 3, or 5-day courses would be to not use a text box (into which the user could enter anything). Instead, if there will NEVER be more than 1, 3, or 5, an option group could work. If there's ANY chance there could be others, a small table of valid values and a combo box on the form could do the trick. -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Phil" wrote in message ... Hi Jeff thanks for this i appreciate your reply. From your suggestion, I take it that validation would be better through the form than through the tables. how would I go about this and ensure that each course had the correct number of days at the moment I only have a text box which shows if it is 1,3 or 5 days thanks Phil "Jeff Boyce" wrote: Jamie As you've pointed out, the validation (no "day 4" in a "3-day" course) could be handled at the table level with constraints added into the table definition. This approach is consistent with our earlier "conversation" about embedding as much business logic/validation into the table as possible. Aside from the issue of complexity (more difficult to achieve, since the user interface does not facilitate this), I have a concern (just realizing it) that embedding logic and business rules into the underlying data structure forces anyone using the data to agree to the embedded rules, even if they aren't aware that they are there! (again, with the lack of user interface...) I see an advantage using a more multi-tier approach, in which the data is just data, the user-interface is only that, and the business rules occupy a middle-ground? What are your thoughts? Regards Jeff Boyce Microsoft Office/Access MVP "Jamie Collins" wrote in message oups.com... On Nov 21, 12:51 pm, "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote: If I understand, you can have between 1 and 5 days for which you need to collect/record information. Instead of adding fields, then modifying every query, form, report, macro and code snippet that refers to the fields, add a single table to hold the "many" data. You've described what sounds like a one-to-many relationship -- use the new table to show that. Jeff, I read the OP's spec as needing to go a bit further than this e.g. it's OK to add day four into the database if it's one of those five-day courses but not for a three-day course. Here's something I did for an earlier thread: Approach 1: use referential integrity: CREATE TABLE Students ( student_ID INTEGER NOT NULL UNIQUE ) ; CREATE TABLE Classes ( class_ID INTEGER NOT NULL UNIQUE, seating_capacity INTEGER NOT NULL, CHECK (seating_capacity 0), UNIQUE (seating_capacity, class_ID) ) ; CREATE TABLE Enrolment ( class_ID INTEGER NOT NULL, seating_capacity INTEGER NOT NULL, FOREIGN KEY (seating_capacity, class_ID) REFERENCES Classes (seating_capacity, class_ID), student_ID INTEGER NOT NULL REFERENCES Students (student_ID), UNIQUE (class_ID, student_ID), seat_number INTEGER NOT NULL, UNIQUE (class_ID, seat_number), CONSTRAINT row_level_CHECK_constraint CHECK (seat_number = seating_capacity) ) ; The pros include ease of implementation in Access because the row-level CHECK constraint can be replaced by a record-level Validation Rule. The cons include the otherwise-redundant repeating seating_capacity on each row and the burdensome need to maintain a sequence for seat_number... Approach 2: 'hide' the need for both the repeating seating_capacity and the sequence of seat numbers in a table-level CHECK constraint: CREATE TABLE Enrolment ( class_ID INTEGER NOT NULL REFERENCES Classes (class_ID), student_ID INTEGER NOT NULL REFERENCES Students (student_ID), UNIQUE (class_ID, student_ID), CONSTRAINT table_level_CHECK_constraint CHECK (( SELECT C1.seating_capacity FROM Classes AS C1 WHERE Enrolment.class_ID = C1.class_ID ) = ( SELECT COUNT(*) FROM Enrolment AS E1 WHERE Enrolment.class_ID = E1.class_ID) ) ) ; http://groups.google.com/group/micro...4430dc8b50ba8f Jamie. -- |
#13
|
|||
|
|||
Use a variable number of fields
Jeff Boyce wrote: thanks for this i appreciate your reply. From your suggestion, I take it that validation would be better through the form than through the tables. It would be simpler to use form-level validation, since there are user-interface tools that assist. That doesn't necessarily make it "better". Phil, I'd urge you to read this series of three short articles on this very issue (I'm pretty sure I've previously referred Jeff here g): Mop the Floor and Fix the Leak by Joe Celko http://www.dbazine.com/ofinterest/oi-articles/celko25/ "The poster was asked about some basic data integrity issue and in the discussion he replied that all the validation would be done in the front end application program, so we did not have to bother with constraints on the [database] side. Golly gee whiz, do you suppose that might be a bad idea? Let's just list some of the assumptions and situations required for this approach to work..." Jamie. -- |
#14
|
|||
|
|||
Use a variable number of fields
Jeff & Jamie
I would like to thank you both for your help. Looking at the question I originally posted I asked about five questions in one. But your input has helped me focus on one issue I decided to use form level validation which is now working very well, I decide on this mainly because of the user interface assistance which helped me. Jamie, your solution was elegant and I have learnt so much and realised how little I know and can understand. I am now going to try and build a register which does not work like a spreadsheet. thanks again Phil "Jamie Collins" wrote: Jeff Boyce wrote: thanks for this i appreciate your reply. From your suggestion, I take it that validation would be better through the form than through the tables. It would be simpler to use form-level validation, since there are user-interface tools that assist. That doesn't necessarily make it "better". Phil, I'd urge you to read this series of three short articles on this very issue (I'm pretty sure I've previously referred Jeff here g): Mop the Floor and Fix the Leak by Joe Celko http://www.dbazine.com/ofinterest/oi-articles/celko25/ "The poster was asked about some basic data integrity issue and in the discussion he replied that all the validation would be done in the front end application program, so we did not have to bother with constraints on the [database] side. Golly gee whiz, do you suppose that might be a bad idea? Let's just list some of the assumptions and situations required for this approach to work..." Jamie. -- |
#15
|
|||
|
|||
Use a variable number of fields
Jeff Boyce wrote: I see an advantage using a more multi-tier approach, in which the data is just data, the user-interface is only that, and the business rules occupy a middle-ground? What are your thoughts? I think business rules would ideally be enforced throughout the application. Take fpr example two related data elements: employee_earnings_start_date and employee_earnings_end_date. That start_date occurs before end_date is a 'law of nature' type business rule. I would enforce this rule in front end form, in any function that acts on both dates, in the data layer and in the database. If I had a component with a public/top level function that took the two dates as arguments, then I would raise an error if the end_date parameter value was before the start_date parameter value. I view the database as 'public', reflecting the fact it can be accessed from a variety of sources (e.g. Excel is a popular tool for accessing Access data), subject to permissions. Some business rules are too complex to be implemented as SQL constraints e.g. SQL isn't very good at involved mathematical calculations. Some business rules can be implemented as SQL constraints but they are impractical e.g. a OLTP application with a high volume or INSERT/UPDATE actions that cannot wait for constraints to be checked; it is not possible to defer constraints in Jet, therefore constraints get checked at the end of every SQL statement (and even earlier if more than one table is involved!) and it is unworkable to temporarily DROP constraints in a multi-user environment. Something from the aforementioned article (http://www.dbazine.com/ofinterest/oi...les/celko27/): "There should be one and only one trusted source for the business rules." I agree but with the emphasis on the word 'trusted'. If this were taken literally (i.e. a business rule should only be enforced in one place) it would be contradicted by the later statement, "the overhead of going back and forth between the application and the [trusted source] can kill a system." I think the constraints should be as 'close' to the data as possible i.e. a column (field) validation rule then a row (record) validation rule then a table-level constraint. If these are is not possible or are impractical, my next preference would be to write SQL procedures to control edits to data while removing permissions from the base tables. In SQL Server, I'd consider VIEWs using WITH CHECK OPTION or INSTEAD OF triggers. For those things too complex for SQL there is the possibility of using CLR to write database constraints...but that's another SQL product. If data constraints cannot be enforced on the current SQL platform, maybe its time for a new SQL platform? Jamie. -- |
#16
|
|||
|
|||
Use a variable number of fields
Thanks, Jamie. I don't recall seeing this one before...
Jeff "Jamie Collins" wrote in message ups.com... Jeff Boyce wrote: thanks for this i appreciate your reply. From your suggestion, I take it that validation would be better through the form than through the tables. It would be simpler to use form-level validation, since there are user-interface tools that assist. That doesn't necessarily make it "better". Phil, I'd urge you to read this series of three short articles on this very issue (I'm pretty sure I've previously referred Jeff here g): Mop the Floor and Fix the Leak by Joe Celko http://www.dbazine.com/ofinterest/oi-articles/celko25/ "The poster was asked about some basic data integrity issue and in the discussion he replied that all the validation would be done in the front end application program, so we did not have to bother with constraints on the [database] side. Golly gee whiz, do you suppose that might be a bad idea? Let's just list some of the assumptions and situations required for this approach to work..." Jamie. -- |
#17
|
|||
|
|||
Use a variable number of fields
Thanks, Jamie.
What's bothering me is the possibility that business rules could be scattered across any/all layers of the application. This seems to offer the possibility that a new user (i.e., a new business use) could be (involuntarily) forced to comply with rules of which s/he was completely unaware. The notion of using a "business rules layer" would be to make all rules explicitly available. If a new business use(r) didn't check in this, as quoted below, "trusted" location, s/he only has him/herself to blame. Interesting concepts and discussion! Jeff "Jamie Collins" wrote in message oups.com... Jeff Boyce wrote: I see an advantage using a more multi-tier approach, in which the data is just data, the user-interface is only that, and the business rules occupy a middle-ground? What are your thoughts? I think business rules would ideally be enforced throughout the application. Take fpr example two related data elements: employee_earnings_start_date and employee_earnings_end_date. That start_date occurs before end_date is a 'law of nature' type business rule. I would enforce this rule in front end form, in any function that acts on both dates, in the data layer and in the database. If I had a component with a public/top level function that took the two dates as arguments, then I would raise an error if the end_date parameter value was before the start_date parameter value. I view the database as 'public', reflecting the fact it can be accessed from a variety of sources (e.g. Excel is a popular tool for accessing Access data), subject to permissions. Some business rules are too complex to be implemented as SQL constraints e.g. SQL isn't very good at involved mathematical calculations. Some business rules can be implemented as SQL constraints but they are impractical e.g. a OLTP application with a high volume or INSERT/UPDATE actions that cannot wait for constraints to be checked; it is not possible to defer constraints in Jet, therefore constraints get checked at the end of every SQL statement (and even earlier if more than one table is involved!) and it is unworkable to temporarily DROP constraints in a multi-user environment. Something from the aforementioned article (http://www.dbazine.com/ofinterest/oi...les/celko27/): "There should be one and only one trusted source for the business rules." I agree but with the emphasis on the word 'trusted'. If this were taken literally (i.e. a business rule should only be enforced in one place) it would be contradicted by the later statement, "the overhead of going back and forth between the application and the [trusted source] can kill a system." I think the constraints should be as 'close' to the data as possible i.e. a column (field) validation rule then a row (record) validation rule then a table-level constraint. If these are is not possible or are impractical, my next preference would be to write SQL procedures to control edits to data while removing permissions from the base tables. In SQL Server, I'd consider VIEWs using WITH CHECK OPTION or INSTEAD OF triggers. For those things too complex for SQL there is the possibility of using CLR to write database constraints...but that's another SQL product. If data constraints cannot be enforced on the current SQL platform, maybe its time for a new SQL platform? Jamie. -- |
|
Thread Tools | |
Display Modes | |
|
|