1

Per excellent advice I received on a recent question (Database design problem), I am incorporating a super-type/sub-type pattern in a DB I'm building for an MVC2 app. I'll be using Entity Framework to provide the models to MVC.

The super-type is Publications, while the sub-types are Articles, BlogPosts, etc. Each sub-type table will have a 2-column composite primary key (pub_id, pub_type), with foreign keys that reference corresponding columns in the super-type table

For data integrity purposes (see lengthy comments on accepted solution to original question) the super-type table should not include the pub_type in its primary key (unlike the sub-type tables),

And that is where Entity Framework doesn't seem to play along. Warnings I get when I generate the .edmx file from my existing database:

The relationship 'FK_Articles_Publications' has columns that are not part of the key of the table on the priamry side of the relationship. The relationship was excluded.

(etc.)

QUESTION: is there any way to coax Entity Framework into mapping the relationship given the foreign keys I want (or will I have to compromise the DB design and set a composite key on the supertype table)?

If not, this adds the additional problem of requiring that joing any other, non-sub-type table (and I plan on several) to the super-type table requires that I have columns corresponding to both pub_id and pub_type. For example, I want a topics table to be able to associate with any kind of publication via the super-type table -- I would need to store (redundantly) the pub_type in a column in the topics table.

I am quite new to EF (and ORM), but it's power is alluring, and I don't want to give it up.

Community
  • 1
  • 1
Faust
  • 15,130
  • 9
  • 54
  • 111

2 Answers2

2

In this table,

CREATE TABLE publications (
  pub_id INTEGER NOT NULL PRIMARY KEY,
  pub_type CHAR(1) CHECK (pub_type IN ('A', 'B', 'P', 'S')),
  pub_url VARCHAR(64) NOT NULL UNIQUE,
  CONSTRAINT publications_superkey UNIQUE (pub_id, pub_type)
);

it matters that {pub_id} is unique and that {pub_id, pub_type} is unique. (The column pub_type should also be declared NOT NULL.) In the first case, uniqueness guarantees identity. In the second case, uniqueness guarantees that subtypes reference the right kind of row in the supertype. But it doesn't matter much to the database engine which one is declared PRIMARY KEY and which one is declared UNIQUE. Both can be the target of foreign key references.

If a dbms were designed around current relational theory, it might support only KEY declarations instead of PRIMARY KEY and NOT NULL UNIQUE.

So if your ORM can't cope with constraints the way they're written, it's ok to make {pub_id} the primary key in all those tables, and to declare the reducible superkey {pub_id, pub_type} to be unique in all those tables.

I don't know whether that will silence EF. You should test tables that reference the subtypes as well as tables that reference the supertype.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • to my rescue again! I've up-voted this answer. I'll set up that design, and if it plays well with EF, I'll accept your answer. – Faust Apr 01 '11 at 11:20
  • OK, though I cannot get EF to accept the keys as originally prescribed (I am going to have to use a double PK in the super-type and include pub_type in joining tables that are not sub-types) your answer here makes it clear that this doesn't compromise the relational integrity as long as I define the constraints properly. Thanks again. – Faust Apr 01 '11 at 22:54
0

Try the Table Per Hierarchy inheritance, it looks like it suits your design rather well.

Devart
  • 119,203
  • 23
  • 166
  • 186