Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31

    Unanswered: SQL 2008 How to redesign this database (multiple records in one cell)

    Hello,
    I'm building small movie database, and I have table which contains informations like "title" "year" "rank", and one of my columns is "genre". I have a table of genres with their ID's, so I can create foreign key, and write genre ID in this column. But here is problem: one movie can match few genres. How can I implement it? So far, I have one solution: write genres in this column, separated by comma. But this solution require to delete foreign key and whole genres table, and store them as plain text, which is making harder building any statistics in future.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It would be a very bad idea to store multiple values in one column, as you already indicated.

    What you described is a many-to-many relation. A movie can belong to many genres. A certain genre can relate to many movies.

    What you need is an extra table, a cross table, with a composed primary key. One part of the PK is a Foreign key to the table Movie and the other part of the PK is a FK to the table Genre.

    You can also create this table with an extra column to store the PK, a sequence for example, and create both FK's as ordinary, non-PK columns.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    Thanks for your answer!
    This solution seems to be right, but can you send me this cross table? I'm beginner in databases, so I'm not sure, how it should looks like.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CREATE TABLE movies
    ( id INTEGER NOT NULL PRIMARY KEY
    , title VARCHAR(37) NOT NULL
    , yr SMALLINT
    , rank SMALLINT
    );
    CREATE TABLE genres
    ( id INTEGER NOT NULL PRIMARY KEY
    , name VARCHAR(37) NOT NULL
    );
    CREATE TABLE movies_genres
    ( movie_id INTEGER NOT NULL
    , genre_id INTEGER NOT NULL
    , PRIMARY KEY ( movie_id, genre_id )
    , FOREIGN KEY ( movie_id ) REFERENCES movies ( id )
    , FOREIGN KEY ( genre_id ) REFERENCES genres ( id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Wim
    You can also create this table with an extra column to store the PK, a sequence for example, and create both FK's as ordinary, non-PK columns.
    Why ?
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2008
    Location
    Poland
    Posts
    31
    Thanks for code r937!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •