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.

I'm modelling data with multiplicity like this:

enter image description here

Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I model this?

Here is one possible representation with nearly enforced consistency (it doesn't enforce the 1+ Composition : 0+ Anthology multiplicity). However it duplicates FK_Composer lots of places (which as a side annoyance breaks some of my Entity Framework features).

Composer    Composition    junction          Anthology
--------    -----------    --------------    ---------
                           FK_Anthology   -> PK
PK       <- FK_Composer <- FK_Composer    -> FK_Composer
            PK          <- FK_Composition

Note: I'm also trying to hack this out at the business logic and ORM layers and have hit obstacles there as well.

share|improve this question
1  
I think you have the model right. The strict 1+ Composition per Anthology can be enforced by making an Insert (into Anthology) procedure that will also insert into the junction table (and similarly the Update and Delete procedures for the two tables, Anthology and junction, should take care not to leave any anthology unrelated.) –  ypercube May 2 '13 at 9:56
    
@ypercube: Thank you, that makes sense. Is it standard practice then to restrict direct inserts? The CreateAnthology() procedure will require a related Composition, which does leave a slightly distasteful asymmetry, since the first Composition to join an Anthology is not really 'special' per-se. That has me wondering how I will represent the interaction in code; it could perhaps accept an array of Compositions. –  shannon May 2 '13 at 10:16

1 Answer 1

up vote 5 down vote accepted

The question I would ask is whether the direct relationship between Anthology and Composer is "important" to the system? There are all kinds of incidental relationships between tangible things that are recorded in any system. However, only certain of these are important for the purposes of the system itself. These are the ones that belong in a relational schema.

If anthologies will always be made up of compositions, and compositions will always have composers, then you can always derive the relationship between anthologies and composers using a query. If you did it that way there would be no risk of inconsistent relationships.

This model would look like this:

ERD

You would have table definitions something like this:

create table Composer
( composer_id int IDENTITY
, composer_name nvarchar(50)
, biography nvarchar(1000) null
, other_composer_info nvarchar(1000) null
, constraint pk_composer primary key(composer_id)
);

create table Composition
( composition_id int IDENTITY
, composer_id int
, composition_name nvarchar(50)
, composed_on datetime
, constraint pk_composition primary key(composition_id)
, constraint fk_composition_composer 
    foreign key (composer_id) references Composer(composer_id)
);

create table Anthology
( anthology_id int IDENTITY
, anthology_name nvarchar(50)
, constraint pk_anthology primary key (anthology_id)
);

create table AnthologyItem
( anthology_id int
, composition_id int
, constraint pk_anthology_item primary key 
   (anthology_id, composition_id)
, constraint fk_item_anthology (anthology_id)
   references Anthology(anthology_id)
, constraint fk_item_composition (composition_id)
   references Composition(composition_id)
);

The good thing about this is that it has no redundant data to get out of synch. The problem is that it doesn't quite fit Shannon's requirement that an anthology be about one composer and that all of the compositions in that anthology must be from the same composer.

Unfortunately, this is not an easy problem to solve with declarative referential constraints. Declarative constraints are great for making sure that everything within a row makes sense. What they aren't built to do is enforce rules between rows.

There is a declarative way to solve this problem, but it involves a trade-off that many people wouldn't like, because it smells an awful lot like violating normalization. Some people would argue (Mike Sherril comes to mind) that this solution doesn't literally violate normalization rules, but people who are less well attuned to the actual rules of normalization will probably look at this solution with skepticism.

So what is this controversial solution? It looks like this:

ERD2

Note that the primary keys of some of these tables have been modified. Here is the SQL DDL for the solution: (You need to scroll it to the bottom to see the magic.)

create table Composer
( composer_id int IDENTITY
, composer_name nvarchar(50)
, biography nvarchar(1000) null
, other_composer_info nvarchar(1000) null
, constraint pk_composer primary key(composer_id)
);

create table Composition
( composition_id int IDENTITY
, composer_id int
, composition_name nvarchar(50)
, composed_on datetime
, constraint pk_composition 
    primary key(composer_id, composition_id) -- NOTE CHANGE!
, constraint fk_composition_composer 
    foreign key (composer_id) references Composer(composer_id)
);

create table Anthology
( anthology_id int IDENTITY
, composer_id int                            -- THIS IS NEW!
, anthology_name nvarchar(50)
, constraint pk_anthology 
    primary key (composer_id, anthology_id)  -- THIS IS DIFFERENT
);

create table AnthologyItem
( composer_id int                            -- THIS IS NEW!
, anthology_id int
, composition_id int
, constraint pk_anthology_item primary key   -- THIS HAS CHANGED.
   (composer_id, anthology_id, composition_id)
, constraint fk_item_anthology 
    foreign key (composer_id, anthology_id)
    references Anthology(composer_id, anthology_id)
, constraint fk_item_composition
    foreign key (composer_id, composition_id)
    references Composition(composer_id, composition_id)
);

Note that the way this works is you have to impose a single composer on an anthology by making the composer part of the anthology's primary key. You do the same thing with composition. Then, when you create an intersection between composition and anthology, you have the composer ID twice. You can then use a check constraint to declaratively enforce that compositions and anthologies have not only a single composer, but the same one.

NOTE: I'm not saying you should do this, I'm just saying you could do this. YMMV etc.

share|improve this answer
    
If I follow you correctly, I keep thinking the same thing and then realizing it doesn't work. If I don't include the composer relationship where it is, I can navigate from Composer1 -> Composition1 -> Junction1 -> Anthology1 -> Junction2 -> Composition2 -> Composer2. Now I have inconsistent object ownership, undefined at the far end. (My Anthology isn't about a single Composer anymore, in this metaphor.) –  shannon May 2 '13 at 17:13
    
@Shannon - Please see my expanded answer to address your question. –  Joel Brown May 2 '13 at 20:40
    
In your schema, I don't believe the composer is actually part of the primary key on the tables where it's been added, but rather merely part of the referential constraint. For example, additional tables related to Anthology and Composition do not actually have a composer_id column (for relation to Anthology or Composition, at least). Additionally, there is no need to specify the composer_id twice in the AnthologyItem table (avoiding the check constraint). That is what I was trying to convey in the table definition in my question. Does that have issues that you see? –  shannon May 2 '13 at 22:08
    
Oh, p.s. I do have the (seemingly circular) join back to Composer in my schema. It avoids the need to group/select unique relations from the junction table to get at a core relationship. –  shannon May 2 '13 at 22:10
    
@Shannon - You want to have the composer be part of the composite primary keys on each branch so that the composer key is propagated to the intersection table. You could always denormalize it anyway, but keeping it in the PK brings DRI to bear on the problem. You are quite right that there don't need to be two distinct composer keys, although that is what you'd get if you were using a modeling tool to gen your schema. Answer edited to remove the red herring. –  Joel Brown May 2 '13 at 23:38

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.