How to create record and assign to 1 of 3 sub-types, each sub-type with different properties

I'm constructing a schema for a MySQL database. I have a table called 'entry' (the supertype). An entry can be either a 'photo', 'essay', or 'video' (subtype). Each subtype has different properties/columns.

My current design calls for an entries table, and a separate table for each of the three sub-types.

The sub types are associated with the a record in 'entries' via a foreign-key to the entries table's id attribut. My question is, how can I mo开发者_运维知识库dify this design to restrict an entry to being associated with only one type of subtype. Currently, multiple subtypes can be associated with the same entries record.

You cannot easily do this declaratively in SQL. What you want to do is put a CONSTRAINT on the primary key that's shared across all four tables that the key must exist in entries (that's OK, it's a PRIMARY KEY) and that it must not exist in either of the other two tables. That second part has no corresponding constraint type in SQL.

You're basically stuck with TRIGGERs. (You can do this in some other engines with a CHECK CONSTRAINT that contains a sub-query, but I don't think MySQL supports CHECK CONSTRAINTs at all and some engines will not look outside the current row when evaluating a CHECK CONSTRAINT).

I'm not entirely sure that this is the best way of doing it, but here's an option:

CREATE TABLE `entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `type` enum('photo','essay','video') NOT NULL DEFAULT 'photo',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

My thinking here is that you enforce only one entry in the entries table per name, hence the UNIQUE KEY, and the type enum essentially informs you which table to join on.

This might be totally inappropriate depending on how you want to use / select the data out of the table(s), mind you.