Help! on this simple Database design

wontonnoodleboi · #1 (**permalink**) 06-06-11, 17:49

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!

r937 · #2 (**permalink**) 06-06-11, 18:41

can you explain more how a single comment can be associated with multiple users?

wontonnoodleboi · #3 (**permalink**) 06-06-11, 18:43

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.

r937 · #4 (**permalink**) 06-06-11, 18:45

okay [whew], that's reassuring

what's the submittal table for?

wontonnoodleboi · #5 (**permalink**) 06-06-11, 19:05

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

r937 · #6 (**permalink**) 06-06-11, 19:17

Quote:

Originally Posted by wontonnoodleboi

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 )
);

wontonnoodleboi · #7 (**permalink**) 06-08-11, 13:24

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.

r937 · #8 (**permalink**) 06-08-11, 13:37

Quote:

Originally Posted by wontonnoodleboi

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

wontonnoodleboi · #9 (**permalink**) 06-08-11, 13:43

If you look at my diagram, would I need a submittal table and a comment table?

r937 · #10 (**permalink**) 06-08-11, 13:57

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech