18 replies to this topic

[WBSKI]

Is there some sort of standard that gives all ski areas a unique identifier (like an ISBN on a book) within the US (or globally ideally)? Just wondering, for website development purposes - thought someone here might know.

Thanks

[Bogong]

WBSKI, on May 15 2009, 03:09 PM, said:

Is there some sort of standard that gives all ski areas a unique identifier (like an ISBN on a book) within the US (or globally ideally)? Just wondering, for website development purposes - thought someone here might know.

Thanks

What about the resort logo? I know they change and it's not ideal or as foolproof as a regulated numbering system, but it does allow people to quickly recognise data relating to one area at a glance. I've put the resort logo next to the resort name for reports which compare data between resorts and people say that it makes it easier for them to find information relating to a particular resort.

. .

[WBSKI]

Hi, good idea - but that isn't really an option. This isn't really designed for people to recognize but rather for URL requests. Too bad we can't put images in URLs! You can't go to a URL like www.skiareasexample.com/United States/Crystal Mountain as there are multiple crystal mountains in the US. One could also use assigned numbers to ski areas, but how would another website know what URL to request if you used your own numbering system (for a web service)?

[AlphaBet]

You could use the name of the resort and the state. There's even a couple of different ways to do that:

www.skiareasexample.com/United States/Crystal_Mountain_WA
www.skiareasexample.com/United States/WA/Crystal_Mountain

That will work so long as there aren't two ski areas with the same name in any state.

[SkiBachelor]

I don't see why this that big of an issue. Personally, it makes better sense to have the primary key be a number than a name because if a ski resort changes its name, have fun going through all the different tables and making that change and it's better database design. The only advantage of using a name than a number for a primary key is that it's better for search engine optimization.

[WBSKI]

Hi Cameron,

I totally agree with you on this point, it makes no sense to have a name as a primary key! My point is to have a good web service (that other websites/apps can request data from), you need to have some sort of identifier for each ski area that is constant and also standardized across the web. If some website wants to call a web service for information about Whistler, they would have to know the automatically generated unique identifier in advance. On the other hand, if there was some standardized system, like isbn or country codes, then the client website would know what they are asking for (assuming they have a database of the ski area ids). The client could alternatively ask for a ski area by name/state/country, and the service could search the database for the most relevant record, but this poses a problem as well since the client may not get the expected ski area returned. I know that using the name is a problem from the experience of linking Skiingbc.info to Skimap.org. Some ski area pages on skiingbc.info say they do not have any maps, when in reality skimap.org can simply not find the ski area that skiingbc.info is requesting. This could be solved by a better algorithm for sure, but still, if a ski area's name changes, or skimap.org uses a different ski area name than the client, communication will be hindered. Therefore, I am looking for some sort of standardized system to tie everything together, so as to save the client website from manually linking IDs that are only relevant to one database rather than all ski area databases that use the system.

Makes sense? Maybe I should just start a small project to give each ski area a standardized unique identifier that is somewhat logical..

[iceberg210]

I like the idea of the numeric, especially I think this would be a good idea if you did a number that means something outside of just the actual digits. For example have the first two digits be a country code, you could even use the same country codes as they do for telephone numbers.

Then you could use a two digit number for province, or state. Then a number for the resort.

So let's say you were to do the sates in alphabetical order, then alphabetical, or really arbitrary for the resort.

So for example let's say you wanted a number for Eaglecrest Ski Area in Alaska the number would be

01-02-04

Cause it's in the US (international code 01) Alaska, (State 2 alphabetically) and then i just assigned 4 to it.

I've put together a database of all the US and Canada ski resorts and I'd be happy to make identifiers for them if we settle on the best pattern to do so by, so we can be standardized.

[SkiBachelor]

My solution would then be to use a popular site like OnTheSnow.com, and use its primary keys for the ski resorts on your website. While that might not sound like a good solution, OnTheSnow is the largest ski industry website and several applications are already using OnTheSnow.com to collect data.

Another solution though is to just create another column in your resort table with the identifier your client requests. So when they run a query, SELECT * FROM resorts WHERE resort_id_2 = 502; it will still make this match.

[SkiBachelor]

So after thinking about this issue over lunch, I believe it would be in your best interest to have your own PK system and then a column that is a foreign key for the primary key of another website because if you based it off the primary key of a current website like OnTheSnow, they're not going to have a key number for lost ski areas.

[WBSKI]

Certainly the internal website will still use the DB keys, however, I think it doesn't make sense to have a column for every other website, since this would have to be updated whenever the other website added a ski area to it ect.

I think I will go for a name based search system, that returns the most relevant id keys, which can be used to query the DB. Alternatively, the client can call based on a standardized system like iceberg210's. I am happy to run a script on skimap.org's db of several thousand ski areas and generate a special key for each as iceberg suggested. No need for you to make identifiers by hand! After I have done that, I will publish the up-to-date list of ski area names + stats + identifier in a couple formats.

I have an idea for another website which just deals with databases and client websites connecting to the databases, so this could be the start of that.. (you can add a ski area to that website and then you will get a identifier to use on your website).

A couple things still need to be worked out however. For instance, some ski areas span across regions and countries - how are these handled? Also how are ski area mergers handled. Solutions are welcome!

[SkiBachelor]

To handle ski areas in multiple states and countries, it requires a join table. So in the resort table, you would only store the most relevant information and then make the connection for a state and country in another table.

So for Lost Trail, Idaho

It may have the foreign key of 556 (pk in resort table), Idaho 42 and Montana 40 for their foreign key from the state table.

So in the join table, it would look like this

556 | 42
556 | 40

Then when you run your query, it will be based off both the resort and join tables and Lost Trail would appear in both the Montana and Idaho state lists. This same concept applies to the country table.

However, I feel you probably know about joined tables and how to make relationships with them.

[WBSKI]

I actually haven't done much with JOIN and never knew about this application of it. Makes sense, so if I understand you right, you have a ski area indexes table with repeating records for each region/country the ski area is in. Then in the ski area details table you have all the information that is relevant to the ski area regardless of which region/country the user is viewing it in?
I think a better approach to countries/regions is to use already standardized abbreviations


Using ISO standards is optimal: (for regions within countries)
http://www.commondatahub.com/live/geograph...6_2_state_codes
For countries (using alpha-2)
http://en.wikipedia....wiki/ISO_3166-1

So for instance: CA-BC-2 (Apex Mountain Resort)

Some work will have to be done on this front in overseas regions if we plan to always use region codes since my ski area db currently does not use regions for more obscure parts of the world.

[SkiBachelor]

Well, it's no the JOIN command that's used in a SELECT statement, rather just the name of a table when it consists of only foreign keys and other column attributes.

Quote

Then in the ski area details table you have all the information that is relevant to the ski area regardless of which region/country the user is viewing it in?

Exactly and the country and state reference will be made in the joined/weak table (a weak table is one where there is no primary key). However, most countries do break sections into states and providences like Germany and Switzerland, so therefore you should consider just having a table that contains states/providences so that you don't have redundancy with having to list the country id.

[WBSKI]

Understood. So one should have a countries table (from ISO), regions table (from iso as well), and a ski area join table and a ski area table.

[SkiBachelor]

That is correct.

[Aussierob]

Hey WBSKI,
In BC the BCSA assigns a number to each resort and lift. I believe they are all unique. IIRC Blackcomb is 140 and Whistler is 3, but would have to check.

[WBSKI]

Does BCSA have a list of these IDs? They don't seem to have a website. I could easily implement these into the standardized system like: CA-BC-[BCSA ID]

This post has been edited by WBSKI: 19 May 2009 - 08:05 AM

[Aussierob]

I'm on vacation so I'll see what I can round up.

[Kicking Horse]

WBski,

Colorado also has a Ski area ID and Tramway Id. I can supply you with both for all of Colorado if you want. This info is from the Tramway Board.