Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Firstly, always name your tables in the singular. This is because a "table definition" is actually the definition for each row - <em>one</em> row (ie singular). Also, it reads more naturally: <code>quiz.id</code> makes sense - the id of the quiz, but <code>quizzes.id</code> is what? The id of the quizzes? No.</p> <p>To answer your question, you should definitely merge poll and quiz into one table. The tip off is they have the same definition. If you need to distinguish between the two, have a boolean column. Let's call it quiz.</p> <p>You should also name your foreign keys as 'table_id', eg 'quiz_id'.</p> <p>You should probably give a name to your quizzes.</p> <p>In mysql, the <code>timestamp</code> data type <em>automatically</em> updates whenever you change something in the row. It's pretty handy - saves adding <code>now()</code> to every update/insert value set.</p> <p>IMHO your tables should be:</p> <pre><code>create table quiz ( id integer not null auto_increment primary key, created datetime, modified timestamp, name text, is_poll boolean not null default false ); create table question ( id integer not null auto_increment primary key, quiz_id integer not null references quiz, question varchar(255) not null, created datetime, modified timestamp ); create table answer ( id integer not null auto_increment primary key, question_id integer not null references question, answer varchar(255) not null, created datetime, modified timestamp ); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCan anyone simplify this MySQL table relationship for polls?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBohemian
    1. USBohemian
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCan anyone simplify this MySQL table relationship for polls?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI disagree with your singular rule. Tables are collections of objects and therefore must be plural. In your example `quiz.id` does not make sense because the attribute `id` is unique and therefore will refer to only one quiz. But when we talk about the quizzes stored in the quizzes table like, for example, `SELECT quizzes.name`, `WHERE quizzes.modified`, `SELECT * FROM quizzes`, it makes perfect sense to talk in plural since a resultset may contain more than one quiz. When I talk I say, hand me over those quizzes, I don't say hand me over those quiz.
      singulars
      1. PO
      1. USBen
    2. CO@Ben You are contradicting yourself! `quiz.id` *does* refer to only one quiz! That's the whole point! I will concede that `SELECT * FROM quizzes` sounds natural, but in all other contexts, like `WHERE quiz.id = 1` etc, the singular is the *only* choice that sounds sane. Consider that each row is like a persisted version of an object. You don't name object classes in the plural, so don't name the row class (which is the table def) in the plural.
      singulars
      1. PO
      1. USBohemian
    3. CObut it does sound natural! "Hand me over the quizzes, those quizzes that have the name test written on them" we need to talk in plural since we're asking for a resulset, a collection of items, not just one item. Again, `quiz.id = 1` sounds natural because the id is unique an will be referring to only one single quiz. But what about the name or the score `SELECT FROM quizzes WHERE score > 0` seems natural to me since I'll be expecting several quizzes, not just one. `SELECT FROM quizzes WHERE id = 1` sounds like "hand me those quizzes that have the id 1" which is perfectly valid.
      singulars
      1. PO
      1. USBen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload