StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You're really not exploiting the relational nature of databases. The nice way to do it is:</p> <ul> <li>Have a table of tasks (with unique ids, without the extra milestone field, without the preformers)</li> <li>Have a table of milestones with two columns: the task id and the special milestone field -- only milestones will appear in this table</li> <li>Have a table with two columns: task id and performer</li> <li><p>Have a table with two columns: performer and sub-performer</p></li> <li><p>If a performer can have multiple fields, use a prerformer id in the tables above and have a table with performer id and other fields</p></li> </ul> <p><strong>Re: Comment</strong></p> <blockquote> <p>I have read that normalization can reduce DB efficiency, that's why I combine them all.</p> </blockquote> <p>Where? It's a pretty strange claim.</p> <blockquote> <p>For the table that contain taskid and performer (the 3rd on your list) Would it be like, if task 143 needs Staff A, B, C. In DB, (row 1| 143 | A) (row 2| 143 | B) (row 3| 143 | C) Don't you still have redundancy?</p> </blockquote> <p>The repetition in the third table isn't a redundancy problem because you aren't replicating any information: the information in the table is about relationships and there are three relationships in three rows.</p> <p>A redundancy problem appears when you have a setup like yours, were, let's say task 143 has a completion_date "May 31, 2011" then your table would look like:</p> <pre><code>task_id completion_date performer 143 May 31, 2011 A 143 May 31, 2011 B 143 May 31, 2011 C </code></pre> <p>Now let's say I want to change the <code>completion-date</code> for task 143. In your setup I have to change it in all three rows, and what's worse, if someone does something wrong you could get an inconsistent table like:</p> <pre><code>task_id completion_date performer 143 May 31, 2011 A 143 May 12, 2011 B 143 May 31, 2101 C </code></pre> <p>And now you don't know which is the right <code>completion_date</code>! When you normalize, you only have one row in one table to change the date, and your database is never inconsistent like that.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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