MySQL数据库规范化和最佳实践

Question

I've got two tables:

CREATE TABLE `ch_nav_items_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `menu_id` int(11) DEFAULT NULL,
  `parent` int(4) DEFAULT NULL,
  `displayname` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `link` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `link` (`link`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

CREATE TABLE `ch_houses_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `displayname` varchar(30) DEFAULT NULL,
  `alias` varchar(30) DEFAULT NULL,
  `street` varchar(100) DEFAULT '',
  `zip` int(5) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `alias_link` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `alias_link` (`alias_link`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8

that I want to normalize.

In ch_nav_items_test I store any navigation link the website has. menu_id is to distinct between frontend, backend, footer ... navigation. Parent is to build multilevel navigation. A house link would be on a second level.

When I create a new house, it'll have a display name (e.g. House Magic) and an automatically created alias (e.g. house-magic). This alias would be the link, too, with a prefix of "houses/" (e.g. houses/house-magic would be the link in ch_nav_items_test). The display name is stored in both tables atm but imho I can't use a foreign key, because the nav items table not only has house links, so there would be values that do not exist in the houses table. I am not able to build this foreign key.

What I end with is:

• ch_nav_items_test.displayname = "House Magic"
• ch_nav_itmes_test.link = "houses/house-magic"
• ch_houses_test.displayname = "House Magic"
• ch_houses_test.alias = "house-magic"
• ch_houses_test.alias-link = "houses/house-magic"

The ch_houses_test.alias-link I created temporarily to try to link to the nav link column with a foreign key without success.

It would be great to have a foreign link, because when I delete a house, I need to delete the related nav item link, too. Also, if I change the name of the house, the link in nav items needs to be changed, too. And so the alias does.

Atm I'm stuck at how to normalize this and I don't know what is best practice here. I'm okay with completely redefine my table structure but I want to keep it very simple.

I know that I can handle all this in php code, what I would normally do, because I'm more a php coder than a mysql expert, but if there is a simple way to handle most of this with few queries, I'd prefer this.

Answer 1

This problem can be described as an analogy in OOP. If you need several classes to have the same kind of property or behavior. What would you do? You create an interface to group these classes.

Now back to your example, the nav_item is the common property of house and other tables. What you need is a parent table or an interface that nav_item table can refer to. Let's call this table links and define it like this

CREATE TABLE `links` (
  `id` varchar(60) NOT NULL CHARACTER SET latin1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then the houses table and other tables can create their own links and refer to

CREATE TABLE `ch_houses_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `link` varchar(60) NOT NULL CHARACTER SET latin1,
  PRIMARY KEY (`id`),
  CONSTRAINT FOREIGN KEY (`link`) REFERENCES `links` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8

Finally, the nav_item can also refer to links belong to house or other entities

CREATE TABLE `ch_nav_items_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `menu_id` int(11) DEFAULT NULL,
  `parent` int(4) DEFAULT NULL,
  `displayname` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  `link` varchar(60) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FOREIGN KEY (`link`) REFERENCES `links` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8

When you want to delete or update both the house and nav_item link, simply delete or update their corresponding record in links table. And in case you want to extend nav_item to other entities just refer these entities to links table.