Database Design simple FAQ

[robtyketto]

Greetings,

I'm designing a simple FAQ system.

The primary aims are as follows :-

* Allow a choice of category to display Question and Answers
* Allow a sequence to be provided to display Q&A's
* Allow user to rate the advice
* Record the number of times the Q&A viewed
* Record the initial date of when the Q&A was entered into the system by an Admin user
* Upon edit of Q&A record the updated time stamp

Here's my current DB schema (looking very sparse)

Any advice and guidance would be greatly appreciated (I did think about a category table but from the category name maybe makes a description redundant).

 

[ajetrumpet]

You've got quite a bit of tasking to do here. Maybe the best thing to do would be to start in on this project and then post specific questions when you come across roadblocks...

Personally, I don't see anything wrong with what you want to do. The only thing I see if the relationship. I would think that would only be legit if the FAQ's are going to written (or whatever you mean by this) by each specific user. Is that what you mean to accomplish with that link?

Anyway, in general I would say: Why not give it a go?

 

[robtyketto]

Thanks for the response.

The link is to record which USER has added the Question and Answer.

One FLAW Ive spotted already is it wont record who UPDATES the Q&A (only datestamp it) only the creator will be recorded.

The reason I have implemented it fully, is I didnt mention Im using Access with JSP (which im learning!!).

Its for a university module and am concearned about the lack of relationships.

 

[gemma-the-husky]

it depends what its for, but i dont think the userid belongs in the FAQ table. although this means is one user is linked to many FAQ's. If that relatiionship indicates the "owner" of the FAQ its fine.

i think what you really want perhaps is tables as follows

FAQ table
User table
FAQViewed table (FaqRef, UserRef) then you can see how many users have examined a given FAQ. This stil lwont identify the same user viewing the FAQ on multiple occasions without some more attention

depends what you want to do really

 

[robtyketto]

Thanks for the replys.

I do only want to record who OWNS the Q&A (who created it).
Though I also would like to know who UPDATED it, which currently I cant do.

As far as recording the views, Im not interested in individuals I will write code to update the view count only if the user has privillege 'U' for user and record the figure collectively for all users.

Worried about the simplicity of it all, thinking I missing some links.

 

[gemma-the-husky]

will you allow a user other than the owner to edit the FAQ?

or will they just be able to add an extra note to it?


if you just want to know who last edited it, then add an extra field to the FAQ for edited by, edited on, as well as the existing userid which deals with the owner (although this will only record one editor, rather than a full history)

a site like this presumably stores lots of mini-notes, each with their owner details, linked to a common main/starter thread. there's more to it than that, though, because within a thread, notes can be attached to subthreads, rather than all being attached to the main thread.

 

[ajetrumpet]

Though I also would like to know who UPDATED it, which currently I cant do.

All that sounds like is a timestamp issue. Gemma has already explained half of that. Follow those words, and use the Date() and Time() functions with the OnDirty property of a form. That's usually how it's done...