Different approach - FAQ Search engine

[Bluie]

hi..

I have tried to do a search on this but i cant seem to find something similiar. And I did a post of what i wanted to do here:
Click Me

This is my table structure:

** I have keep this structure the simple. In actual of what i want to do, its more section under the 3rd lvl. as in more section under tblNAMe and tblRELATIONSHIP.

-> My structure:

tblQNA
CategoryNAME
CategoryMOOD
CategoryRELATIONSHIP

tblNAME
CategoryMALE
CategoryFEMALE

tblRELATIONSHIP
CategoryNEAR
CategoryFAR

tblMALE (Question related to Name - Male will be here)
MaleQuestion
MaleAnswer

tblFEMALE (Question related to Name - Female will be here)
FemaleQuestion
FemaleAnswer

tblMOOD (Question related to Mood will be here)
MoodQuestion
MoodAnswer

tblNEAR (Question related to Near Relation will be here)
NearQuestion
NearAnswer

tblFAR (Question related to Far Relation will be here)
FarQuestion
FarAnswer

So what am i trying to do? People would put in a question and an answer into a box. After that, the person can choose which category does the inputed question belongs to which category(male? female? .. ).This is actually a FAQ search engine.

So ppl will have an option to search for keywords and match questions. There should also be an option weather it should search from tblQNA or tblName or tblGOOD. So it has different level of searching. This is why it has such tree. So after searching, it will display the possible matched question (display question only). then the user can click which question to view the answer (together with the question).

Hope to get some advise. Is this how the best way to implement? OR is there a better method? pls advise and thanks in advance. ill be trying to do the access now. ill post as i goes along.

cheers

 

[KenHigg]

Are you still working on this?

kh

 

[Bluie]

yes i am... sry i was away for the weekend. now i am back on the work again.

i am wondering, is my structure correct?

 

[Bluie]

this is what i have done. its easy.. but now i have headache on how do i actually have to build the relationship here.. ill look into some examples of what ppl have done and get into here again..

 

[Bluie]

any idea if my table is correct? ill check back in 12 hours more. thanks

 

[ecniv]

I don't think I'd use that structure you've posted... But then there are numerous ways to attempt this (more you do the better as you will then see the benefits of doing things in different ways).

Look ups

[b]tblLUSexes[/b]
SexesID - Autonumber - pk
Sex - Text - 10

[b]tblLUMoods[/b]
MoodsID - autonumber - pk
Mood - Text - 20
IsActive - Y/N - default Y

[b]tblLURels[/b]
RelsID - autonumber - pk
Relationship - Text -10
IsActive - Y/N - default Y

Table(s)

[b]tblQuestions[/b]
QuestionID - autonumber -pk
Question - memo
Name - text - 30 (optional to fill?)
SexesID - number - long
MoodsID - number - long
RelsID - number - long
PostedAt - date/time (so you can see when posted)

AllowZero length for all text/memo fields

The users then post a question with the look ups which are only those that you or an admin allow...


Vince

 

[Bluie]

thanks for the feedback ecniv.

but if someone were to do a search(keyword) on the topic sex-male, how can i implement it with the following table structure that you have posted? im sry i am a beginner and i am trying to understand as much as i can.

 

[Bluie]

btw, can u explain a little on this?

IsActive - Y/N - default Y

what is it for?

 

[ecniv]

Ok, your last question first.

IsActive field is so you don't need to delete the record in the look up table.
Say over time things change, perhaps the RelationShips descriptions increase, and some are no longer used. If you set the isactive to false, then on the choosing search/entering data forms the drop down combos/list boxes will only show those that are true. If you deleted that record, you'd need to update all the other records that point to it, otherwise they'd point to a null value.
It's an option, you don't need it in there, but you may wish to consider it.

Now your first question:

thanks for the feedback ecniv.

but if someone were to do a search(keyword) on the topic sex-male, how can i implement it with the following table structure that you have posted? im sry i am a beginner and i am trying to understand as much as i can.

On your search screen you have drop down combos for all the look up tables, the users select those they want to search by then you create an sql statement to pull back the required data + the filters the user selected. Again there are many ways of achieving this... You'd have to choose which you'd like to use. Or try several.


Vince

 

[Bluie]

I dont know if i should ask this here, but id like to do something with the combo box.

When ComboBox1 is selected, u can choose Name,Mood,Relationship
When Name is choosen, ComboBox2 have selection Male and Female.
(Please refer to 1st post diagram)


It is something like the attachment i have attached from one of the example in the forum. But i understand half half. Does any1 know where i can get hold on an example of this? This is how the question and answer would be categories into.

thanks again.

 

[ecniv]

Depends on your table layouts...

If you have a list of first options (Sex, Mood, relationship, etc) and a list of second options dependant on the first (male, female) all in the same table or in two tables but with a relating ID field then yes you can...

Example table
ThingID - Autonumber - pk
ThingType - text
RefersToID - number

1 - Sex - 0
2 - Mood - 0
3 - Relationship - 0
4 - Male - 1
5 - Female - 1

The first list filters only on those where the RefersToID is 0 (ie the top choices)
The Second list refers to the same table, but is filtered by RefersToID equalling the selected first option.

Example:
If the first combo is Sex (1) the second should list Male and Female (refersto id is 1)


This is only one option/suggestion.


Vince