A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use a variable number of fields



 
 
Thread Tools Display Modes
  #11  
Old November 23rd, 2006, 05:02 PM posted to microsoft.public.access.tablesdbdesign
Phil
external usenet poster
 
Posts: 606
Default 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  
Old November 24th, 2006, 03:32 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default 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  
Old November 28th, 2006, 10:16 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old November 28th, 2006, 10:53 AM posted to microsoft.public.access.tablesdbdesign
Phil
external usenet poster
 
Posts: 606
Default 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  
Old November 28th, 2006, 11:18 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old November 28th, 2006, 02:03 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default 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  
Old November 28th, 2006, 02:07 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.