Database structure question

[mattrock23]

I am designing a site for a group of people that like to climb mountains. They want a page for each person listing the mountains they have climbed and also a page for each mountain which would list who has climbed it.

My question is how should I set up the database. I already need a table with a row for each user. My thought was to store the names of the mountains as one string and then explode() it to get each name. It seems like a waste of space to have a table with a row for each mountain and a string in there with all the names of all those who have climbed it since all that information is stored in the user table.

If I didn't use another table for mountains, I could go through each row of the user table, explode the mountain string, and return the name of the person if the name of the mountain is in the array somewhere. That also seems kinda wasteful and maybe slow.

Another thing is I need to have some way of standardizing the names of mountains. One person might say they climbed Mt. Shasta which would not match Mount Shasta. So I am brought back to the idea of having a table full of mountain names and people can just check a box if they have climbed that mountain and add another mountain to the list if they need to.

If there is anyone who has designed this kind of database and has a good solution let me know.

[sekasi]

I'd make one database with a couple of columns;

ID(key) | MountainName:VarChar | MountainHeight:Int | MountainLocation: VarChar

Then you can have one with the people, with all their info and then store the mountain ID in a column in the users, in an array format SQL wise so like .. 5, 7, 11, 17 if someone climbed those 4 mountains.

Something like that.

[jwilliam]

Here's how I'd tackle it:




CREATE TABLE users (
id int auto_increment NOT NULL,
... put whatever here ...,
PRIMARY KEY(id))
ENGINE=InnoDB COLLATE=utf8_swedish_ie;

CREATE TABLE mountains (
id int auto_increment NOT NULL,
name varchar(128),
... other mountain info here ...,
PRIMARY KEY(id))
ENGINE=InnoDB COLLATE=utf8_swedish_ci;

CREATE TABLE climbs (
user int,
mountain int,
date int, -- or a mysql date format... I always use unix timestamps
PRIMARY KEY(user, mountain, date),
FOREIGN KEY(user) REFERENCES users(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(mountain) REFERENCES mountains(id)
ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB COLLATE=utf8_swedish_ci;



Obviously, users holds the users and mountains holds the mountains... Then, when a user climbs a mountain you make an entry in climbs containing the id of the user and the id of the mountain, as well as the day he made the climb. (The date makes it possible for a user to have climbed a single mountain multiple times). You can access a user and his climbs (or a mountain and the climbs made on it...), using table joins. Google table joins if you're not familiar, and if you have trouble with them let us know.

In addition, the foreign key constraints really help to keep your database from getting all gunked up. Basically, if you change a user's id, or a mountain's id, mysql will automatically update the 'climbs' that correspond to those entries. If you delete a user or a mountain, mysql will automatically delete any appropriate 'climbs.' It saves a programmer a lot of time and debugging headaches.

[mattrock23]

Thanks for the replies. I am looking into table joins and mysql arrays. Table joins look pretty complicated, but it may be what I am looking for.

[Charleh]

jwilliams method is pretty much the exact way to do it. I work with databases for a living, you want a table which contains your base data, and then a table which contains the metadata which relates the two tables. i.e. one for the users and mountains (your data) and the climbs (metadata)

Joins are pretty simple - you will most likely only use two kinds of joins in a majority of queries, the INNER and the LEFT join.

Joins work by returning rows from tables based on a matching expression - data is returned by the result of the expression and the type of join performed.

There's a lot of tuts on joins out there to get you started, they are very simple once you know what they are doing - if you need any advanced queries you can ask me as I've written some absolute monsters to bring together data from all of our many systems at work!

[mattrock23]

(The date makes it possible for a user to have climbed a single mountain multiple times)

Will an error be thrown if there are two rows that are exaclty the same or something? What if they can't remember when they made some climbs?

[jwilliam]

Yes, an error will be thrown if you insert a mountain and a user twice with the date set to null each time. This is because the primary key consists of the three rows user, mountain and date. You could remove date as a primary key, but then each user could only climb a mountain once (as it pertains to the database...). I prefer having the date a primary key... it's just a more elegant solution than just user/mountain, or using an auto-incrementing integer as the id.

The climber doesn't need to remember the exact time, either. Just the month would be fine... or perhaps even the year, though I'm not sure how many mountains a climber goes up in a year because... well... I've never climbed a mountain.