Database design schema how to create record and assign to 1 of 3 sub-types, each sub-type with diff properties?

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.

Up vote 0 down vote favorite share g+ share fb share tw.

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 modify 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.

---- EDIT ---- Forgot to change the name of the subtype tables when I created that diagram in google-docs. It should read photo_entry, essay_entry, video_entry as the table names. Mysql database database-design database-schema link|improve this question asked Sep 23 '11 at 19:15Casey Flynn1,2961735 93% accept rate.

I'm not sure if this is enforceable using a database schema in MySQL. Maybe adding a link table would help make it clear. – rfausak Sep 23 '11 at 19:21 @rhausak, I considered adding a link table with a foreign key pointing to record and requiring that to be unique, and having three fields - one for each type of sub-record and have that be a foreign key to each of the tables, then allowing all three to be null so only one could be populated, but this doesn't guarantee only 1 is populated – Casey Flynn Sep 23 '11 at 19:27 Well you could do entry_id (INT) | subentry_id (INT) | subentry_type (ENUM 'photo','essay','video') where entry_id is a primary key and subentry_id is unique.

– rfausak Sep 23 '11 at 19:32 See the answer and code in this similar SO question1. 1: stackoverflow.com/questions/4969133/database-design-problem/… – Catcall Sep 23 '11 at 0:41.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.