To Normalize or not ??

[SteveS]

I have been 'volunteered' to computerize our Karate school's tournament paper
scoring forms. At one time someone attempted to do this using Excel, but due
to problems (no laptops, Cat5 cables on the floor, cost) it was shelved. Now
with cheaper laptops and Wi-Fi, this demon has reared its ugly head once
again.

My problem right now is the form in Access *must* look like the paper form.
The paper form has a header with some fields for judge's names, tournament
name, date, etc. The columns in the "detail" section are Seed,
ContestantID_FK, Score1, Score2, Score3, Score4, Score5, Total Score, Lowest
Score & Highest Score (this is the paper form - the total, lowest and highest
are not stored in a table).

I'm trying to normalize the scores table, so instead of fields
ContestantID_FK, Score1, Score2,..Score5, I have ContestantID_FK, Scores,
Order, JudgeID_FK.
I have made a crosstab query for the report, but I have a major brain freeze
on how ot make a form for entering the scores. The details section has to be
in continuous forms so I can't use unbound controls and VBA to save the
scores and if I use a crosstab query, it is not updateable.

All of the books stress the evils of repeating fields, and the joy of being
in 3NF, but I can't find anything on how to make the form for data entry. My
fingers have the thousand cuts of death from the books I've pages thru and
I've Googled using several different keywords until I'm seeing double.

Since the number of records will be almost nothing (about 300 per
tournament) I'm tempted to use "spreadsheet" like tables. But every once in a
blue moon, we will have only three judges or seven instead of the standard
five judges.

I'm turning to the wondrous source of all knowledge - the NG for help in
solving this Koan. Anyone have any words of wisdom?

TIA

 

[Albert D.Kallal]

Well, the only problem, or limitation here is that the form MUST look like
the paper, and to me that is a mistake and limits your design.

Remember when movies came out? People just said that movies is talking
radio. In fact, many thought the ONLY use of movies would be to allow people
to see things they have not, and thus was though to be of only use for
education. Of course today, we know that much of the entertainment business
revolves around movies, and we have Hollywood.

When computers first came out, they were though to be of ONLY use to
scientists and engineers. Well, as it turns out, games on computers is now a
LARGER industry then is Holly wood (that is correct, games on computers now
sells MORE then what Hollywood does in one year into north America. Darn
amazing!)

My point of the above? If you only think in terms of a paper form, then you
will stuck with a 2000 year old thinking and technology called paper and
trees. I don't see you trying to keep ink wells around either. So, you need
to break with the past.

Assuming you are going to have some flexibility in your designs You could
for example use two sub forms. On the left side you have the contestants.
And, on the right side you have a form to enter the results. Moving the
arrowkeys up/down on the left side would thus show you (and allow you to
edit) the results for each person.

I have some screen shots that do just there here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

If you look at the very last screen, it is a classic example of a check
amount written (on the left side), and on the right side for EACH person, I
can split out the check amounts to each account. This is nice interface, and
allows use of a normalized database.

Now
with cheaper laptops and Wi-Fi, this demon has reared its ugly head once
again.

Careful...you can't use ms-access over a wi-fi. You can use remote desktop
over wi-fi however.

So, if you do give your self some flexibility, and move beyond a paper form,
then you do in fact have a lot of solutions. You only real limit here is to
make a form that looks exactly like the paper one. And, if you want to write
a lot of code, you can use a un-bound form, and make a form very close.

I think the above ideas of using more then one sub-form is a reasonable
solution, and is far easier to implement then a very complex approach
anyway.

 

[DS]

Well, the only problem, or limitation here is that the form MUST look like
the paper, and to me that is a mistake and limits your design.

Remember when movies came out? People just said that movies is talking
radio. In fact, many thought the ONLY use of movies would be to allow people
to see things they have not, and thus was though to be of only use for
education. Of course today, we know that much of the entertainment business
revolves around movies, and we have Hollywood.

When computers first came out, they were though to be of ONLY use to
scientists and engineers. Well, as it turns out, games on computers is now a
LARGER industry then is Holly wood (that is correct, games on computers now
sells MORE then what Hollywood does in one year into north America. Darn
amazing!)

My point of the above? If you only think in terms of a paper form, then you
will stuck with a 2000 year old thinking and technology called paper and
trees. I don't see you trying to keep ink wells around either. So, you need
to break with the past.

Assuming you are going to have some flexibility in your designs You could
for example use two sub forms. On the left side you have the contestants.
And, on the right side you have a form to enter the results. Moving the
arrowkeys up/down on the left side would thus show you (and allow you to
edit) the results for each person.

I have some screen shots that do just there here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

If you look at the very last screen, it is a classic example of a check
amount written (on the left side), and on the right side for EACH person, I
can split out the check amounts to each account. This is nice interface, and
allows use of a normalized database.




Careful...you can't use ms-access over a wi-fi. You can use remote desktop
over wi-fi however.

So, if you do give your self some flexibility, and move beyond a paper form,
then you do in fact have a lot of solutions. You only real limit here is to
make a form that looks exactly like the paper one. And, if you want to write
a lot of code, you can use a un-bound form, and make a form very close.

I think the above ideas of using more then one sub-form is a reasonable
solution, and is far easier to implement then a very complex approach
anyway.

A couple of points. You can't use Access with Wi-Fi? I'm not saying you
can but I'd like to know why before I get myself into more trouble!
Also the term "Unbound Form" has come my way a couple of times today.
How does one get the Info to stick or go to where it belongs, what
happens to all of your queries and such. Also what are the advantages
of this?
Thanks, I'm just being inquisitive, you've peaked my intreset!
DS

 

[SteveS]

Well, the only problem, or limitation here is that the form MUST look like
the paper, and to me that is a mistake and limits your design.

Remember when movies came out? People just said that movies is talking
radio. In fact, many thought the ONLY use of movies would be to allow people
to see things they have not, and thus was though to be of only use for
education. Of course today, we know that much of the entertainment business
revolves around movies, and we have Hollywood.

When computers first came out, they were though to be of ONLY use to
scientists and engineers. Well, as it turns out, games on computers is now a
LARGER industry then is Holly wood (that is correct, games on computers now
sells MORE then what Hollywood does in one year into north America. Darn
amazing!)

My point of the above? If you only think in terms of a paper form, then you
will stuck with a 2000 year old thinking and technology called paper and
trees. I don't see you trying to keep ink wells around either. So, you need
to break with the past.

Assuming you are going to have some flexibility in your designs You could
for example use two sub forms. On the left side you have the contestants.
And, on the right side you have a form to enter the results. Moving the
arrowkeys up/down on the left side would thus show you (and allow you to
edit) the results for each person.

I have some screen shots that do just there here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

If you look at the very last screen, it is a classic example of a check
amount written (on the left side), and on the right side for EACH person, I
can split out the check amounts to each account. This is nice interface, and
allows use of a normalized database.




Careful...you can't use ms-access over a wi-fi. You can use remote desktop
over wi-fi however.

So, if you do give your self some flexibility, and move beyond a paper form,
then you do in fact have a lot of solutions. You only real limit here is to
make a form that looks exactly like the paper one. And, if you want to write
a lot of code, you can use a un-bound form, and make a form very close.

I think the above ideas of using more then one sub-form is a reasonable
solution, and is far easier to implement then a very complex approach
anyway.

I know that having to make the form look like the paper limits my design, but
so far he hasn't budged on letting me modify the look of the form. (It took him
ofer 4 years to decide on which computer to buy - and that is after he finally
chose a PC over a Mac!)

I have your site bookmarked (along with many other MVP sites) and had read
about WAN-speed problems, but I didn't know Access didn't play well with
Wireless lans. I did a Google and found there is very little about problems
with Access over Wi-Fi. I had planned to use Wi-Fi in a gym with the BE laptop
in the middle (at the side) and two or three laptops at each end.....

Do you have any references or info about using remote desktop?


One more question. What about if I use MSDE with an Access FE over wireless? Or
is wire the only option? TS is not an option - costs too much. (Remember the
volunteer status <g>.)


Thanks,

 

[Albert D.Kallal]

A couple of points. You can't use Access with Wi-Fi? I'm not saying you

can but I'd like to know why before I get myself into more trouble!

The problem is that when you run a split database (or even not split), you
in effect have a file OPEN across the wireless link. We all know that the
WORST thing that can happen is that while writing to a file, you turn your
computer off (in this case, you have a file open on your hard disk).

Often, the file will get damaged when you do this. Why? Because parts of
the file are in your computer memory, and as these parts are written to the
disk, the disk drive is in a vulnerable state, and if you stop this process,
then pieces and parts of the file DO NOT get written to the hard disk. They
get lost, and now you got a file on the disk drive with some pieces of the
file that got written, and some pieces that did not!

This is much the same idea when you turn off your computer, but forget to
save a word document (it is in memory, and thus does not get saved). The
problem here is than word is not so bad, because usually ALL of the document
is
loaded into memory. If you turn off the computer, not much happens, and you
loose your word document changes. However, when you got a file open like
ms-access, parts of that file are in memory, and other parts are actually on
the disk drive. And, some of those parts may need to be written back to
disk. And, if some of those parts don't make it back to the disk drive, then
you got a damaged file because certain pieces are needed to keep humpty
dumpy together. You turn the power off, and many of those pieces are
lost. Often the result is a damaged file. You see, the problem here is that
ms-access is different then excel or word. In ms-access, if you got 200,000
records, ms-access is smart enough to only pull in that ONE PART of the file
where the ONE record you need to edit out of the 200,000 records. (if
ms-access had to pull in the whole file each time..it would be slow as a
dog..and it is designed to be a high performance system, even when you got
200,000 records). So, only parts are moved from the file into memory.


Note that the exact same process happens when you open a file across a
wireless network. As it writes, parts of those pieces in memory may not
make it back to the disk drive due to a temporary cut in the connection.
When a temporary cut happens, pieces get lost, and further often when the
re-connection occurs, the system on the other end thinks that the file was
closed (after all, what can it think when the connection goes away?).

With a true client/server setup, if you are ordering a book on
www.amazon.com, and pull the plug, it would be laughable to think that this
would cause a problem on the Amazon site (it does not, because your computer
NEVER opens a file on their computers..but talks through a web site, and a
database server (which does in fact write to the file).

So, if you use a client to server (sql server, or the MSDE edition which in
included on the office cd), then those parts, and pieces of the file are
NEVER on YOUR computer, but those pieces and parts of the file most
certainly
are on the database server side. So, if you disconnect, or pull the plug on
your computer, those pieces of the file are sitting on the server side, and
thus no damage can occur. On the other hand, if while that database server
is updating and writing to its disk derive, and you pull the plug on that
server, the it is most certainly vulnerable to file damage...

Also the term "Unbound Form" has come my way a couple of times today. How
does one get the Info to stick or go to where it belongs, what happens to
all of your queries and such. Also what are the advantages of this?

A un-bound form is simply a form where the datasouce of the form is NOT set.
This means you have to write code to fill the controls, and then when done,
take the data in the controls..and write the data back to the database.

Also what are the advantages of this?

Well, since you are writing your own code, then YOU can choose as to when
the data will get written back to the table. You might want a complex un-do
feature, or simply not have the file update under certain circumstances. A
lot of VB developers use un-bound controls and forms. Further, VB6 does NOT
in
fact allow databound forms like ms-access. VB6 most certainly does have data
bound controls (you bind them to a datacontorl). Another advantage here is
that you can load up a form, and then disconnect from the database. This
tends to give you more reliability, as while the person is editing the
database, an active connection to the table is not needed. For this reason,
unbound forms can be more reliable then bound. However, when you throw in
the overhead of checking what changed on the form, and also the overhead of
re-establishing the connection, then often you don't save near as much in
terms of bandwidth, or even reliability here.

 

[Albert D.Kallal]

One more question. What about if I use MSDE with an Access FE over

wireless? Or

is wire the only option? TS is not an option - costs too much. (Remember
the
volunteer status <g>.)

Yes, MSDE will work over wireless and can tolerate a much worse connection
then a file share. A very possible solution.

TS is not an option - costs too much

if you have a windows 2000 server or later, then you an use the two free
remote users at no extra cost.

And, if you don't have a server, you can still use remote desktop from the
wireless notebook on the floor to a desktop computer (of course, this would
mean for each wireless user, that a desktop box be somewhere to remotely
use, but might be possible if you got 4, or 5 machines in a office, and then
only need 1, or 2 users on the floor with a notebook/wifi connection.

 

[DS]

The problem is that when you run a split database (or even not split), you
in effect have a file OPEN across the wireless link. We all know that the
WORST thing that can happen is that while writing to a file, you turn your
computer off (in this case, you have a file open on your hard disk).

Often, the file will get damaged when you do this. Why? Because parts of
the file are in your computer memory, and as these parts are written to the
disk, the disk drive is in a vulnerable state, and if you stop this process,
then pieces and parts of the file DO NOT get written to the hard disk. They
get lost, and now you got a file on the disk drive with some pieces of the
file that got written, and some pieces that did not!

This is much the same idea when you turn off your computer, but forget to
save a word document (it is in memory, and thus does not get saved). The
problem here is than word is not so bad, because usually ALL of the document
is
loaded into memory. If you turn off the computer, not much happens, and you
loose your word document changes. However, when you got a file open like
ms-access, parts of that file are in memory, and other parts are actually on
the disk drive. And, some of those parts may need to be written back to
disk. And, if some of those parts don't make it back to the disk drive, then
you got a damaged file because certain pieces are needed to keep humpty
dumpy together. You turn the power off, and many of those pieces are
lost. Often the result is a damaged file. You see, the problem here is that
ms-access is different then excel or word. In ms-access, if you got 200,000
records, ms-access is smart enough to only pull in that ONE PART of the file
where the ONE record you need to edit out of the 200,000 records. (if
ms-access had to pull in the whole file each time..it would be slow as a
dog..and it is designed to be a high performance system, even when you got
200,000 records). So, only parts are moved from the file into memory.


Note that the exact same process happens when you open a file across a
wireless network. As it writes, parts of those pieces in memory may not
make it back to the disk drive due to a temporary cut in the connection.
When a temporary cut happens, pieces get lost, and further often when the
re-connection occurs, the system on the other end thinks that the file was
closed (after all, what can it think when the connection goes away?).

With a true client/server setup, if you are ordering a book on
www.amazon.com, and pull the plug, it would be laughable to think that this
would cause a problem on the Amazon site (it does not, because your computer
NEVER opens a file on their computers..but talks through a web site, and a
database server (which does in fact write to the file).

So, if you use a client to server (sql server, or the MSDE edition which in
included on the office cd), then those parts, and pieces of the file are
NEVER on YOUR computer, but those pieces and parts of the file most
certainly
are on the database server side. So, if you disconnect, or pull the plug on
your computer, those pieces of the file are sitting on the server side, and
thus no damage can occur. On the other hand, if while that database server
is updating and writing to its disk derive, and you pull the plug on that
server, the it is most certainly vulnerable to file damage...




A un-bound form is simply a form where the datasouce of the form is NOT set.
This means you have to write code to fill the controls, and then when done,
take the data in the controls..and write the data back to the database.




Well, since you are writing your own code, then YOU can choose as to when
the data will get written back to the table. You might want a complex un-do
feature, or simply not have the file update under certain circumstances. A
lot of VB developers use un-bound controls and forms. Further, VB6 does NOT
in
fact allow databound forms like ms-access. VB6 most certainly does have data
bound controls (you bind them to a datacontorl). Another advantage here is
that you can load up a form, and then disconnect from the database. This
tends to give you more reliability, as while the person is editing the
database, an active connection to the table is not needed. For this reason,
unbound forms can be more reliable then bound. However, when you throw in
the overhead of checking what changed on the form, and also the overhead of
re-establishing the connection, then often you don't save near as much in
terms of bandwidth, or even reliability here.

That was really a great explanation! I appreciate you taking the time
to explain this. Thank You!
Sincerely
DS

 

[SteveS]

is wire the only option? TS is not an option - costs too much. (Remember
the
volunteer status <g>.)

Yes, MSDE will work over wireless and can tolerate a much worse connection
then a file share. A very possible solution.




if you have a windows 2000 server or later, then you an use the two free
remote users at no extra cost.

And, if you don't have a server, you can still use remote desktop from the
wireless notebook on the floor to a desktop computer (of course, this would
mean for each wireless user, that a desktop box be somewhere to remotely
use, but might be possible if you got 4, or 5 machines in a office, and then
only need 1, or 2 users on the floor with a notebook/wifi connection.

Thanks. I'll try MSDE and see if any corruption occurs.