• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Database design question

 
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all, I'm new here, and I'm a newbie in database design. I have some questions regarding database design:
1.) Say I want to maintain a information of a person in a database. In the table "Person", I want to have a field call friends which represent a list of "Id" of "Person"(reference to the same table). What datatype should I use to store a list of Id's in a single field?
2.) Say I have a table "Questions" that maintain questions in different kinds of test, like physics, chemistry, biology... but some questions may be in more than one catergories (i.e. a question can be both chemistry and physics question). Should I maintain all questions in one table with the fields "Phy", "Chem", "Bio" as flags? Or should I maintain one table that contains all questions, and three more table "PhyQuestions", "ChemQuestions", "BioQuestions", that contain only the "Id's" of question in the "Questions" table?
Thanks
Cal
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
1. Number is the best datatype for Primary and Foreign keys. It is always a little bit faster.
2. It sounds like you have a many to many relationship. If you want you can create a "Connector" table that would handle the linking. Meaning


In your decision, you have to look at the future possibilities and what kind of flexibility is acceptible. If you know you will be adding more kinds of test, then the Flag idea will get unwieldy as you add more and moer of these flag fields. Same with the individual tables for classes, each time you need a new set, you need a new table. Now if you know there will never be more than three classes, then the flag idea is the easiest and quickest.
Does this help?
Mark
 
Calvin Kong
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It helps!! Thank you so much!!
 
reply
    Bookmark Topic Watch Topic
  • New Topic