Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Unanswered: Database design ideas

    Hi,

    I'm starting to make an application dealing with movie collections, practically a movie collection management application. Basically it will be a web app (asp.net with mvc), with users who once authenticated will be able to create, modify, view and delete their collection of movies. Besides that, they will be able to create custom movie collection (just as imdb allows) who can be viewed and commented by any other user (registered or not). I started the design of a database and so far it looks clean but i think is still missing functionallity. I attached the diagram of the db, and maybe some of you will give me hints or tips about how the design can be improved, either logically or by adding new features.

    Thank you.
    Attached Thumbnails Attached Thumbnails Screenshot (2011-10-19 at 07.29.30).png  

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you comment your data model in words?

    I want to see all films produced by Steven Spielberg. How can I do that?

    What if I want to see all the different people who have directed the films in my possession.

    I mean, there are other roles in making a film than just actors.
    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
    Oct 2011
    Posts
    5

    Smile

    Of course, the application will have combo boxes to filter information about movie data. As i said, the two main menu options will be, once a user logs in, for example: "My movie collection" and "Custom movie lists". At least these are my ideas for now. If you can give me more ideas, i'll be grateful.
    Once "My movie collection" is selected another page containing various filters will be displayed (e.g. Filter by year, by actor, by director, by genre, etc) or even a multi column filter to show data from any of these field, based on the string typed by the user.
    Data from some of the fields (actor, director) will be displayed as link so that when pressed will display all the movies of that actor. And so on...
    For director, i used the same table "Actor", that contains a field is_director. I choose to use the same table because basically it is the same information. I'm not sure if this is quite the way to do it, but correct me if i'm wrong.
    The "Custom movie list" will be practically a list of movies, viewable by any user, either registered or not. The owner of the list will be able to modify, delete or create a new list.
    At least that's the ideas i have for now. If you have a more creative mind than mine and have any other ideas, please share them
    Also, the main reason for doing this is to improve my database design skills. So, in order for this database to become more challenging, i think the application should be more complex, with more functionallity if needed.
    But for the current workflow of the application, i can' make the database more complex than it is.

  4. #4
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Have you used indexing (non clustered) for some of the search specific columns for performance improvement

  5. #5
    Join Date
    Oct 2011
    Posts
    5
    I didn't thought about indexing, because i don't know how big the database will be. But is a good idea to put an index at least on movie name.

    Thanks !

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    For director, i used the same table "Actor", that contains a field is_director. I choose to use the same table because basically it is the same information. I'm not sure if this is quite the way to do it, but correct me if i'm wrong.
    Yes, it is the same information, but the way you solved it is not the way to go. I don't expect to find a director in a box labelled "Actor".

    I'd change the name of the table to reflect the fact that it contains all the people who have collaborated to create films.

    (You should reconsider the naming of your tables, like the table "Movie" is the junction table between MovieDetais and Actor?) In Movie, you could add a FK to a reference table that indicates the function/profession/role/task of that person in that film. The same person can perform multiple tasks in the very same film, I heard Sylvester Stallone was famous for that: actor, director, producer, ... in the very same film.

    If you ever want to store information about the script writers, you only have to add a record in the reference able, instead of altering your data model, provide an extra bit column, adjust a number of modules in your program so they can use that extra column. And then next week to do it all again to add the "special effects guy".

    I think there are a few film websites on the internet. Google for them and gather some ideas from what they offer. Then adjust your data model to mimic (part of) their behaviour.

    You will certainly learn a lot. Have fun.
    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

  7. #7
    Join Date
    Oct 2011
    Posts
    5
    Yes, you're right. I changed the name of the "Actor" table to "MoviePeopleInvolved" (i don't know how fortunate this name is but...no other ideas for now ) I also created a table "MovieRole" which will keep every person's role in a movie (actor, director, etc.).

    You told me that i should link "Movie" table with "MovieRole" ...But why with "Movie" and not with "MoviePeopleInvolved" ? It makes more sense, in my opinion. Maybe you are right, but for now, i don't see why. Maybe you can give me some details about this.

    Also the junction table "Movie", it's a bit annoying in the sense that i don't know how to rename it and what else should i add to it. I know though that i need it in order to keep the database normalized.

    Thanks for your advices !
    Attached Thumbnails Attached Thumbnails db_diagram.png  

  8. #8
    Join Date
    Oct 2011
    Posts
    5
    Sorry, my mistake...i figure it out now that it makes sense to link "MovieRole" to "Movie", because each role is different from movie to movie....My bad!
    Attached Thumbnails Attached Thumbnails db_diagram.png  
    Last edited by oliv29; 10-21-11 at 19:57.

  9. #9
    Join Date
    Sep 2013
    Posts
    1
    Thanks for your advices

Posting Permissions

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