Code: Select all
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_notes` TEXT NOT NULL,
`climb_dateposted` DATETIME NOT NULL,
PRIMARY KEY (`climb_id`)
)
CREATE TABLE `mc_boulderclimbs` (
`bclimb_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`boulder_id` INT UNSIGNED NOT NULL , #FK
`user_id` INT UNSIGNED NOT NULL, #FK
`bclimb_date` DATE NOT NULL,
`bclimb_style` ENUM('OS','FL','RP') NOT NULL,
`bclimb_tries` SMALLINT UNSIGNED NOT NULL DEFAULT '1',
`bclimb_notes` TEXT NOT NULL,
`climb_dateposted` DATETIME NOT NULL,
PRIMARY KEY (`bclimb_id`)
)To compare it to something else, think about records and cds. Both can be described with the same columns, but are different things. People would be looking for cd's or records, not both at the same time.
However, if I would just merge them in one table and use 1 field `climb_kind` ENUM ('route','boulder') it would eliminate a lot of duplication. Both in the database and in the php logic. I can imagine I can reuse all my queries for one table and just add a WHERE climb_kind = '$var' clause. If I were to use the 2-table design I would have to duplicate all queries (as they are in fact the same for both tables).
So am I right to think that I can better merge these tables in one?
(at first another reason for splitting them up was performance. But as I've found out that will probably not be an issue if the queries are written well and the tables indexed)