Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Need a little help with database structure

    I'm building a web app backed by a MySQL database.

    The app will allow users to register, create lists, add items to a list and if the item isn't already created then create an item and add it to a list.

    So, for example, the user creates lists for movies they want to watch. One list might be 'To watch in 2014', another could be 'Horror movies' and a final list called 'To watch with friends'

    They will then be able to search a database of films, find the film they want and add it to one of their lists.

    So far, the database structure would be something like:

    Code:
    Users
    
    id
    name
    email

    Code:
    Movies
    
    id 
    Name
    Code:
    Lists
    
    id
    list_name
    user_id
    Code:
    User_Lists
    
    id
    movie_id
    list_id
    So the user can create a list and add movies to the list.

    Now, if a movie doesn't exist in the database I want the user to be able to create it and add it to their list but not appear in the search results with the pre-defined list of movies. However, at some later date some of these user generated may be added to the 'master list' in the Movies database.

    Can someone advise on how this new table for user generated films might look, how it will interact with the other tables and how the other table structures might need to change to accommodate it?

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    put it in the same table as other films but with some identifier (column) which indicates if this entry is approved or verified

    if you want additional information on these user define entries then have a sub table with a one to one relationship which contains that additional information. have a look a the sub/super type model
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2012
    Posts
    3
    That's great thanks.

    So, I could either put them in one table as follows:

    Code:
    Movies
    
    id
    name
    approved
    created_by
    Where approved is 1 by default for admin created entries, 0 by default for user created entries (with the option of updating to 1 if approved by an admin) and possibly a 'created_by' column with the user id of the user that created the entry.

Posting Permissions

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