quote:

---

Originally posted by JeffK95Z

Hi Bas, i had a chance to look over the sql code for the db structure and i think its just about perfect!

the only questions I have, involve the tblconditions and how that works...

Could you explain how it would work?

---

quote:

---

Originally posted by JeffK95Z

another question Bas...

how are you storing passwords? Are you storing them encrypted?

crypt() is a wonderful function :)

---

quote:

---

Originally posted by ge-an_bas

Hi,

A typical LAMP setup here (Linux, Apache, PHP, MySQL). The server is my own (behind a fairly big DSL link) so I have complete freedom to do what I want.
The software is fairly old, a new server image is also in the making :-)

I placed the sql code at http://www.brijn.nu/test/hikerep.sql

Passwords will be changed later. I've normalized the db quite a bit. Not yet in are the counter for view of hikes and the relationship tables for GPS coords vs Town/Waypoint/...

All suggestions are welcome, I'm for example to familiair with standard DB naming conventions :-)

Bas

---

quote:

---

Originally posted by LongShadow

Heheh I've talked to people a number of times about revamping the DB here. I'm still game to host it on this site if someone is interested in putting some of it together. I have alot of ideas as well that would help tie it back to the forums, allow for photo tie in, trail feedback, stats, etc.

I think I've designed the DB a number of times but all that have volunteering the past have ended up bailing. I guess its alot of work.

Lemme know if you're interested.

---

<?xml version="1.0" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <xsd:element name="hikingTrail">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="name" type="requiredString" minOccurs="1" maxOccurs="1"/>
        <xsd:element name="description" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="location" type="hikingTrailLocation" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="statistics" type="hikingTrailStatistics" minOccurs="0" maxOccurs="1"/> 
        <xsd:element name="ratings" type="hikingTrailRatings" minOccurs="0" maxOccurs="1" />
        <xsd:element name="points" type="gpsPoints" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="directions" type="xsd:string" minOccurs="0" maxOccurs="1"/>
        <xsd:element name="details" type="xsd:string" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>

  <xsd:complexType name="hikingTrailLocation">
    <xsd:sequence>
      <xsd:element name="city" type="requiredString" minOccurs="1" maxOccurs="1"/>
      <xsd:element name="region" type="requiredString" minOccurs="1" maxOccurs="1"/>
      <xsd:element name="province" type="provinceValue" minOccurs="1" maxOccurs="1"/>
      <xsd:element name="park" type="xsd:string" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
  </xsd:complexType>

  <xsd:complexType name="hikingTrailStatistics">
    <xsd:sequence>
      <xsd:element name="distance" type="distanceValue" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="elevationStart" type="xsd:integer" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="elevationMax" type="xsd:integer" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="elevation" type="xsd:integer" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="averageGrade" type="averageGradeValue" minOccurs="0" maxOccurs="1"/>
      <xsd:element name="completionTime" type="timeValue" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
  </xsd:complexType>

  <xsd:complexType name="hikingTrailRatings">
    <xsd:sequence>
      <xsd:element name="condition" type="hikingTrailCondition" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
  </xsd:complexType>

  <xsd:complexType name="hikingTrailCondition">
    <xsd:sequence>
      <xsd:element name="trail" type="ratingValue" minOccurs="1" maxOccurs="1"/>
      <xsd:element name="road" type="ratingValue" minOccurs="1" maxOccurs="1"/>
      <xsd:element name="parking" type="ratingValue" minOccurs="1" maxOccurs="1"/>
    </xsd:sequence>
  </xsd:complexType>

  <xsd:simpleType name="provinceValue">
    <xsd:restriction base="xsd:string">
      <xsd:minLength value="2"/>
      <xsd:maxLength value="2"/>
    </xsd:restriction>
  </xsd:simpleType>

  <xsd:simpleType name="requiredString">
    <xsd:restriction base="xsd:string">
      <xsd:minLength value="1"/> 
    </xsd:restriction>
  </xsd:simpleType>

  <xsd:simpleType name="timeValue">
    <xsd:restriction base="xsd:decimal">
      <xsd:minExclusive value="0"/>
      <xsd:maxInclusive value="100"/>
      <xsd:fractionDigits value="1"/>
    </xsd:restriction>
  </xsd:simpleType>

  <xsd:simpleType name="distanceValue">
    <xsd:restriction base="xsd:decimal">
      <xsd:minExclusive value="0"/>
      <xsd:maxInclusive value="100"/>
      <xsd:fractionDigits value="1"/>
    </xsd:restriction>
  </xsd:simpleType>

  <xsd:simpleType name="averageGradeValue">
    <xsd:restriction base="xsd:decimal">
      <xsd:minInclusive value="-100"/>
      <xsd:maxInclusive value="100"/>
      <xsd:fractionDigits value="1"/>
    </xsd:restriction>
  </xsd:simpleType> 

  <xsd:simpleType name="ratingValue">
    <xsd:restriction base="xsd:integer">
      <xsd:minInclusive value="0"/>
      <xsd:maxInclusive value="5"/>
    </xsd:restriction>
  </xsd:simpleType>

  <xsd:simpleType name="utmCoordinates">
    <xsd:restriction base="xsd:string">
      <xsd:pattern value="[0-9]{1,2} [C-X] [0-9]{6,10} [0-9]{6,10}"/>
    </xsd:restriction>
  </xsd:simpleType>

  <xsd:complexType name="gpsPoints">
    <xsd:sequence>
      <xsd:element name="point" type="gpsPoint" minOccurs="0" maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:complexType>

  <xsd:complexType name="gpsPoint">
    <xsd:sequence>
      <xsd:element name="description" type="requiredString" minOccurs="1" maxOccurs="1"/>
      <xsd:element name="coordinates" type="utmCoordinates" minOccurs="1" maxOccurs="1"/>
      <xsd:element name="estimate" type="xsd:boolean" minOccurs="0" maxOccurs="1"/>
    </xsd:sequence>
  </xsd:complexType>

</xsd:schema>

quote:

---

Originally posted by ShadowChaser

I'm interested in programming it - can you run ASP.NET as well as ASP? It would make a lot of sense programming it in ASP.NET instead of a legacy language.

I'm sure we can get a little team together. The main thing I'm interested in is XML data sharing - the last thing the world needs is one more "locked down" trail database.

What database engine do you use on ClubTread?

---

quote:

---

Originally posted by JeffK95Z

I can't really speak for Bas, but I'm totally up for it...

Can we get a list of the features you had in mind LongShadow?

Also, this site runs on MS software? I'm seeing asp page extensions...

I'm much more familar with LAMP systems and enjoy the fact that its completely free. I believe Bas feels the same as it seems thats his choice as well.

Based on this sites OS, it may be easier for us to continue with a php/mysql solution rather then try to go for it with asp and the MS apps...

But lets keep the chatting going...

Ideally i'd like to get a full list of features before diving in, so it can be planned out, etc...

---

quote:

---

Originally posted by LongShadow
In terms of data elements for a trail, have a look at trailpaq.

---

quote:

---

Originally posted by LongShadow

Heheh I've talked to people a number of times about revamping the DB here. I'm still game to host it on this site if someone is interested in putting some of it together. I have alot of ideas as well that would help tie it back to the forums, allow for photo tie in, trail feedback, stats, etc.

I think I've designed the DB a number of times but all that have volunteering the past have ended up bailing. I guess its alot of work.

Lemme know if you're interested.

---

quote:

---

Originally posted by ShadowChaser
I'm interested in programming it - can you run ASP.NET as well as ASP? It would make a lot of sense programming it in ASP.NET instead of a legacy language.

I'm sure we can get a little team together. The main thing I'm interested in is XML data sharing - the last thing the world needs is one more "locked down" trail database.

What database engine do you use on ClubTread?

---

quote:

---

Originally posted by ShadowChaser

We will need to determine what the "root" element should be.

Trail? Hiking Trail? Hike? Something else?

* Listing by trail may not be ideal. Some trails stretch for days and days, when a portion of it may consitute a day-hike. Skyline II, Heather Trail, etc come to mind.

* Listing a hiking trail may not include other activities. Do we care? Do we provide a system within the schema to track random bushwhacking adventures, ala Bivouac?

My current proposal would be to list hiking trails. If a longer trail has a shorter "day-hike" segment that is clearly defined and has a fixed destination, it would be entered twice (once for the day-hike, once for the full hike - multiday). This would be a fairly infrequent thing. We could then add "Trip Reports" as a top-level entity with an optional field to "link" it to a specific trail. That way we keep the mountaineers and the hikers happy, and you'll still be able to show the trip reports for a certain hiking trail.

---

quote:

---

Originally posted by ShadowChaser

Here is a quick sample XML Schema I wrote based on my original InfoPath design:

** note ** please do not use this. I will be posting updated versions. Thanks :-)

<?xml version="1.0" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <xsd:element name="hikingTrail">

</xsd:schema>

---

quote:

---

Originally posted by ge-an_bas
Short summary :-)
I would like to continue on the LAMP platform (using PHP), but i'm very open for all suggestions.. Also, if somebody want source code, feel free...

---