CODEWITHSUNDEEP
database-designforeign-keysentity-framework-4
Major Productions
Major Productions

Reputation: 6042

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

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:

Reviews:

Games:

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.

Upvotes: 0

Views: 141

Answers (2)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28718

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.

Upvotes: 1

Shlomo
Shlomo

Reputation: 14350

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.

Upvotes: 1

Related Questions