Database design w/Entity Framework 4 - where should I put my foreign keys?

Question

I'm developing a couple of media (video games, TV shows, movies) review sites and am having an issue designing the relationships between my data. Case in point:

I decided that having a base Content table would be a good idea for most of the data. My Articles, News, and Reviews tables could all point to the Content table. My current setup is:

Content:

• ContentID - int, primary key, identity
• Text - text
• DateAdded - datetime

Reviews:

• ReviewID - int, primary key, identity
• Score - smallint
• ContentID - int, foreign key that points to Content.ContentID

Games:

• GameID - int, primary key, identity
• Title - nvarchar(50)
• GenreID - int, foreign key that points to Genres.GenreID
• ReviewID - int, foreign key that points to the Reviews.ReviewID

I'm getting a bit thrown off because the Entity Model is showing a 1-to-many relationship between Content and Reviews, and between Games and Reviews, when they should really be 1-to-1. Each review should point to one content entry, each game should have a review, and there should be only one review per game.

I'm just wondering if I'm on the right track.

Answer 1

What's the Text field in Content for? I would rename it to something more descriptive.

It's a good database design, but more flexible than your needs. The one change I would make is to make the GameId not be an identity, rather rename it to ContentId and make it a FK onto the Content table, that way you can search for games/not-games at the same time. After that I would remove the ReviewId column from the Games table (not necessary since you can join by Reviews.ContentId field).

Your design does allow for multiple reviews per content piece... I would imagine you will want this flexibility: If you don't have multiple reviewers now, you may have it in the future.

Answer 2

EF is correct, because your model allows multiple Reviews to share the same Content, and multiple Games to share the same Review.

At the same time, your model is logical so I wouldn't worry about what EF thinks. You should be able to delete the 'Navigation Properties' that you don't need if you want - i.e. delete the Reviews property on the Content entity (in the designer). This won't give you any benefit but may make things simpler for you.

If you are certain that every Review should have its own Content and so forth, you should make ContentID unique in the Review table. It is not incorrect to make ContentID the primary key of the Review table, either - in this way the Review becomes an extension of Content. However you need to consider if this meets your needs.