Database design are MarketId and SelectionId unique?

[cwfmoore]

Hi,

Currently designing a database to store racing data, as the topic says are the selectionId and marketIds unique, in other works is it safe to use them as primary keys in a database?

TIA.

[Big Bad Barney]

Hi,

Currently designing a database to store racing data, as the topic says are the selectionId and marketIds unique, in other works is it safe to use them as primary keys in a database?

TIA.

I'm always nervous about how accurate anything I say is, but;
Market Id is unique.
Selection Id is unique to a runner, but that same SelectionId can be in multiple markets, (Mr Spotty Brown horse runs once a week in different market will have the same SelectionId in both markets) so depending on what you're looking to achieve a composite or surrogate key will be necessary for your runners table.

Hope that helps.

[LinusP]

Hi,

Currently designing a database to store racing data, as the topic says are the selectionId and marketIds unique, in other works is it safe to use them as primary keys in a database?

TIA.

I'm always nervous about how accurate anything I say is, but;
Market Id is unique.
Selection Id is unique to a runner, but that same SelectionId can be in multiple markets, (Mr Spotty Brown horse runs once a week in different market will have the same SelectionId in both markets) so depending on what you're looking to achieve a composite or surrogate key will be necessary for your runners table.

Hope that helps.

+1 but watch out for a particular runner/team having different selectionIds this can be seen often in football.

1234: Manchester United
1235: Manchester Utd

Also be careful in saving marketIds as floats/decimals as they are strings.

[Euler]

I find that Betfair reuse ID's quite frequently. I've woken up on many a morning to think I've been trading one market only to see it populated with something completely different. So I string check to avoid that now.

[Derek27]

Perhaps adding the market date to the key will do the job?

[firlandsfarm]

Be careful if the selections have a number prefix i.e. Australian horses. The Selection name includes the number so it may be that in one race the Selection will have the name "3. Goodrunner" with an ID assigned but if it returns a week later with a different number, say "5. Goodrunner" it will have a different SelectionID. So with some markets you need to strip out the number details to group races for the same selection.

[ShaunWhite]

Some dogs get assigned more than one selectionID too.

But marketId + selectionId is unique though.

[LinusP]

Some dogs get assigned more than one selectionID too.

But marketId + selectionId is unique though.

+ handicap

I forgot about that edge case..

[Big Bad Barney]

Some dogs get assigned more than one selectionID too.

Why is this do you guys think? Is it just the data entry guy at Betfair is asleep? or some other more tangible reason?

+ handicap

I forgot about that edge case..

Do you mean the MarketId, SelectionId, and Handicap has to be the composite key? or is handicap the reason for different selectionId's? (I still have a lot to learn and Handicaps is one of those things:))

[ShaunWhite]

Some dogs get assigned more than one selectionID too.

Why is this do you guys think? Is it just the data entry guy at Betfair is asleep? or some other more tangible reason?

If it's a manual process then it'll be prone to error (esp at $10/hr), I'm suprised there aren't more typos when there's 100s of 000s of registered names.

I sometimes wonder if it's partly because the trap number is in the dog name. Perhaps someone/something sometimes searches for 3.FastFlash and doesn't find it, so adds it, when they should have just searched for FastFlash. Who knows.

[Wolf1877]

All sorts of crap happens at Betfair! ...... probably why their system crashes so often but to be fair it is quite a challenging commercial environment with everything being so time and sequence sensitive.

Market ID can also change for a market. Usually where a market has been screwed up by Betfair in some way so they just permanently suspend/void the original Market ID and just start again with a brand new market id. So if you have a race loaded in Guardian it just stops updating for an extended period (...... however a lengthy suspension can also happen if a market has to be manually unsuspended and it gets overlooked). I've not personally noticed a Market ID actually reused but then Peter undoubtedly probably trades every market going!

Other stuff that happens is that horse names can be duplicated (both current and reuse of). There was a Scottish meeting had 2 races on the same day with identically named horses but which were actually completely different horses - one was French registered. I think UK/IRE seem to have their act together with avoiding duplicating current horse names.

I've also seen markets where a selection is dropped (i.e. it apparently becomes a non runner) and then it becomes a runner again later - presumably where the betfair bod had accidentally marked a runner as a non runner and then then reinstated the runner 30 minutes later but decided not to bother completely voiding the market and starting again.

On UK and Ireland markets I go with Race DateTime and VenueCode. if the 17:45 at Kempton doesnt start til 18:30 because of a trakside incident it still stays the 17:45 in the records. Ditto if actual scheduled start times are pushed back 5 minutes to spread out races better if another meeting is abandoned due to weather etc. Not sure the same applies in the aussie and US markets.

Rule number one seems to be there are no hard and fast rules!
I'd try and build a database of horse name, country registered, date foaled etc and then link it to current trainer etc but then you need to go beyond Betfair data to do that.

[Anbell]

Hi,

Currently designing a database to store racing data, as the topic says are the selectionId and marketIds unique, in other works is it safe to use them as primary keys in a database?

TIA.

One thing to consider (I dont actually know the answer) is how BF handles win/place. ie are they the same mktID? Same with selectionId. I presume not.

[ShaunWhite]

One thing to consider (I dont actually know the answer) is how BF handles win/place. ie are they the same mktID? Same with selectionId. I presume not.

Different marketIDs (usually place market id = win market id +1)
Same animal, same selectionId

You can see the marketId in the address shown in your browser when you view a market, and the marketId/selctionId is shown in the same place when you view a selection's chart.