Ever-Shrinking Data in USFA Membership Listings

[qatet]

While checking the USFA status of the fencers pre-registered for a tournament I'm running tomorrow, I noticed that they have now removed the fencer's division from the Excel file . As a fencer with an extremely common name (I know six other Kate Thomases and have also met a Thom Cate who used to be involved in fencing in the Northeast) I am well aware that a name is not enough to securely match a name between two data sources. (Heck, even matching to the right division isn't a sure thing if you've got parents and kids with the same name or just an unfortunate co-incidence!)

I understand that folks are scared of having their kiddies' information on he internet, but this is rendering the data utterly useless. If a tournament manager can't match the fencer to the data then it can no longer be used to securely verify membership status or ratings. While a member should have a card that they can show, there is no other source for ratings. Can anybody think of a way to solve this? Would putting up USFA member numbers somehow make it easy for pedophiles to get at kids, or whatever it is that folsk are so scared of? That would at least provide a unique identifier that doesn't track back to an age or geographic location. How can this be fixed?

[FencingKitten]

A member of my section has speculated that this may not have been intentional but that it was instead a copy/paste error somewhere. Still waiting to hear back on that.

[Lemonaide]

Pre-computer computations. It worked before, it can work again. Paper and pencil works. You have your experienced former fencer secretary sitting at The Table. The directors hand their sheets to him/her. She/he tabulates. It goes up on the board. When everything is done they post all of the results on a board. Everyone sees it. They send it to the USFA and everyone gets their results back to their salle. I like it better than any excel spreadsheet. They should use Acess, not Excel in the first place.

[nahouw]

Originally Posted by Lemonaide

They should use Acess, not Excel in the first place.

Agreed. Inadvertant Excel copy/cut/sorting and paste errors are eliminated by using Access.

[TooLoftheDeviL]

I initially tried to take the html members listed seperated by division and start an open office database with the information, but it was so far removed from access that I gave up without a lot of struggle.

So I agree... they absolutely should be using access to store the member information.

[peet]

Originally Posted by nahouw

Agreed. Inadvertant Excel copy/cut/sorting and paste errors are eliminated by using Access.

Originally Posted by TooLoftheDeviL

I initially tried to take the html members listed seperated by division and start an open office database with the information, but it was so far removed from access that I gave up without a lot of struggle.

So I agree... they absolutely should be using access to store the member information.

AFAIK, The member/fencer data is not stored in excel. The excel sheet posted to the USFA website is just an export from the actual database, which is in an AS400 (a mainframe-ish computer) at the USFA office.

Which is not to say that the actual database is in all that great a shape, but it's not in Excel.

IMHO, MS Access is the very last thing they should be using to track member data. It should be a serious RDBMS such as Oracle, MySQL, MSSQL, or suchlike.

-p

[foiled once again]

Originally Posted by peet

IMHO, MS Access is the very last thing they should be using to track member data. It should be a serious RDBMS such as Oracle, MySQL, MSSQL, or suchlike.

-p

Agreed but I think the export into excel for the local level club owner is acceptable for running events and what have you.

[tchwojko]

Originally Posted by TooLoftheDeviL

So I agree... they absolutely should be using access to store the member information.

Um, absolutely not Access. I second Peet, they need a real database.

[peet]

Originally Posted by foiled once again

Agreed but I think the export into excel for the local level club owner is acceptable for running events and what have you.

Sure, and that's what they're doing; exporting into excel and posting that .xls file to the website. Now, how & why it is missing the division info that it used to have is anyone's guess.

-p

[trazom]

Strangely, I feel comforted by the idea that the membership data is contained in an old mainframe AS400 system.

It gets me back to my roots. My first programming was on a VAX system, and I recently dealt with exports from a really old mainframe that gave variable fixed width data.

[Goldgar]

Originally Posted by peet

IMHO, MS Access is the very last thing they should be using to track member data. It should be a serious RDBMS such as Oracle, MySQL, MSSQL, or suchlike.

Originally Posted by tchwojko

Um, absolutely not Access. I second Peet, they need a real database.

Access may or may not be suitable for tracking the USFA's membership data, but if it isn't, it won't be because it isn't a "real database". I'll assume that in this discussion, by "Access" you really mean the Jet database engine, since Access itself can be used as a front-end for SQL Server, Oracle, MySQL, and other client/server DBMS. Even with that stipulation, Jet is a real RDMS, though not client/server; it just has certain constraints and limitations that make it unsuitable for certain types of applications.

USFA membership data is paltry in volume, so that's no barrier. Jet can handle file sizes up to a maximum of 2GB, which would be way more than needed for the USFA's membership records. Jet is limited to a maximum of 255 simultaneous connections (and the practical limit is lower), so that could be an issue, depending on how the database is to be used. Certainly it would not be suitable to drive a high-volume website. And because it's a file-server DB, not client-server, it doesn't work at all well over a WAN, though there are ways to work around that if one is willing to take the trouble. And Jet is not suitable for an application that must be up and running 24/7, because you can't back it up reliably while updates are in progress.

Those constraints definitely do rule out Access/Jet for lots of applications. What people sometimes forget is that there are lots of applications for which they do *not* rule out Access. The way the USFA is currently handling and storing registrations, I doubt very much that Access couldn't handle all the current membership-related functions except online registration with ease. It seems likely that even the online registration process could be managed by batching registrations.

That said -- and Access defended -- then I would also say that, given the goal of adding online NAC registration and integrating it all with other database information, it does seem to me that a client/server database would be a better choice. The requirements of high concurrency and the desirability of continuous availability would be the main factors, trumping poratability and ease of use.

[tchwojko]

Originally Posted by Goldgar

Access, Jet, yada yada...

That said -- and Access defended -- then I would also say that, given the goal of adding online NAC registration and integrating it all with other database information, it does seem to me that a client/server database would be a better choice. The requirements of high concurrency and the desirability of continuous availability would be the main factors, trumping poratability and ease of use.

That was an impressive Rube Goldberg of an agreement.

[KD5MDK]

Originally Posted by tchwojko

Um, absolutely not Access. I second Peet, they need a real database.

So, Sybase?

[DJ Apostrophe]

Originally Posted by qatet

While checking the USFA status of the fencers pre-registered for a tournament I'm running tomorrow, I noticed that they have now removed the fencer's division from the Excel file . As a fencer with an extremely common name (I know six other Kate Thomases and have also met a Thom Cate who used to be involved in fencing in the Northeast) I am well aware that a name is not enough to securely match a name between two data sources. (Heck, even matching to the right division isn't a sure thing if you've got parents and kids with the same name or just an unfortunate co-incidence!)

I understand that folks are scared of having their kiddies' information on he internet, but this is rendering the data utterly useless. If a tournament manager can't match the fencer to the data then it can no longer be used to securely verify membership status or ratings. While a member should have a card that they can show, there is no other source for ratings. Can anybody think of a way to solve this? Would putting up USFA member numbers somehow make it easy for pedophiles to get at kids, or whatever it is that folsk are so scared of? That would at least provide a unique identifier that doesn't track back to an age or geographic location. How can this be fixed?

My speculation on why this was done is as follows:

The election.

The former version of the .csv file gave USFA IDs which is the key piece of data that is being used to authenticate the sealed ballots.

If someone wanted to disrupt the election, they could have sent in a ton of ballots with other people's name and USFA ID on them. The election committee would have no way of knowing which ballot was the 'real' ballot from the member, and which was fraudulent. The only way would be to go back to the member and verify the signature, but at that point, the envelopes would have been most likely separated from the ballots.

-w

[Wafath]

Originally Posted by peet

Sure, and that's what they're doing; exporting into excel and posting that .xls file to the website. Now, how & why it is missing the division info that it used to have is anyone's guess.

<unnecessarily pedantic comment>
It would appear that the USFA is exporting a csv, which is a perfectly acceptable intermediate format. Most people choose to have csv files associated with MS Excel.
</upc>

Originally Posted by DJ Apostrophe

If someone wanted to disrupt the election, they could have sent in a ton of ballots with other people's name and USFA ID on them.

Good point. Of course there are a lot of old files with plenty of info out there. Let's just hope that if someone chooses to do this there is a duplicate ballot and they get caught.

<unnecessarily geeky comment>
It would have been cool to hash the id # with a secret key, and have each fencer write down a 4-letter code on the envelope (in addition to their name & USFA #) to authenticate the ballot.
</ugc>

W

[KD5MDK]

Just have fun authenticating all those hand written hashes...

[tchwojko]

Originally Posted by KD5MDK

So, Sybase?

There's another database other than Oracle? Oh, wait, the USFA doesn't have any money. So I guess it's ok for me to say MySQL.

[KD5MDK]

PostgreSQL? Or would you like to make a very nice donation and receive a spiffy T-shirt.

[Wafath]

Originally Posted by KD5MDK

Just have fun authenticating all those hand written hashes...

How hard is it to read a handwritten "#"?

I was thinking:

FID # + secret string -> MD5 -> take the last 3 hex digits:

EG:

1000012345+"Fencing set up us the bomb" -(MD5)->

2113677aaea3f48eeb31e9772e9b4ea9 ->

key = "ea9"

Is it attackable? Sure. But the biggest problem will be fencers who forget to copy the code, or miss-copy it. The biggest weakness (as with most security systems) is not the system itself, but that someone could access the list used to create the labels, thus having the ability (w/ a photo-copier & some stamps) to vote for anyone.

As it stands, our best hope to detect tampering is to have two votes for the same fencer. Too many people have access to the USFA list.

W

[peet]

Originally Posted by Wafath

<unnecessarily pedantic comment>
It would appear that the USFA is exporting a csv, which is a perfectly acceptable intermediate format. Most people choose to have csv files associated with MS Excel.
</upc>

I think if you'll read the rest of the thread, you'll see that I agree completely that excel (more precisely csv, yes) is a fine format for posting to the website. I only wanted to clarify for readers that the actual USFA database is not in excel, which some folks appeared to believe.

Or maybe I'm the one misunderstanding your intent.

And everyone else's.

And now I've apparently sparked up a debate over the various merits of various RDBMSs.



My bad.
No really, I'm sorry.





Really.

-p