Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Help! on this simple Database design

    I'm new to database design and I'd like to see if anyone can help me with this. The database will store comments/issues about projects so comments can be tracked and categorized.

    Here's what I have so far. Please let me know if I am on the right track.

    Tables: Comments/Issues(comment_id, type, date, priority_level), Project(project_name, location, date_started, date_ended), User(name, etc.), Submittal

    - A project can have many comments/issues but a comment can only belong to one project.
    - Each comment is assigned to one user or many users.
    - A user can have many comments.

    I'm having trouble with linking the tables together on the many to many relationship. Any help is very much appreciated. Thank you!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you explain more how a single comment can be associated with multiple users?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    5
    I'm sorry. Looking back at what I wrote I made a mistake. Each comment has one user that is in charge of it. So each comment would only have one user.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay [whew], that's reassuring

    what's the submittal table for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2011
    Posts
    5
    I was thinking there needed to be a table to link the project and comment tables.

    Well here's the scenario and where I want to be at the end. A company needs to create a database to track comments on projects. Each comment submitted to the project belongs to a user that is in charge on it.

    I'm a beginner and learning more on DB so let me know if I have missed anything. Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wontonnoodleboi View Post
    I was thinking there needed to be a table to link the project and comment tables.
    only if a single comment can apply to more than one project (which you already said was not the case)

    here's what i think you can use --
    Code:
    CREATE TABLE projects
    ( id INTEGER NOT NULL PRIMARY KEY
    , name VARCHAR(99) NOT NULL
    , type VARCHAR(9) NOT NULL
    , start_date DATE NOT NULL
    , end_date DATE NULL
    , priority SMALLINT NOT NULL
    );
    
    CREATE TABLE users
    ( id INTEGER NOT NULL PRIMARY KEY 
    , name VARCHAR(99) NOT NULL
    , iq SMALLINT NULL
    );
    
    CREATE TABLE comments
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , type VARCHAR(9) NOT NULL
    , post_date DATE NULL
    , priority SMALLINT NOT NULL
    , project_id INTEGER NOT NULL
    , user_id INTEGER NOT NULL
    , CONSTRAINT valid_project
         FOREIGN KEY ( project_id ) 
            REFERENCES projects ( id )
    , CONSTRAINT valid_user
         FOREIGN KEY ( user_id ) 
            REFERENCES users ( id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2011
    Posts
    5
    I've attached a drawing so hopefully the visual is better at describing this than me. I believe I might need the submittal table because the submittal table will be linked to the comments where it can be parsed to each project. So each project has many comments. I believe this would be a many to many relationship?


    Thanks again for the help.
    Attached Thumbnails Attached Thumbnails CaptureDB.PNG  

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wontonnoodleboi View Post
    So each project has many comments. I believe this would be a many to many relationship?
    no, only if a single comment can apply to more than one project -- which i point out once again you already said wasn't the case

    so it's one-to-many, not many-to-many -- each project has multiple comments, but each comment applies to only one project
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2011
    Posts
    5
    If you look at my diagram, would I need a submittal table and a comment table?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your diagram does nothing for me, sorry

    i still don't think you need a "submittal" table -- how is that going to be different from the comment table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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