DB design feedback needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

DB design feedback needed

Post by Sinemacula »

I've got an html form that currently produces a report using a PHP script (see this thread for info on that - viewtopic.php?f=1&t=79035 )

I also have an earlier version that puts all the data into a single database table, one record per submission. The process of reworking my report script (the above referenced thread documents some of that process), has led me to the conclusion that I should rethink the database side of it too.

Here's the old form:

Code: Select all

<form name="cmcscorer" action="cmcscorer.php" method="post"  target="blank">
<div style="text-align: center; font-weight: bold;">Your Name: <input type="text" name="name"></div>
<table cellpadding="10px">
<tr><td class="input">
<p>Topic 1:<br /><input type="text" name="topic1" /><br />
L-Mode Rating: <select name="l1">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="ab1" value="1"/>A - B<input type="radio" name="ab1" value="2"/></p>
<p><input type="radio" name="cd1" value="1"/>C - D<input type="radio" name="cd1" value="2"/></p>
<p><input type="radio" name="ae1" value="1"/>A - E<input type="radio" name="ae1" value="2"/></p>
<p><input type="radio" name="bd1" value="1"/>B - D<input type="radio" name="bd1" value="2"/></p>
<p><input type="radio" name="ec1" value="1"/>E - C<input type="radio" name="ec1" value="2"/></p>
<p><input type="radio" name="da1" value="1"/>D - A<input type="radio" name="da1" value="2"/></p>
<p><input type="radio" name="eb1" value="1"/>E - B<input type="radio" name="eb1" value="2"/></p>
<p><input type="radio" name="ca1" value="1"/>C - A<input type="radio" name="ca1" value="2"/></p>
<p><input type="radio" name="de1" value="1"/>D - E<input type="radio" name="de1" value="2"/></p>
<p><input type="radio" name="bc1" value="1"/>B - C<input type="radio" name="bc1" value="2"/></p>
</td>
<td class="input">
<p>Topic 2:<br /><input type="text" name="topic2" /><br />
L-Mode Rating: <select name="l2">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="ab2" value="1"/>A - B<input type="radio" name="ab2" value="2"/></p>
<p><input type="radio" name="cd2" value="1"/>C - D<input type="radio" name="cd2" value="2"/></p>
<p><input type="radio" name="ae2" value="1"/>A - E<input type="radio" name="ae2" value="2"/></p>
<p><input type="radio" name="bd2" value="1"/>B - D<input type="radio" name="bd2" value="2"/></p>
<p><input type="radio" name="ec2" value="1"/>E - C<input type="radio" name="ec2" value="2"/></p>
<p><input type="radio" name="da2" value="1"/>D - A<input type="radio" name="da2" value="2"/></p>
<p><input type="radio" name="eb2" value="1"/>E - B<input type="radio" name="eb2" value="2"/></p>
<p><input type="radio" name="ca2" value="1"/>C - A<input type="radio" name="ca2" value="2"/></p>
<p><input type="radio" name="de2" value="1"/>D - E<input type="radio" name="de2" value="2"/></p>
<p><input type="radio" name="bc2" value="1"/>B - C<input type="radio" name="bc2" value="2"/></p>
</td>
<td class="input">
<p>Topic 3:<br /><input type="text" name="topic3" /><br />
L-Mode Rating: <select name="l3">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="ab3" value="1"/>A - B<input type="radio" name="ab3" value="2"/></p>
<p><input type="radio" name="cd3" value="1"/>C - D<input type="radio" name="cd3" value="2"/></p>
<p><input type="radio" name="ae3" value="1"/>A - E<input type="radio" name="ae3" value="2"/></p>
<p><input type="radio" name="bd3" value="1"/>B - D<input type="radio" name="bd3" value="2"/></p>
<p><input type="radio" name="ec3" value="1"/>E - C<input type="radio" name="ec3" value="2"/></p>
<p><input type="radio" name="da3" value="1"/>D - A<input type="radio" name="da3" value="2"/></p>
<p><input type="radio" name="eb3" value="1"/>E - B<input type="radio" name="eb3" value="2"/></p>
<p><input type="radio" name="ca3" value="1"/>C - A<input type="radio" name="ca3" value="2"/></p>
<p><input type="radio" name="de3" value="1"/>D - E<input type="radio" name="de3" value="2"/></p>
<p><input type="radio" name="bc3" value="1"/>B - C<input type="radio" name="bc3" value="2"/></p>
</td>
<td class="input">
<p>Topic 4:<br /><input type="text" name="topic4" /><br />
L-Mode Rating: <select name="l4">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="ab4" value="1"/>A - B<input type="radio" name="ab4" value="2"/></p>
<p><input type="radio" name="cd4" value="1"/>C - D<input type="radio" name="cd4" value="2"/></p>
<p><input type="radio" name="ae4" value="1"/>A - E<input type="radio" name="ae4" value="2"/></p>
<p><input type="radio" name="bd4" value="1"/>B - D<input type="radio" name="bd4" value="2"/></p>
<p><input type="radio" name="ec4" value="1"/>E - C<input type="radio" name="ec4" value="2"/></p>
<p><input type="radio" name="da4" value="1"/>D - A<input type="radio" name="da4" value="2"/></p>
<p><input type="radio" name="eb4" value="1"/>E - B<input type="radio" name="eb4" value="2"/></p>
<p><input type="radio" name="ca4" value="1"/>C - A<input type="radio" name="ca4" value="2"/></p>
<p><input type="radio" name="de4" value="1"/>D - E<input type="radio" name="de4" value="2"/></p>
<p><input type="radio" name="bc4" value="1"/>B - C<input type="radio" name="bc4" value="2"/></p>
</td>
<td class="input">
<p>Topic 5:<br /><input type="hidden" name="topic5" value="Prefer"><strong>"PREFER"</strong><br /><br />
<br /></p>
<p><input type="radio" name="ab5" value="1"/>A - B<input type="radio" name="ab5" value="2"/></p>
<p><input type="radio" name="cd5" value="1"/>C - D<input type="radio" name="cd5" value="2"/></p>
<p><input type="radio" name="ae5" value="1"/>A - E<input type="radio" name="ae5" value="2"/></p>
<p><input type="radio" name="bd5" value="1"/>B - D<input type="radio" name="bd5" value="2"/></p>
<p><input type="radio" name="ec5" value="1"/>E - C<input type="radio" name="ec5" value="2"/></p>
<p><input type="radio" name="da5" value="1"/>D - A<input type="radio" name="da5" value="2"/></p>
<p><input type="radio" name="eb5" value="1"/>E - B<input type="radio" name="eb5" value="2"/></p>
<p><input type="radio" name="ca5" value="1"/>C - A<input type="radio" name="ca5" value="2"/></p>
<p><input type="radio" name="de5" value="1"/>D - E<input type="radio" name="de5" value="2"/></p>
<p><input type="radio" name="bc5" value="1"/>B - C<input type="radio" name="bc5" value="2"/></p>
</td></tr>
</table>
<table cellpadding="5px" cellspacing="5px">
<tr>
<td class="images"><img src="./images/PDImages/image_a.png" width="150px"><br />A</td>
<td><textarea cols="50" rows="3" name="desc_a">Please replace this text with your name or description for Image A</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_b.png" width="150px"><br />B</td>
<td><textarea cols="50" rows="3" name="desc_b">Please replace this text with your name or description for Image B</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_c.png" width="150px"><br />C</td>
<td><textarea cols="50" rows="3" name="desc_c">Please replace this text with your name or description for Image C</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_d.png" width="150px"><br />D</td>
<td><textarea cols="50" rows="3" name="desc_d">Please replace this text with your name or description for Image D</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_e.png" width="150px"><br />E</td>
<td><textarea cols="50" rows="3" name="desc_e">Please replace this text with your name or description for Image E</textarea></td>
</tr>
</table>
<center><input type="submit" /></center>
</form></div>
My database table has a field for each and every form field - about 70 (actually, the system it's embedded in also adds in a userid and timestamp and record_id, so it's about 73).

In order to make the report script more efficient, I've been led to the concept of arrays - so my new form looks like this:

Code: Select all

<form name="cmcscorer3" action="cmcscorer3.php" method="post"  target="blank">
<div style="text-align: center; font-weight: bold;">Your Name: <input type="text" name="name"></div>
<table cellpadding="10px">
<tr><td class="input">
<p>Topic 1:<br /><input type="text" name="topic1" /><br />
L-Mode Rating: <select name="lmode[1]">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="answer_list[1][1]" value="A"/>A - B<input type="radio" name="answer_list[1][1]" value="B"/></p>
<p><input type="radio" name="answer_list[1][2]" value="C"/>C - D<input type="radio" name="answer_list[1][2]" value="D"/></p>
<p><input type="radio" name="answer_list[1][3]" value="A"/>A - E<input type="radio" name="answer_list[1][3]" value="E"/></p>
<p><input type="radio" name="answer_list[1][4]" value="B"/>B - D<input type="radio" name="answer_list[1][4]" value="D"/></p>
<p><input type="radio" name="answer_list[1][5]" value="E"/>E - C<input type="radio" name="answer_list[1][5]" value="C"/></p>
<p><input type="radio" name="answer_list[1][6]" value="D"/>D - A<input type="radio" name="answer_list[1][6]" value="A"/></p>
<p><input type="radio" name="answer_list[1][7]" value="E"/>E - B<input type="radio" name="answer_list[1][7]" value="B"/></p>
<p><input type="radio" name="answer_list[1][8]" value="C"/>C - A<input type="radio" name="answer_list[1][8]" value="A"/></p>
<p><input type="radio" name="answer_list[1][9]" value="D"/>D - E<input type="radio" name="answer_list[1][9]" value="E"/></p>
<p><input type="radio" name="answer_list[1][10]" value="B"/>B - C<input type="radio" name="answer_list[1][10]" value="C"/></p>
</td>
<td class="input">
<p>Topic 2:<br /><input type="text" name="topic2" /><br />
L-Mode Rating: <select name="lmode[2]">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="answer_list[2][1]" value="A"/>A - B<input type="radio" name="answer_list[2][1]" value="B"/></p>
<p><input type="radio" name="answer_list[2][2]" value="C"/>C - D<input type="radio" name="answer_list[2][2]" value="D"/></p>
<p><input type="radio" name="answer_list[2][3]" value="A"/>A - E<input type="radio" name="answer_list[2][3]" value="E"/></p>
<p><input type="radio" name="answer_list[2][4]" value="B"/>B - D<input type="radio" name="answer_list[2][4]" value="D"/></p>
<p><input type="radio" name="answer_list[2][5]" value="E"/>E - C<input type="radio" name="answer_list[2][5]" value="C"/></p>
<p><input type="radio" name="answer_list[2][6]" value="D"/>D - A<input type="radio" name="answer_list[2][6]" value="A"/></p>
<p><input type="radio" name="answer_list[2][7]" value="E"/>E - B<input type="radio" name="answer_list[2][7]" value="B"/></p>
<p><input type="radio" name="answer_list[2][8]" value="C"/>C - A<input type="radio" name="answer_list[2][8]" value="A"/></p>
<p><input type="radio" name="answer_list[2][9]" value="D"/>D - E<input type="radio" name="answer_list[2][9]" value="E"/></p>
<p><input type="radio" name="answer_list[2][10]" value="B"/>B - C<input type="radio" name="answer_list[2][10]" value="C"/></p>
</td>
<td class="input">
<p>Topic 3:<br /><input type="text" name="topic3" /><br />
L-Mode Rating: <select name="lmode[3]">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="answer_list[3][1]" value="A"/>A - B<input type="radio" name="answer_list[3][1]" value="B"/></p>
<p><input type="radio" name="answer_list[3][2]" value="C"/>C - D<input type="radio" name="answer_list[3][2]" value="D"/></p>
<p><input type="radio" name="answer_list[3][3]" value="A"/>A - E<input type="radio" name="answer_list[3][3]" value="E"/></p>
<p><input type="radio" name="answer_list[3][4]" value="B"/>B - D<input type="radio" name="answer_list[3][4]" value="D"/></p>
<p><input type="radio" name="answer_list[3][5]" value="E"/>E - C<input type="radio" name="answer_list[3][5]" value="C"/></p>
<p><input type="radio" name="answer_list[3][6]" value="D"/>D - A<input type="radio" name="answer_list[3][6]" value="A"/></p>
<p><input type="radio" name="answer_list[3][7]" value="E"/>E - B<input type="radio" name="answer_list[3][7]" value="B"/></p>
<p><input type="radio" name="answer_list[3][8]" value="C"/>C - A<input type="radio" name="answer_list[3][8]" value="A"/></p>
<p><input type="radio" name="answer_list[3][8]" value="D"/>D - E<input type="radio" name="answer_list[3][9]" value="E"/></p>
<p><input type="radio" name="answer_list[3][10]" value="B"/>B - C<input type="radio" name="answer_list[3][10]" value="C"/></p>
</td>
<td class="input">
<p>Topic 4:<br /><input type="hidden" name="topic4" value="Yourself" /><strong>"YOURSELF"</strong><br />
L-Mode Rating: <select name="lmode[4]">
<option value="0" label="0" />0</option>
<option value="1" label="1" />1</option>
<option value="2" label="2" />2</option>
<option value="3" label="3" />3</option>
<option value="4" label="4" />4</option>
<option value="5" label="5" />5</option>
<option value="na" selected="selected" label="-" />-</option>
<option value="6" label="6" />6</option>
<option value="7" label="7" />7</option>
<option value="8" label="8" />8</option>
<option value="9" label="9" />9</option>
<option value="10" label="10" />10</option>
</select></p>
<p><input type="radio" name="answer_list[4][1]" value="A"/>A - B<input type="radio" name="answer_list[4][1]" value="B"/></p>
<p><input type="radio" name="answer_list[4][2]" value="C"/>C - D<input type="radio" name="answer_list[4][2]" value="D"/></p>
<p><input type="radio" name="answer_list[4][3]" value="A"/>A - E<input type="radio" name="answer_list[4][3]" value="E"/></p>
<p><input type="radio" name="answer_list[4][4]" value="B"/>B - D<input type="radio" name="answer_list[4][4]" value="D"/></p>
<p><input type="radio" name="answer_list[4][5]" value="E"/>E - C<input type="radio" name="answer_list[4][5]" value="C"/></p>
<p><input type="radio" name="answer_list[4][6]" value="D"/>D - A<input type="radio" name="answer_list[4][6]" value="A"/></p>
<p><input type="radio" name="answer_list[4][7]" value="E"/>E - B<input type="radio" name="answer_list[4][7]" value="B"/></p>
<p><input type="radio" name="answer_list[4][8]" value="C"/>C - A<input type="radio" name="answer_list[4][8]" value="A"/></p>
<p><input type="radio" name="answer_list[4][9]" value="D"/>D - E<input type="radio" name="answer_list[4][9]" value="E"/></p>
<p><input type="radio" name="answer_list[4][10]" value="B"/>B - C<input type="radio" name="answer_list[4][10]" value="C"/></p>
</td>
<td class="input">
<p>Topic 5:<br /><input type="hidden" name="topic5" value="Prefer"><strong>"PREFER"</strong><br /><br />
<br /></p>
<p><input type="radio" name="answer_list[5][1]" value="A"/>A - B<input type="radio" name="answer_list[5][1]" value="B"/></p>
<p><input type="radio" name="answer_list[5][2]" value="C"/>C - D<input type="radio" name="answer_list[5][2]" value="D"/></p>
<p><input type="radio" name="answer_list[5][3]" value="A"/>A - E<input type="radio" name="answer_list[5][3]" value="E"/></p>
<p><input type="radio" name="answer_list[5][4]" value="B"/>B - D<input type="radio" name="answer_list[5][4]" value="D"/></p>
<p><input type="radio" name="answer_list[5][5]" value="E"/>E - C<input type="radio" name="answer_list[5][5]" value="C"/></p>
<p><input type="radio" name="answer_list[5][6]" value="D"/>D - A<input type="radio" name="answer_list[5][6]" value="A"/></p>
<p><input type="radio" name="answer_list[5][7]" value="E"/>E - B<input type="radio" name="answer_list[5][7]" value="B"/></p>
<p><input type="radio" name="answer_list[5][8]" value="C"/>C - A<input type="radio" name="answer_list[5][8]" value="A"/></p>
<p><input type="radio" name="answer_list[5][9]" value="D"/>D - E<input type="radio" name="answer_list[5][9]" value="E"/></p>
<p><input type="radio" name="answer_list[5][10]" value="B"/>B - C<input type="radio" name="answer_list[5][10]" value="C"/></p>
</td></tr>
</table>
<table cellpadding="5px" cellspacing="5px">
<tr>
<td class="images"><img src="./images/PDImages/image_a.png" width="150px"><br />A</td>
<td><textarea cols="50" rows="3" name="desc_a">Please replace this text with your name or description for Image A</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_b.png" width="150px"><br />B</td>
<td><textarea cols="50" rows="3" name="desc_b">Please replace this text with your name or description for Image B</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_c.png" width="150px"><br />C</td>
<td><textarea cols="50" rows="3" name="desc_c">Please replace this text with your name or description for Image C</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_d.png" width="150px"><br />D</td>
<td><textarea cols="50" rows="3" name="desc_d">Please replace this text with your name or description for Image D</textarea></td>
</tr><tr>
<td class="images"><img src="./images/PDImages/image_e.png" width="150px"><br />E</td>
<td><textarea cols="50" rows="3" name="desc_e">Please replace this text with your name or description for Image E</textarea></td>
</tr>
</table>
<center><input type="submit" /></center>
</form>
Essentially, I'm collecting 11 responses to each of 5 topics, plus 5 descriptions not related to the topics, plus name/user_id.

So, here's what I'm thinking in terms of database redesign:
tbl_submission
sub_id
user_id
timestamp

tbl_topics
topic_id (1-5 -- there are always 5 topics per submission)
sub_id
topic
l_mode

tbl_answers
answer_id (1-10 -- there are always 10 rows per topic, 5 topics per submission)
topic_id
sub_id
answer

tbl_images
image_id (A, B, C, D, or E - so are always 5 rows per submission)
sub_id
description

Does this make sense? Is there a simpler, or better way to do it?

Would it help to know how the data needs to be manipulated/presented/scored -- would that make a difference?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB design feedback needed

Post by califdon »

And presumably you'd have a tbl_users, right? What you have shown is a fully normalized schema, which would be the most flexible, in the event that someday, down the road, you needed to change the number of topics per submission, or questions per topic. If it should be the case that there is some real constraint that assures you that those numbers will never be subject to being changed, I think I would be inclined to denormalize it, more or less as you now have it. This is always a difficult choice. Denormalizing reduces flexibility and I would only rarely endorse it, but when the alternative is 50 records in 2 tables for each submission, I would give it some consideration. One of my considerations would be the ease of restoring a corrupted database. A denormalized table might be easier to reconstruct or repair. I don't think performance would be an issue, certainly not with typical numbers of records. If you're anticipating tens of millions of submissions, maybe performance might become an issue. But in general, joins should be efficient, given that you index appropriately. That said, I'm sure if you ask ten database developers, you will get eleven different answers.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Re: DB design feedback needed

Post by Sinemacula »

Thanks for your response, califdon!

Yes, there will be a tbl_users also -- I didn't think about including that here because I was thinking about tying into the Joomla! system I'm using, which has the users table already - but yes, a users table will exist one way or another.

No I'll go look up "normalized" and "denormalized" :oops: :lol:

As I think about it, I may need the flexibility to change the number of topics per submission at some point (mainly if I expand what I'm doing to a different audience).

Also, there are a few versions of the assessment I'm using - three use 10 questions per topic, and one has 30 questions per topic. I hadn't thought of using the same database for all versions, but I suppose with this type of structure I could easily do so? I should probably add a field to tbl_submission for the version number.
That said, I'm sure if you ask ten database developers, you will get eleven different answers.
:lol: :lol:
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB design feedback needed

Post by califdon »

Sinemacula wrote:Yes, there will be a tbl_users also -- I didn't think about including that here because I was thinking about tying into the Joomla! system I'm using, which has the users table already - but yes, a users table will exist one way or another.
I figured as much.
Now I'll go look up "normalized" and "denormalized" :oops: :lol:
Oh, do! It's the heart and soul of database design. Here are a few good references:
http://databases.about.com/od/specificp ... zation.htm
http://www.databasejournal.com/sqletc/a ... hp/1428511
http://support.microsoft.com/kb/283878
http://www.devshed.com/c/a/MySQL/An-Int ... alization/
I hadn't thought of using the same database for all versions, but I suppose with this type of structure I could easily do so? I should probably add a field to tbl_submission for the version number.
Whether you use the same database might have other considerations, but you certainly could do that. Yes, you would need to do some minor adjustments.
Sinemacula
Forum Contributor
Posts: 110
Joined: Sat Feb 08, 2003 2:36 am
Location: San Jose, CA

Re: DB design feedback needed

Post by Sinemacula »

Thanks for the references, califdon.

I haven't read everything in complete detail, but I think I get the gist of the normalized/denormalized issue now.

If I do gather correctly, I think the alternative you might suggest would be to combine the topic and answer tables?

Or would you denormalize completely and combine them all into a single submissions table?

I think I'm leaning towards the normalized approach so that I do have the flexibility to change the number of topics, or use the same database for all versions (adding a "version" field to the submissions table, I think) - as ultimately we may want to be able to compare data from different versions. Also, it would be easier to duplicate the system for custom applications that might require the ability to have a different number of topics.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB design feedback needed

Post by califdon »

From what you have said about the potential for future expansion to other versions, I'd be inclined to proceed with the normalized schema you first presented, giving you the greatest flexibility. You would probably find some experienced developers who would agree with me and others who wouldn't. It's my general position that you should always opt for a normalized schema unless there are clear reasons not to, and you understand the limitations that denormalization would bring to your database. The exceptions that I've run across have been for extremely high performance systems, where thousands of users are accessing millions of records per minute, that sort of situation. Even then, there is no unanimity in the community about whether denormalizing is the right thing to do.
Post Reply