Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I originally thought I was going to have a one to many relationship in a database with the following tables

ENTITY_JSON_MAP
ATTRIBUTE_JSON_MAP

Originally I was going to have an FK in ATTRIBUTE_JSON_MAP pointing to ENTITY_JSON_MAP. I was going to the Attribute table with

alter table ATTRIBUTE_JSON_MAP add unique (NAME, JSON_MAP_ENUM, ENTITY_JSON_MAP_FK)

Now however I've realized that I need to make it a many to many relationship, so I've created a mapping table, but now I've lost the ability to make sure that I don't get duplicates of that combination.

I was hoping there was still a way to do this. Inserting/deleting tables doesn't need to be fast, as the table will not be inserted or deleted at run time.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

If I understand you correctly, your original

Unique (name, json_map_enum, entity_json_map_fk)

Is made up of

name: name of the attribute json_map_enum: the position of this attribute in the entity entity_json_map_fk: reference to entity

You should be able to enforce this in the mapping table as well.

CREATE TABLE Entity_Attribute_Map (
    ATTRIBUTE_JSON_MAP_fk int references ATTRIBUTE_JSON_MAP(id),
    entity_json_map_fk int references entity_json_map(id),
    JSON_MAP_ENUM int,
    Primary Key (ATTRIBUTE_JSON_MAP_fk, entity_json_map_fk, JSON_MAP_ENUM)
)
share|improve this answer
    
So that will make it so I'll never have an entity mapped to two attributes with the same name and enum? –  DrSammyD Oct 25 '12 at 13:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.