Sql-server – Design – Q&A Database – Help

database-designsql server

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.

Best Answer

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.

ERD

Related Question