Hi
I have a database called "question"
I also have a database called "subject"

So a question can  have up to 10 linked subject names.
It does not make sense to store all 10 subject names in the question database !
so I thought i might use "bits" to store each subject that might be relevant to the question for efficiency.

My only worry is how easy would it be to query such a setup with bits being used to store information. e.g. filters etc..based on the bits set / not set ?

Am I barking up the wrong tree? or is there a more elegant solution that would be easier to code and query.

Typically I would need to filter the question based on whether the subject / subjects have been selected for inclusion.

Kind regards

Kamran

Kamran,

Just to clarify things, you meant a *table* called "question" and another one called "subject", all in the same database, right?

If that is the case, I would say that you are in the right track. At least it's what books say, that you should have a third table linking the first 2. However, just to be sure, would you mind to post the structure of your tables?

--
Fernando Dias
[Team Elevate]

On 4/29/2014 8:51 AM, kamran wrote:
> Hi
> I have a database called "question"
> I also have a database called "subject"
>
> So a question can  have up to 10 linked subject names.
> It does not make sense to store all 10 subject names in the question database !
> so I thought i might use "bits" to store each subject that might be relevant to the question for efficiency.
>
> My only worry is how easy would it be to query such a setup with bits being used to store information. e.g. filters etc..based on the bits set / not set ?
>
> Am I barking up the wrong tree? or is there a more elegant solution that would be easier to code and query.
>
> Typically I would need to filter the question based on whether the subject / subjects have been selected for inclusion.


You could probably do this using bits but you're right that queries
would become a pain not to mention it would have to be hardcoded (i.e.
category to bit mapping).

I would not say adding a field for each is always a bad idea. Yes you're
taking up more space but these are questions (not answers) so you'll
likely have a limited set of them anyways.  So if you have a reasonably
sized fixed number of fixed subjects then having a field for each
(boolean for example) makes the SQL really easy. Like with bits you're
hardcoding but sql is way simpler.

Better and more generic solution is to go with 2 tables - questions and
subjects like you said though sql gets bit more complex.

I would suggest you concentrate on design and queries you need to
actually use and see how it works out.

Raul

Hi Fernando / Raul

Yes I meant tables:

so question table and subject table is in one dbisam database.

The structure is not final yet but:

Question Table ='Q.No, Q.Question,Q.SubjectNo1,Q.SubjectNo2....Q.SubjectNo50,Q.Answer

Subject Table =S.No,S.Description

Although 10 - 50 related subjects per question is unlikely I need to cater for this in case.

So a typical scenario would be to select a question based on a single subject area / multiple subject areas

Kind regards

Kamran


Fernando wrote:

Just to clarify things, you meant a *table* called "question" and another one called "subject", all in the same database, right?

If that is the case, I would say that you are in the right track. At least it's what books say, that you should have a third table linking the first 2. However, just to be sure, would you mind to post the structure of your tables?





Raul wrote:

On 4/29/2014 8:51 AM, kamran wrote:
> Hi
> I have a database called "question"
> I also have a database called "subject"
>
> So a question can  have up to 10 linked subject names.
> It does not make sense to store all 10 subject names in the question database !
> so I thought i might use "bits" to store each subject that might be relevant to the question for efficiency.
>
> My only worry is how easy would it be to query such a setup with bits being used to store information. e.g. filters etc..based on the bits set / not set ?
>
> Am I barking up the wrong tree? or is there a more elegant solution that would be easier to code and query.
>
> Typically I would need to filter the question based on whether the subject / subjects have been selected for inclusion.


You could probably do this using bits but you're right that queries
would become a pain not to mention it would have to be hardcoded (i.e.
category to bit mapping).

I would not say adding a field for each is always a bad idea. Yes you're
taking up more space but these are questions (not answers) so you'll
likely have a limited set of them anyways.  So if you have a reasonably
sized fixed number of fixed subjects then having a field for each
(boolean for example) makes the SQL really easy. Like with bits you're
hardcoding but sql is way simpler.

Better and more generic solution is to go with 2 tables - questions and
subjects like you said though sql gets bit more complex.

I would suggest you concentrate on design and queries you need to
actually use and see how it works out.

Raul

I think I would go with Fernando, ie
 a table of questions,
 one of subjects, and
 a third table which could have as few as 2 columns with records
containing the IDs of each matching question and subject.

Then it is pretty easy to select all the subjects for a question, or
questions for a subject, etc.

Kamran,

I would go with this structure:

Questions(QNo, Question, Answer)
Subjects(SNo, Description)
QuesSubs(QNo, SNo)

As Malcolm said, it's easy to query data from multiple tables: Just use SQL JOINS and/or Master-Detail links with table and query components. It's also easier to maintain the database.


--
Fernando Dias
[Team Elevate]