Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 6 of 6 total |
Data design question |
Tue, Apr 29 2014 8:51 AM | Permanent Link |
kamran | 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 |
Tue, Apr 29 2014 12:00 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Tue, Apr 29 2014 12:22 PM | Permanent Link |
Raul 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 |
Tue, Apr 29 2014 12:59 PM | Permanent Link |
kamran | 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 |
Tue, Apr 29 2014 4:52 PM | Permanent Link |
Malcolm Taylor | 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. |
Tue, Apr 29 2014 6:56 PM | Permanent Link |
Fernando Dias Team Elevate | 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] |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |