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.

ERD

• Richard Alonso: Now I'm learning mathematics, I'll see it better, thanks a lot for your answer ! When I check, i'll aprove.
• Richard Alonso: It's very vague, we need to think how a mechanical workshop solves their problems. Are you understanding my point of view? 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. Can someone help me? That was always my number one problem at designing this database, have all this restrictions makes me confused.
• Stijn Wynants: To search you are able to search the answer field (or any field) If you want to have fast searches, you can add different columns like condition or noise or smell. The sky is the limit. You can also add a step column to give an ordering to your answers, so you can look at what steps you've taken as answer 1 & 2 & 3 because there is a 1 question to multiple answer. However you might want to rename the answer table to Actions or something like that
• Richard Alonso: I'll try it later, thanks a lot @StijnWynants ! You are being very helpful because you gave me the solution and then i thought that was extremely basic, so me and my teacher decided to turn it harder and then you appear again and solved my problem. If something is wrong with your point of view for my app (because you think/imagine one thing I think/imagine another) there's no problem, you are being very nice and helping me a lot, I'll try to explain better if it occurs again. So, one more time, thanks.
• Richard Alonso: One last question, what is "IsSolver" in the app? Or what you tought for him, whatever, explain me this FIELD