I am trying to implement online examination system.  There are two types of question, either fill in the blanks, or multiple choice questions (MCQs).  Right now, I modeled tables as:  questions (     qid INT AUTO_INCREMENT PRIMARY KEY,      question TEXT,     answer VARCHAR(20),     mcq TINYINT(1) );  options (     qid INT,     options VARCHAR(20) );   Options will be stored as:  {     (1,option1),     (1,option2),     (1,option3),     (1,option4) }   MCQ is a 1-bit flag to flag whether it is MCQ or not, if yes, then it fetches options from options table.   For different courses in any institute, should I maintain  different table for different courses, or add course field in question table itself? Is it okay to store answer in questions table, or should I maintain a separate answers table ?  answers (     qid INT,     course VARCHAR(10),     answer );
122213
I am putting together a quiz application.   (I'm calling each quiz a "challenge")  I have a "challenge" table and a "challenge_question" table which is pretty straightforward.  challenge table: id name num_questions (I want to randomize and only display a certain number of Qs per each challenge)  challenge_question table: id challenge_id question (text of the question) required (some questions will be required to be displayed per each challenge) question_type (here I indicate what type of question: Mult. choice, T/F, Ordering, or Fill-in-the-blank)   Where I'm having trouble in normalization is with the types of question options:  Multiple choice, true/false, ordering, and fill-in-the-blank. I wonder, should I have a separate table for each type of challenge-question-option? Or is it possible to combine these into one somehow..  If I combined all possible options into a single table, it might look be something like this  challenge_options table: id challenge_question_id option (text field, can be used for both multiple choice and for fill-in-the-blank style questions) is_true (boolean field for true/false questions) is_correct (boolean, used to determine multiple choice answer) ordering_option_1  ordering_option_2 ordering_option_3 ordering_option_4 (these ordering fields would store the correct sequence of an ordering style of question)   This really strikes me as less than ideal.  Actually, I don't see how the ordering_options would work out correctly at all.  Wouldn't I need to have each of those be a separate option?  Surely there must be a better way.  Any suggestions/criticisms or alternate approaches?
9465
I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate pivot table for fixed subjectID like    SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks from [Exam].[ObtainedMarkEntry]) as InvoiceResult PIVOT (     sum(ObtainedMarks) for [SubjectID] in  ([1],[2],[3],[4]) )as finalResult   This works but problem is 1,2,3 is not static it should come from same table   So I did something like   declare @minvalue int, @maxvalue int declare @myArray varchar(max) set @MyArray=''  select RowID=IDENTITY(int, 1,1), SubjectID into #tempSubject from [Exam].[ObtainedMarkEntry] select * from #tempSubject set @minvalue = (select min(RowID) from #tempSubject) set @maxvalue = (select max(RowID) from #tempSubject) while (@minvalue&lt;=@maxvalue) begin     declare @sid nvarchar(10)     set @sid=(select SubjectID from #tempSubject where RowID=@minvalue)     set @myArray= @myArray + '[' + @sid + '], '      set @minvalue= @minvalue+1 end set @myArray= SUBSTRING(@myArray,0,len(@myArray)) drop table #tempSubject  declare @q as nvarchar(max) SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks  from [Exam].[ObtainedMarkEntry]) as InvoiceResult PIVOT (     sum(ObtainedMarks) for [SubjectID] in  (@myArray) )as finalResult   Now I am getting error at in(@myArray) can't I assign variable in this section.
59131
I'm creating a user-based website. For each user, I'll need a few MySQL tables to store different types of information (that is, userInfo, quotesSubmitted, and ratesSubmitted).  Is it a better idea to:  a) Create one database for the site (that is, "mySite") and then hundreds or thousands of tables inside this (that is, "userInfo_bob", "quotessubmitted_bob", "userInfo_shelly", and"quotesSubmitted_shelly")  or  b) Create hundreds or thousands of databases (that is, "Bob", "Shelly", etc.) and only a couple tables per database (that is, Inside of "Bob": userInfo, quotesSubmitted, ratesSubmitted, etc.)  Should I use one database, and many tables in that database, or many databases and few tables per database?    Edit:  The problem is that I need to keep track of who has rated what.  That means if a user has rated 300 quotes, I need to be able to know exactly which quotes the user has rated.  Maybe I should do this?  One table for quotes. One table to list users. One table to document ALL ratings that have been made (that is, Three columns: User, Quote, rating).  That seems reasonable. Is there any problem with that?
52077
This is the sample document -&gt;  I want to extract questions along with the options. There are other question papers as which have questions with diagrams in them. I want to be able to extract them as well. End goal is to store them in a question bank. I am planning to run an image segmentation algorithm to extract the questions along with their options. Will that be feasible? Suggestions are more than welcome.
37749
I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .  For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.  My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.   However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see  for a clearer description).  But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see ).  AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?  EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.  Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.
10493
Do you know a good source to learn how to design SQL solutions?  Beyond the basic language syntax, I'm looking for something to help me understand:   What tables to build and how to link them How to design for different scales (small client APP to a huge distributed website) How to write effective / efficient  / elegant SQL queries
111326
For a client we'll need 29,000 terms (all the highschool's in the US) and each term needs 6 meta items (address, phone...). My initial thought was to store the term meta information as suggested  but after looking through it again I will be using lots of terms and I wonder if it will overload the options table.  Is there a good time to create a new table? Would this qualify? Other thoughts on how to relate the HS that a person attends to the 'post' that will be their academic profile?  It was  that I make the 'terms' a Custom Post Type. I'm not opposed to that either but I fail to see how I'll relate the CPT to multiple 'authors' since a school will have more than one student that attends it. Because of the multiple ownership of the content terms just seemed to fit better.
35502
The draft feature was discussed a lot  and in other posts, but I didn't see much talk about the OP's mention of being able to have multiple.     Optionally, allow multiple draft questions and a page to edit/delete them.    I think the draft feature where it autosaves and stores it server-side is great, but in most things where they have a draft feature (like email clients), they allow you to have multiple drafts (also stored server-side).  The expiration can even work the same as it does for the single draft system right now.  You wouldn't even need full page to store them, I think the user profile page could have a tab for it like it currently has for answers, questions, and everything else.    It could look very similar to how questions look, with it's title and tags listed there.
105471
I know this has been asked  and , but I have the same idea with a different possible implementation and I need some help.  Initially I had my blogstories table with this structure:  | Column    | Type        | Description                                    | |-----------|-------------|------------------------------------------------| | uid       | varchar(15) | 15 characters unique generated id              | | title     | varchar(60) | story title                                    | | content   | longtext    | story content                                  | | author    | varchar(10) | id of the user that originally wrote the story | | timestamp | int         | integer generated with microtime()             |   After I decided I wanted to implement some versioning system for every story on the blog, the first thing that came to my mind was creating a different table to hold edits; after that, I thought I could modify the existing table to hold versions instead of edits. This is the structure that came to my mind:  | Column        | Type          | Description                                       | |------------   |-------------  |------------------------------------------------   | | story_id      | varchar(15)   | 15 characters unique generated id                 | | version_id    | varchar(5)    | 5 characters unique generated id                  | | editor_id     | varchar(10)   | id of the user that commited                      | | author_id     | varchar(10)   | id of the user that originally wrote the story    | | timestamp     | int           | integer generated with microtime()                | | title         | varchar(60)   | current story title                               | | content       | longtext      | current story text                                | | coverimg      | varchar(20)   | cover image name                                  |   The reasons why I came here:   The uid field of the initial table was UNIQUE in the table. Now, the story_id is not unique anymore. How should I deal with that? (I thought I could address story_id = x and then find the latest version, but that seems very resource consuming, so please give your advice) author_id field value is repeating in each every row of the table. Where and how should I keep it?     Edit  The unique codes generation process is in the CreateUniqueCode function:  trait UIDFactory {   public function CryptoRand(int $min, int $max): int {     $range = $max - $min;     if ($range &lt; 1) return $min;     $log = ceil(log($range, 2));     $bytes = (int) ($log / 8) + 1;     $bits = (int) $log + 1;     $filter = (int) (1 &lt;&lt; $bits) - 1;     do {         $rnd = hexdec(bin2hex(openssl_random_pseudo_bytes($bytes)));         $rnd = $rnd &amp; $filter;     } while ($rnd &gt;= $range);     return $min + $rnd;   }   public function CreateUID(int $length): string {     $token = "";     $codeAlphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";     $codeAlphabet.= "abcdefghijklmnopqrstuvwxyz";     $codeAlphabet.= "0123456789";     $max = strlen($codeAlphabet) - 1;     for ($i=0; $i &lt; $length; $i++) {         $token .= $codeAlphabet[$this-&gt;CryptoRand(0, $max)];     }     return $token;   } }   The code is written in , and was originally written in PHP by  in his .  The fields author_id and editor_id can be different, because there are users with enough permissions to edit anyone's stories.
61586
I'm noob in database. So don't mind if its a silly question.This is used in my application and is the first one. I need a database design for it.    value 4 is dependent on the other value 1,2&amp;3. even if one of the value changes the combination changes and the fourth value is affected. Each of the value 1,value 2,value 3 are independent on each other. Many-to-many relation between value 1,value 2 and value 2,value 3. value 4 is not derived or computed but a distinct value for all the combinations. So how can I design a database for it?
15142
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.  I have a self-referencing table with a primary key, id and a foreign key parent_id.  +------------+--------------+------+-----+---------+----------------+ | Field      | Type         | Null | Key | Default | Extra          | +------------+--------------+------+-----+---------+----------------+ | id         | int(11)      | NO   | PRI | NULL    | auto_increment |  | parent_id  | int(11)      | YES  |     | NULL    |                |  | name       | varchar(255) | YES  |     | NULL    |                |  | notes      | text         | YES  |     | NULL    |                |  +------------+--------------+------+-----+---------+----------------+   Given a name, how can I query the top-level parent?  Given a name, how can I query all of the id's associated with a record of name = 'foo'?  context: I am not a dba, but am planning to ask a dba to implement this type of hierarchical structure and would like to test some queries. The motivation for doing so is described by .    Here is an example of the relationships among ids in the table:    -- ----------------------------------------------------- -- Create a new database called 'testdb' -- ----------------------------------------------------- SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';  CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `testdb` ;  -- ----------------------------------------------------- -- Table `testdb`.`observations` -- ----------------------------------------------------- CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (   `id` INT NOT NULL ,   `parent_id` INT NULL ,   `name` VARCHAR(45) NULL ,   PRIMARY KEY (`id`) ) ENGINE = InnoDB;  SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;  -- ----------------------------------------------------- -- Add Example Data Set -- -----------------------------------------------------   INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10),     (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);
79420
If I have a table  CREATE TABLE users (   id int(10) unsigned NOT NULL auto_increment,   name varchar(255) NOT NULL,   profession varchar(255) NOT NULL,   employer varchar(255) NOT NULL,   PRIMARY KEY  (id) )   and I want to get all unique values of profession field, what would be faster (or recommended):  SELECT DISTINCT u.profession FROM users u   or  SELECT u.profession FROM users u GROUP BY u.profession   ?
31707
We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1).  The priority does not matter for the inserts or updates i.e. not part of the primary key, which I'll enforce separately.  We basically don't want to have to do an order by over a range scan 170 times a second, as the number executed will drop massively.  Does an index organised table guarantee that priority = 1 will always come before priority = 9 when running the following query:  select *   from my_table  where rownum &lt; 2   For slightly more context a typical query would be:  select *   from my_table  where mod(to_number(to_char(tstamp,'ss')),1) = 0    and done is null    and country = 'gbr'    and rownum &lt; 2   The pk constraint, for the IOT, would become priority, rest of the pk with a separate constraint on the pk solely for structure. done is null in approximately 99% of the table so this isn't very selective anyway.  Main index used is, I think, country, done, to_number(to_char(tstamp,'ss'), we tested about 20 combinations and this came up top by a long way.  I am completely unwilling to add any time at all to these queries, 0.01s a day added by select is 41 minutes a day. We'd much rather settle for "good enough" than perfection.
119219
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?  TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?  First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.  I have tried to put one extremely simple example together:    Schema Setup:  CREATE TABLE MASTER  ( ID INT NOT NULL , NAME VARCHAR2(42 CHAR) NOT NULL , CONSTRAINT PK_MASTER PRIMARY KEY (ID) );  CREATE TABLE DATA ( ID INT NOT NULL , MASTER_ID INT NOT NULL , VALUE NUMBER , CONSTRAINT PK_DATA PRIMARY KEY (ID) , CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID) );  INSERT INTO MASTER values (1, 'One'); INSERT INTO MASTER values (2, 'Two'); INSERT INTO MASTER values (3, 'Three');  CREATE SEQUENCE SEQ_DATA_ID;  INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);   Query A:  select NAME from MASTER where ID = 1   :  | NAME | -------- |  One |   Query B:  select ID, VALUE from DATA where MASTER_ID = 1   :  | ID | VALUE | -------------- |  1 |   1.3 | |  2 |   1.5 | |  3 |   1.7 |   Query C:  select M.NAME, D.ID, D.VALUE  from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID where M.ID = 1   :  | NAME | ID | VALUE | --------------------- |  One |  1 |   1.3 | |  One |  2 |   1.5 | |  One |  3 |   1.7 |   Of course, I didn't measure any performance with these, but one may observe:   Query A+B returns the same amount of usable information as Query C. A+B has to return 1+2x3==7 "Data Cells" to the client C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.   Generalizing from this (as far fetched as it is):   A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?
34791
This is the announcement post of the Stack Exchange Data Explorer. Its answers were meant to iron out the initial glitches 6 to 8 years ago.  If you have found a bug or need support  today with any of SEDEs features, please ask a new question and tag it with   What does it do? Allows you to run arbitrary SQL queries on the data dumps, and share those queries with your friends. Technical details It is hosted on the Stack Exchange infrastructure , the database is hosted on the same SQL Server version all other sites are hosted on. You can use most of the TSQL commands, even create temporary objects (but not User Defined Functions). The controls used are jQuery based, the grid is  . All query results are cached, so sharing a link to a query will not result in the server going in to a tailspin. The query edit control is a slightly modified  control, I also am considering bespin cause the inter iframe communication is nasty complicated. It gives up nice syntax highlighting for queries. Parameters There is support for parameterized queries, to get it to work name your params like ##this##. For example have a . Query name and description A query can have a name and description, to name it lead with a comment, to describe it continue commenting, for example: -- This is my query name  -- This is my description -- I can span multiple lines  SELECT 1  Anonymous users can initially name a query, but can not change the names of any existing queries. Tutorial There's a  which helps you getting started in writing your own SQL queries. Logging in You can use your openid provider to log in, at the moment once logged in we will track the list of queries you execute (giving you basic history support) Also if there are any params named UserId they will be populated automatically if your EmailHash matches the one on the site being queried. Magic columns If you alias an id column with as [Post Link] it will automatically create a link in the result set to the parent site. Similarly if you alias an id column with as [User Link] it will display a link to the user page. select top 10 * from Posts will show you how tags are done. (Magic columns for images is planned) example (most controversial posts:  ) So what are you waiting for, check it out at  Any feedback would be greatly appreciated. I hope to have the login section working fairly soon and allow voting on the best queries and commenting. Featured Queries At the moment, admins have permission to feature interesting queries on this page , I am looking for better ways to manage the huge query list that is building up.
48206
I'm using the exam class, and I want to add answers. Right now the solutions are printet after the question, but before the next question. Ideally they would be presented at the end of a serie of questions. I'd like either   the numeric or very simple one-line answers to questions (something like a new \begin{numans} ... \end{numans} environment), or  the entire solution as it is now with the solution environment,    at the end of the document. Any ideas?   An MWE:  \documentclass[a4paper]{exam} \usepackage[utf8]{inputenc} \printanswers  \begin{document}  \begin{questions} \addpoints \question This is the first question \begin{solution}     This is the solution to question one.  \end{solution}  \addpoints \question This is the second question \begin{solution}     This is the solution to questino two.  \end{solution} \end{questions}  \end{document}   Edit: the solution in Erik's suggestion is possible, but I would like to be able to stick to my already exam classed documents.
38962
Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:   Should table names be plural? Should column names be singular? Should I prefix tables or columns? Should I use any case in naming items?   Are there any recommended guidelines out there for naming items in a database?