Re: parent-child relation with two children...

I hope the real names are not as vague and "meta-data-ish" as "Entities" and "id" in the real data. Being live and preview are attributes, if I understand your intent. But Ia m not sure about the key -- is it version_id or (version_id, version_type), so here is one guess:

CREATE TABLE Entities
(version_id INTEGER NOT NULL PRIMARY KEY,  version_type CHAR(1) DEFAULT 'P' NOT NULL

    CHECK (version_type IN ('P', 'L')),
 parent_version_id INTEGER
   REFERENCE Entities(version_id)); -- null is first prototype

> entity (id, live_version_id NULL, preview_version_id NULL)
> version (id, parent_entity_id)
>
> where version.parent_entity_id must be a valid entity.id, and that
> entity must have the version.id in either live/preview version_id. Is
> this a common set up? Does it make sense? Is there a way to express
> this requirement at the DB level (I'm specifically using MySQL, but in
> general I'd like to know)
>
> Or, did I model my data in a bad way?
>
> Thanks,
> Daniel.
> --
> Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/>
Received on Sat Aug 02 2008 - 16:44:27 CEST