SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    what is the best way of creating this database?

    I am working on task manager website(a to-do list) but having difficult time on creating my database tables.

    a school will be using this site so there will be thousands of people using this site. for this reason database must well organized and should easily retrieve the data.

    in this website students will register and create an account.

    they will be able to create/delete unlimited categories.

    under these categories they can create/delete unlimited tasks.

    users can also create/delete unlimited tags. Tasks will be assigned to these tags.

    here is what i got, let me know what do u think?

    user table
    unique_id
    name
    email
    -----------------
    Category Table
    category_uniqe_id
    unique_id (foreign key)(where I contact to user table)
    category_title

    task list table
    task_unique_id
    category_uniqe_id (foreign key)
    task_title


    tag table
    tag_unique_id
    task_uniqe_id (foreign key)
    tag_title

    if i use above schema, there might be millions of row under every table but user table, and that will cause large database…

    what is the best way to set this up?

    Thanks in advance.

  2. #2
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This design looks good to me. Make sure the title fields in the task and category tables are paired with the unique id of the user to make a unique key. This will ensure one user doesn't create the same tag or category twice.

    I am a little concerned about the tag table though. Are you assigning tags to tasks for individual users? Or for the database as a whole? Right now it looks like you're doing it for the database as a whole. If this is meant to be for individual users, then you should add a user foreign key there in the tag table. And also make sure to triple up the tag_title, task_unique_id, and user_id as a unique id so the same user can't create the same tag for one tag TWICE.

    Hope all this makes sense.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your task list table has a foreign key to the categories table, so this unfortunately means each task can be assigned to only one category

    similarly, your tag table has a foreign key to the task list table, so this unfortunately means each tag can be assigned to only one task

    you need a couple of many-to-many tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply Rudy and svcghostm

    so if i remove foreign key to categories table from task list and add user_unique_id, that would assign each task to its creator, right?

    task list table
    task_unique_id
    user_unique_id (foreign key)
    task_title

    for the tags, i still have no idea... how about this, I know still need to connect to tasks.

    tag table
    tag_unique_id
    user_unique_id (foreign key)
    tag_title

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ty1903 View Post
    so if i remove foreign key to categories table from task list and add user_unique_id, that would assign each task to its creator, right?
    right

    but how many categories can a task belong to?


    Quote Originally Posted by ty1903 View Post
    for the tags, i still have no idea... how about this, I know still need to connect to tasks.
    that's fine, but yeah, you still need a tag-to-task table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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