Design - Q&A Database - Help

Question

I'm developing an application for my last year/degree at school.

• This application is for a mechanical workshop;
• It will have two types of users:
  • Administrator,
  • User;
• Administrator will be just to add new users (with this I mean, just the owner of the workshop can add users to this application, via graphic interface);
• User will be able to add Questions and Answers (that will be the same thing, because he will have a question, and then will put its resolution, it's like my own tutorial) about what he fix at the mechanical workshop;
• User will sometimes need to search for one that he has already input as question/answer/solvedproblem/whatever. -Questions or Answers procediments are step-by-step, so I guess that we'll need to have a table for components, I'm not sure. -The application will ask you for noises, smells, whatever that the man that works at the place detect on a car or motorcycle being repaired. We cannot forget this is a step-by-step Q&A and that I will need to put what I'm trying to solve, how I solved and then one month or two later I'll find/searching what I've already did.

I'm very newbie with databases, how can I design this? I'm very good at using database info, but to make one I would really need someone's help. Let me know if you need more details.

Answer 1

you will need something like this.

CREATE DATABASE QA
GO
USE QA
GO
CREATE TABLE Users
(
UserID int identity(-2147483648,1),
Name varchar(150),
fname varchar(150),
isadmin bit,
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserID),
)

GO
CREATE TABLE Question
(
QuestionID int identity(-2147483648,1),
UserID int,
Question varchar(max),
CONSTRAINT PK_Question PRIMARY KEY CLUSTERED (QuestionID),
CONSTRAINT FK_UserQuestion FOREIGN KEY (UserID)     
    REFERENCES dbo.Users (UserID) 
)
GO
CREATE TABLE Answer
(
AnswerID int identity(-2147483648,1),
QuestionID int,
UserID int,
Answer varchar(max),
IsSolver bit,
CONSTRAINT PK_Answer PRIMARY KEY CLUSTERED (AnswerID),
CONSTRAINT FK_UserAnswer FOREIGN KEY (UserID)     
    REFERENCES dbo.Users (UserID) ,
CONSTRAINT FK_AnswerQuestion FOREIGN KEY (UserID)     
    REFERENCES dbo.Question (QuestionID) 
)
GO

This will generate this diagram.

You will need to add extra fields for other items you need, but the diagram should be what you need to accomplish your task.