When to split or merge tables
Posted: Mon Nov 06, 2006 4:20 am
I've been working on a db design and after normalization ended up with (among others) these two tables:
As you can see both contain the same columns. My question concerns whether that's a good thing. I split the data up in 2 tables because in the application most often there will be queries for either of these two. Most often there will be a SELECT, UPDATE or INSERT query for one or another. So even though "routeclimbs" and "boulderclimbs" share the same columns, they are sort of different things.
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)
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)