CODEWITHSUNDEEP
sqlentity-frameworkforeign-keys
N. Odeh
N. Odeh

Reputation: 35

how to update column rather than ID in many tables

I have a question on how to update several columns in different tables.

I'm using Entity Framework with ASP.NET

The idea started from here : suppose I have the following tables:

CREATE TABLE Movies
(
    Id int,
    Title varchar(255),
);

CREATE TABLE Genres 
(
    Id int,
    Name varchar(255),
);

CREATE TABLE MovieGenres
(
    Id int,
    MovieId int,
    GenreId int,
);

So the first table will contain movies, the second for genres and the third table will contain genres for each movie, (each movie can have more than one genre)

What is best, to keep the third table as it, so when I want to get genres of any movie, I get all the GenreID from the third table then I get the name of each genre from Genres table?

or:

to add a new column to the third table which is the GenreName, so when I insert a new genre for any movie, I insert the MovieId, GenreId, GenreName, then when I want to get genres for any movie I select the genres names from the third table directly? if so, how I can use ON_UPDATE to do this? I mean if I update the name of any genre in the second table, the genres names get updated automatically also on the third table.

I want to know which method is the best from concept and experience point of view.

Upvotes: 0

Views: 46

Answers (1)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

"to add a new column to the third table which is the GenreName, so when I insert a new genre for any movie, I insert the MovieId, GenreId, GenreName"

It is a bad idea. Your third table just should store Id information of Genres and Movies tables to associate them. Firstly, It causes redundant Name information. Imagine that, MovieGenres table stores more than one GenderId info and so the table stores the Name information more than one. If you want to update just one GenderName, you must update all MovieGenres rows. It is not managable and maintainable.

Simply use join keyword in your queries to select them together or you can use navigation properties for Entity Framework.

Upvotes: 1

Related Questions