I have something like:
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/>

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal

Hi,
If I read this correctly, your saying

you have an entity, The entity is either Live or Preview, and the
entity has versions.

I have seen this before, and I believe it is an error in your model.

what you really want here is the entity table and 2 extension tables
of entity one for Live and one for preview.

Like this

Entity(pk_entity),
EntityLiveVersionExtension(pk_entity,LiveVersionID ,version),
EntityPreviewVersionExtension(pk_entity,PreviewVer sionID,version),


All of them have the primary key of pk_Entity and the extensions are a
one --> zero or one relationships


This is one of the ways to handle Object Inheritance within a
relational database. I am guessing you have (or at least could have)
an entityBase and a PreviewENtity and a LiveEntity.

HTH

Tal