Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I have the following tables
[syntax="sql"]CREATE TABLE `mc_routeclimbs` (
`climb_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`route_id` INT UNSIGNED NOT NULL, # FK
`user_id` INT UNSIGNED NOT NULL, # FK
`climb_date` DATE NOT NULL,
`climb_style` ENUM('OS','FL','RP') NOT NULL,
`climb_tries` SMALLINT UNSIGNED NOT NULL DEFAULT '1',
`climb_value` TINYINT UNSIGNED,
`climb_notes` TEXT NOT NULL,
`climb_dateposted` DATETIME NOT NULL,
PRIMARY KEY (`climb_id`)
)
CREATE TABLE `mc_routes` (
`route_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`area_id` INT UNSIGNED NOT NULL,
`route_name` VARCHAR(255) NOT NULL,
`grade` ENUM('3','4','5a','5a+','5b','5b+','5c','5c+','6a','6a+','6b','6b+','6c','6c+','7a','7a+','7b','7b+',
'7c','7c+', '8a','8a+','8b','8b+','8c','8c+','9a','9a+','9b','9b+') NOT NULL,
`route_notes` TEXT NOT NULL,
PRIMARY KEY (`route_id`)
)
CREATE TABLE `mc_areas` (
`area_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`country_id` INT UNSIGNED NOT NULL, # FK
`area_name` VARCHAR(255) NOT NULL,
`area_notes` TEXT NOT NULL,
PRIMARY KEY (`area_id`)
)
CREATE TABLE `mc_countries` (
`country_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`country_name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`country_id`)
)This is for an app in which people can manage their routeclimbs. The main action for the user will be to add routes. Each route is in a certain area, each area is in a certain country. So a user should also be able to manage the area's and countries. But that's not his main action.
User story:
1. user registers. Insert userdata in user table. user gets user_id. store in session.
2. user logs in. Wants to add a routeclimb. Fills in form with:
climb_date
route_name
route_grade
area_name
climb_style
climb_tries
climb_value
climb_notes
To be able to do this I need to:
- check in area table if area already exists
- if yes, retrieve area_id
- if not, insert area in areas table. Then retrieve area_id
- check in routes table if route already exists
- if yes, retrieve route_id.
- if not, insert route_name, route_grade together with area_id into routes. Retrieve route_id
- get user_id from session
- insert route_id, user_id, climb_date, climb_style, climb_tries, climb_value, climb_notes into routeclimbs
Bu there are a few issues
- these are a lot of queries for what is in fact one entry. Isn't there a way I could redesign my tables to do this differently? I know, premature optimization, but I'm still asking. Maybe there's a clean and simple alternative. Or is having 5-7 queries for one data entry ok? (I don't know what's usual)
Retrieving the climbs is a lot easier, in one big joint I can join all tables and retrieve all necessary data.
- data integrity. If for example the area doesn't exist yet, I insert an area, without having a country. Then the area table is not complete anymore. Of course I can let the user go to the area section at some other time and let him manage area's (and insert missing countries).
Maybe I can shuffle around with primary keys a bit or something (now all tables have INT primary keys, but I could also use areaname for example). On the other side, for the data integrity, my guess is that I did design the tables fairly well. There could be more area's with the same name, but in different countries, for example. So therefore it wouldn't be wise to use the areaname as PK.
Also, putting all data in one table (for example, the routename, routegrade, routearea) leads to a lot of duplication.
Sorry for the no-so-precise question, but I would just like to have some feedback on this, before I write a lot of code I can throw away in a few months..
feyd | Please use[/syntax]
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]