Unique ID for EzWinScore

[Graham Smith]

I need to start keeping track of shooters from match to match. I was going to use the USPSA Suffix as the unique identifier because the Prefix may change. Then it occurred to me to ask if there are cases where the Suffix might change. In particular, if someone becomes a Life member, do they get a new USPSA # or does just the Prefix change?

[NMBOpen]

Life membership comes with a new number, which may overlap with early A/TY/FY numbers.

[Graham Smith]

Life membership comes with a new number, which may overlap with early A/TY/FY numbers.

I was afraid you were going to say that. This means that I'm going to have to keep track of both the Prefix and the Suffix plus any changes. <sigh>

[lneel]

Well, there is a unique shooter number in EZWinscore you could use....

Lee

[Graham Smith]

Well, there is a unique shooter number in EZWinscore you could use....

I was just looking at that. It's a sequence number assigned by the software as new records are created. It's probably as good as anything, but oddly it doesn't seem to always carry over into the registration file correctly - you end up with a 0 instead. Oh, well, that's a different subject...

[Rotwang]

Well, there is a unique shooter number in EZWinscore you could use....

I was just looking at that. It's a sequence number assigned by the software as new records are created. It's probably as good as anything, but oddly it doesn't seem to always carry over into the registration file correctly - you end up with a 0 instead. Oh, well, that's a different subject...

I get that when I add a new person directly from the registration form. So now when I need to add someone I go to the master database, add and save them there, and then go back to the registration form. You have to pick another letter tab and then go back to the original tab to refresh the list of names so you can choose the name you just added.

[wgnoyes]

I don't get this. I key in someone new at a match and their record absolutely is copied to masternames.db, even if another one is already there.

[Skydiver]

Unless all the clubs in a section share the same masternames database (and nobody has a match at the same time within a particular time window), the unique masternames record number is still not very useful to uniquely identify a shooter within a section. If that same shooter travels between two sections, now those two sections clubs will also have to share the same masternames database if you are going to use the record number as a unique identifier.

[wgnoyes]

Within a section? Um, okay, you have a couple of avenues. One, just get your clubs to export and send you the resulting .tsv file of the competitors within a match for your use within a spreadsheet or database app, or two, if you need actual scores, have the clubs create the club.txt files (which are intended to be forwarded to the section coordinator), who can then add the contained match results to (again) whatever spreadsheet or database app he is using to track shooter activity across a section.

[Rotwang]

I don't get this. I key in someone new at a match and their record absolutely is copied to masternames.db, even if another one is already there.

The record goes to the masternames database but the masternames record index number doesn't come back to the match entry record. So that field stays blank. If you later edit and save something on the match entry record like the email address you get another record for the person in the masternames database. If you allow the new person to shoot the match twice the second entry will have the master id number.

[wgnoyes]

I don't get this. I key in someone new at a match and their record absolutely is copied to masternames.db, even if another one is already there.

The record goes to the masternames database but the masternames record index number doesn't come back to the match entry record. So that field stays blank.

It appears to stay blank; I suspect it's actually there internally, for reasons which will become apparent.

If you later edit and save something on the match entry record like the email address you get another record for the person in the masternames database.

Not correct, and I tried it to make sure. I defined a new test match, threw a classifier stage at it, then registered a new shooter first name "rot" last name "wang" in open division. He showed no number in the "Master No." text box, and his record also appeared in masternames.db as 2984. Then I registered another competitor from masternames. Then I went back to "rot wang" in the match and changed his email address (it still showed no master no.) and saved it, and then went back to masternames.db. A second duplicate record had NOT been created and the original record 2984 in masternames now had the email address. Then I went back to the match and now the competitor has "2984" in the "master no." field. I'm running ezws 4.07.

If you allow the new person to shoot the match twice the second entry will have the master id number.

When I do those, I know they've already been placed into masternames.db and just go and register them from there, and yes, the masternames relative record number is there in the competitor's re-entry record at that point..

[Rotwang]

Not correct, and I tried it to make sure

...

More quotes than I wanted to deal with.

I haven't tried it in a few minor versions, so I'm happy its been fixed. I can skip my extra steps.

I tried it the same way in the latest version, now when you save the match entry record the master id record number shows as 0 instead of blank. If you edit the match entry record and save it the master record number becomes what it should be and the master record has been changed.

So each shooter in a group of matches will have a unique number that identifies them if the same masternames database is used to score all the matches.

Edited July 26, 2011 by Rotwang

[wgnoyes]

But if the idea is to track shooter scores in multiple matches across a section or other multi-club region, this is still useless to you. You still need to use either-or (or maybe a combination) of exported competitor information from the match plus the club.text section coordinator's results files that are fed into a spreadsheet or database and sorted by shooter name, not any kind of shooter's masternames.db relative record number. That's your best best.

[Graham Smith]

But if the idea is to track shooter scores in multiple matches across a section or other multi-club region, this is still useless to you.

This is a typical database problem - how do you uniquely ID someone. In USPSA, you should be able to use their USPSA number (suffix only) which would allow you to merge databases without a problem and keep multiple matches. But, that ID is subject to change if you switch to life membership and you have to be able to keep track of people who have a pending number. Basically, there's no foolproof way to merge data from two different databases without relying on comparison of (possibly inaccurate or changed) information.

As a simple example, while Bob Jones is waiting on his USPSA number, he shoots a match. Next month, he has his number and shoots a match as Robert Jones. To a computer, those are two completely different people and even a person trying to do a manual comparison could miss that unless they just happened to know the person involved. And if these matches are at two different places and you are trying to merge databases... forget it.

[wgnoyes]

That's one reason why I go to the trouble of registering people with their actual names as presented to uspsa and as exists on their records. (William G. Noyes as opposed to Bill Noyes. Plus they tell me it actually makes the monthly classification update go smoother in the case of someone in the match registered with a uspsa# of "PEN", which means they HAVE or WILL register before the cycle is run, but don't have the number yet. if someone is not a member and won't be one anytime soon, DON'T register them as "Pen"; it just causes the office additional work.) http://www.USPSA.org/uspsa-ezwinscore-verify.php gets me that. ezws actually changes the prefix now on masternames.db classification updates when someone goes from A to TY or whatnot, but it still can't make the leap from A12345 to L3210, because as already said, life members get a whole new number as befitting their status.

Edited July 28, 2011 by wgnoyes

[MoNsTeR]

But if the idea is to track shooter scores in multiple matches across a section or other multi-club region, this is still useless to you.

This is a typical database problem - how do you uniquely ID someone. In USPSA, you should be able to use their USPSA number (suffix only) which would allow you to merge databases without a problem and keep multiple matches. But, that ID is subject to change if you switch to life membership and you have to be able to keep track of people who have a pending number. Basically, there's no foolproof way to merge data from two different databases without relying on comparison of (possibly inaccurate or changed) information.

As a simple example, while Bob Jones is waiting on his USPSA number, he shoots a match. Next month, he has his number and shoots a match as Robert Jones. To a computer, those are two completely different people and even a person trying to do a manual comparison could miss that unless they just happened to know the person involved. And if these matches are at two different places and you are trying to merge databases... forget it.

The proper solution is a centralized system. If competitors and matches and scores were all managed online, in one system, there would be no issues with mismatched identifiers or redundant data. Unfortunately the reach of wireless internet is not nearly good enough to be able to rely on such a system "in the field" as it were.

That issue could potentially be solved with a sophisticated "offline mode" that stored registrations and scores in a temporary fashion that could be subsequently merged into the online database. Easier said than done though!

[Skydiver]

If we break the shackles to the version of PowerBuilder in EzWinScore, almost all of the modern SQL databases, as well as the newer object databases have built in support for replication and synchronization... even for database copies that go on and offline.

And most modern databases also will be network aware and let us have an easier time with using multiple computers for entering scores at a single match.

Edited July 28, 2011 by Skydiver

[wgnoyes]

Yeah, and I've pointed them at IBM DB2 Express before, which is free and whose licensing I seem to recall lets you redistribute it even if you're for profit (we're not) and it does all the client-server processing you want. But its also probably like CICS, IMS, MySQL and the like in that multiple files go into making up one database and now we're talking about export/import to copy matches around. Adaptive Server Anywhere has the distinct advantage that a "database" (that is, a match) is self-contained in a single .db file, so even the most unsophisticated user out there could receive and open a match file from someone else.

[MoNsTeR]

SQLite stores data in portable files, and is free, with broad driver and language support. If I were re-writing EZWS from scratch, that's where I'd look first (assuming you stick with the current use model). Honestly though export/import is a perfectly fine way to exchange data, provided the format is simple and well-known. You could probably export and then import a match database to/from XML with 3 lines of code each way.

If I were re-imagining the scoring system with a central server, I would not go the database replication route. It looks great on paper but it's an administrative nightmare. Instead I would think about a command-queueing mechanism that would let you speculatively make changes to your local copy offline, then synchronize with the central server later to fill in the gaps.