locked
Movies & Series Tracking RRS feed

  • Question

  • I want to develop a database to help me keep track of my series and movies stored on my external hard drive. Does someone perhaps have a easy-to-use database design for this?

    Thank you


    a&h

    Tuesday, September 25, 2012 9:04 AM

Answers

  • Hi heinkasner,

    You can create a table named “Category” to store all the information related to series, and create another table named “Movie” to store all the information related to movies, and then add a foreign key in Movie table reference Category table record, for example:

    create table Category
    (
         ID int identity (1,1) primary key,
         Name varchar(100)
    )
    
    create table Movie
    (
         ID int identity (1,1) primary key,
         Name varchar(100),
         Released Datetime,
         CategoryID int not null foreign key references Category(ID)
    )
    
    insert into Category values ('A');
    insert into Category values ('B');
    
    insert into Movie values ('a1','2010-10-20',1);
    insert into Movie values ('a2','2011-05-07',1);
    insert into Movie values ('b1','2012-01-15',2);
    insert into Movie values ('b2','2010-11-26',2);
    
    -- To get all movies under category 'A'
    select m.* from Movie m join Category c on m.CategoryID = c.ID
    where c.Name='A';
    
    -- To get 'b1' related category name
    select c.Name from Movie m join Category c on m.CategoryID = c.ID
    where m.Name='b1';
    


    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 26, 2012 3:34 AM

All replies

  • You need to desing as per your requirmnet.

    You can create 2 tables, something like this :-

    table 1 = Location & LocationID

    table 2 = Movie_Series_Name , LocationID

    But again desing depand on your requirment.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, September 25, 2012 2:22 PM
  • Hi heinkasner,

    You can create a table named “Category” to store all the information related to series, and create another table named “Movie” to store all the information related to movies, and then add a foreign key in Movie table reference Category table record, for example:

    create table Category
    (
         ID int identity (1,1) primary key,
         Name varchar(100)
    )
    
    create table Movie
    (
         ID int identity (1,1) primary key,
         Name varchar(100),
         Released Datetime,
         CategoryID int not null foreign key references Category(ID)
    )
    
    insert into Category values ('A');
    insert into Category values ('B');
    
    insert into Movie values ('a1','2010-10-20',1);
    insert into Movie values ('a2','2011-05-07',1);
    insert into Movie values ('b1','2012-01-15',2);
    insert into Movie values ('b2','2010-11-26',2);
    
    -- To get all movies under category 'A'
    select m.* from Movie m join Category c on m.CategoryID = c.ID
    where c.Name='A';
    
    -- To get 'b1' related category name
    select c.Name from Movie m join Category c on m.CategoryID = c.ID
    where m.Name='b1';
    


    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 26, 2012 3:34 AM
  • Hi,

    it depends on type of information you need to store:

    e.g if you want to store movie(or series name) and location and released date.

    Just create one table contains (ID, name, location, releasedate).

    but if you want to store more information name, location, type(series or movie), release date, main actors, length, company, and so on

    you need to think about normalization because one movie may have more than main actor, and one movie may produced by more than one company.

    so you have tables like this:

    company (comid, name).

    Actors (actid, name, nationality, gender)

    Type (tid, type) type is series, movie or you may another one.

    movie(mid, tid, actid, comid, location, release date, length).

    and you may have more tables depend on the information you need.

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MCTS: SQL Server Administration/Development

    MyBlog

    Wednesday, September 26, 2012 6:09 AM