further table design questions

#1

Hello

I had some very helpful comments in my earlier post but am still
struggling to get a better table design.

I have made a small step (?) by using an auto increment id in the
p2_users table and by using mysql_insert_id() so that the id in the
p2_results table is the same.

The situtation for groupname p2 is as follows.

There are 12 sections, each with 10 sub-sections and the user can
answer any number of sections between 1 and 12. Each sub-section
provides 5 pieces of user created data.

The p2_user table has the following structure

id (auto increment)
dt (date/time)
name
usercode
group
sub_section

The p2_results table has this structure

id (copied from p2_users via mysql_insert())
sub_section
answer
a_order
ws
whyws
delay

I can see that I get duplicated data in the two tables but am not sure
where to go next....

You can see the p2_users and p2_results data for 2 sections of data at

http://www.micro-active.com/p2_users.gif

and

http://www.micro-active.com/p2_results.gif

Any comments please?!

Cheers

Geoff

#2

In article <6mp3u65lvr3ovamlkped0j0ek40i18pu5r (AT) 4ax (DOT) com>, geoff (AT) invalid (DOT) invalid wrote:

Quote:

Hello

I had some very helpful comments in my earlier post but am still
struggling to get a better table design.

I have made a small step (?) by using an auto increment id in the
p2_users table and by using mysql_insert_id() so that the id in the
p2_results table is the same.

Why? What purpose does that column serve?

Quote:

The situtation for groupname p2 is as follows.

There are 12 sections, each with 10 sub-sections and the user can
answer any number of sections between 1 and 12. Each sub-section
provides 5 pieces of user created data.

The p2_user table has the following structure

id (auto increment)
dt (date/time)
name
usercode
group
sub_section

The p2_results table has this structure

id (copied from p2_users via mysql_insert())
sub_section
answer
a_order
ws
whyws
delay

I can see that I get duplicated data in the two tables but am not sure
where to go next....

You can see the p2_users and p2_results data for 2 sections of data at

http://www.micro-active.com/p2_users.gif

and

http://www.micro-active.com/p2_results.gif

Any comments please?!

Cheers

Geoff

#3

On Sun, 29 May 2011 12:24:14 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

Quote:

In article <6mp3u65lvr3ovamlkped0j0ek40i18pu5r (AT) 4ax (DOT) com>, geoff (AT) invalid (DOT) invalid wrote:
Hello

I had some very helpful comments in my earlier post but am still
struggling to get a better table design.

I have made a small step (?) by using an auto increment id in the
p2_users table and by using mysql_insert_id() so that the id in the
p2_results table is the same.

Why? What purpose does that column serve?

Doug

How cruel !

It allows the connection between the 2 tables. Not necessary? How
should this be done?

Cheers

Geoff

Quote:

The situtation for groupname p2 is as follows.

There are 12 sections, each with 10 sub-sections and the user can
answer any number of sections between 1 and 12. Each sub-section
provides 5 pieces of user created data.

The p2_user table has the following structure

id (auto increment)
dt (date/time)
name
usercode
group
sub_section

The p2_results table has this structure

id (copied from p2_users via mysql_insert())
sub_section
answer
a_order
ws
whyws
delay

I can see that I get duplicated data in the two tables but am not sure
where to go next....

You can see the p2_users and p2_results data for 2 sections of data at

http://www.micro-active.com/p2_users.gif

and

http://www.micro-active.com/p2_results.gif

Any comments please?!

Cheers

Geoff

#4

In article <ijf4u6pds234mi79nj5d98s2778qv5iaqe (AT) 4ax (DOT) com>, geoff (AT) invalid (DOT) invalid wrote:

Quote:

On Sun, 29 May 2011 12:24:14 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

In article <6mp3u65lvr3ovamlkped0j0ek40i18pu5r (AT) 4ax (DOT) com>, geoff (AT) invalid (DOT) invalid
wrote:
Hello

I had some very helpful comments in my earlier post but am still
struggling to get a better table design.

I have made a small step (?) by using an auto increment id in the
p2_users table and by using mysql_insert_id() so that the id in the
p2_results table is the same.

Why? What purpose does that column serve?

Doug

How cruel !

What do you mean, "cruel" ? That's a perfectly legitimate question.

Quote:

It allows the connection between the 2 tables. Not necessary? How
should this be done?

By putting everything in *one* table like I told you to, a week ago. Why do
you think you need two tables?

Quote:

Cheers

Geoff

The situtation for groupname p2 is as follows.

There are 12 sections, each with 10 sub-sections and the user can
answer any number of sections between 1 and 12. Each sub-section
provides 5 pieces of user created data.

The p2_user table has the following structure

id (auto increment)
dt (date/time)
name
usercode
group
sub_section

The p2_results table has this structure

id (copied from p2_users via mysql_insert())
sub_section
answer
a_order
ws
whyws
delay

I can see that I get duplicated data in the two tables but am not sure
where to go next....

You can see the p2_users and p2_results data for 2 sections of data at

http://www.micro-active.com/p2_users.gif

and

http://www.micro-active.com/p2_results.gif

Any comments please?!

Cheers

Geoff

#5

On Sun, 29 May 2011 14:07:46 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

Quote:

How cruel !

What do you mean, "cruel" ? That's a perfectly legitimate question.

Doug,

'just meant it took me a while to sort out the links between the two
tables.

Previously you wrote

Quote:

One table is sufficient. Columns would be:
username
session_ID
section_number
field_number
response

I thought that better design meant that there should be minimum
duplication. Doesn't the above mean that the username and session_ID
are repeated on each row?

Cheers

Geoff

#6

In article <dom4u6lkarubp4bjtk9kg5752t8v77in98 (AT) 4ax (DOT) com>, geoff (AT) invalid (DOT) invalid wrote:

Quote:

On Sun, 29 May 2011 14:07:46 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

How cruel !

What do you mean, "cruel" ? That's a perfectly legitimate question.

Doug,

'just meant it took me a while to sort out the links between the two
tables.

Why do you insist on using two tables? There is *no reason* why you need more
than one.

Quote:

Previously you wrote

One table is sufficient. Columns would be:
username
session_ID
section_number
field_number
response

I thought that better design meant that there should be minimum
duplication.

You misunderstand. Good design means (among other things) not duplicating
*columns* (as you did in your original design, with some 600 columns that all
stored the same attribute).

Quote:

Doesn't the above mean that the username and session_ID
are repeated on each row?

So what if they are? Username + sessionID + section_number + field_number is a
natural primary key for this application.

What is the data you are storing? User responses. How is a user response
uniquely identified? By the combination of username, sessionID, section
number, and field number. One row per response. ONE TABLE.

#7

On Sun, 29 May 2011 18:45:53 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

Doug,

Quote:

You misunderstand. Good design means (among other things) not duplicating
*columns* (as you did in your original design, with some 600 columns that all
stored the same attribute).

Ah! OK.

Quote:

Doesn't the above mean that the username and session_ID
are repeated on each row?

So what if they are? Username + sessionID + section_number + field_number is a
natural primary key for this application.

What is the data you are storing? User responses. How is a user response
uniquely identified? By the combination of username, sessionID, section
number, and field number. One row per response. ONE TABLE.

I should have used sub_section_number and not section_number as there
are 12 sections each with 10 sub-sections.

Does the number of possible rows matter?

If as above there would be 5 rows for each sub-section. There are 10
sub-sections per section, so 10 x 5 = 50 rows.

The user can respond to up to 12 sections so a possible 12 x 50 = 600
rows per session.

If we have 100 users that gives a possible 100 x 600 = 60,000 rows.

Is the 60,000 rows a problem?

Presumably the sessionID could be INT/auto incremental?

Geoff

#8

In article <d585u6p95cj8fkeo71fj9hbacb1kgccip3 (AT) 4ax (DOT) com>, geoff (AT) invalid (DOT) invalid wrote:

Quote:

On Sun, 29 May 2011 18:45:53 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

Doug,

You misunderstand. Good design means (among other things) not duplicating
*columns* (as you did in your original design, with some 600 columns that all
stored the same attribute).

Ah! OK.

Doesn't the above mean that the username and session_ID
are repeated on each row?

So what if they are? Username + sessionID + section_number + field_number is a

natural primary key for this application.

What is the data you are storing? User responses. How is a user response
uniquely identified? By the combination of username, sessionID, section
number, and field number. One row per response. ONE TABLE.

I should have used sub_section_number and not section_number as there
are 12 sections each with 10 sub-sections.

Does the number of possible rows matter?

Probably not in your application. All databases have limits on the size to
which a table can grow, and any file system has an inherent upper limit on the
size of any single file, as well, but it's very unlikely that you're going to
encounter them.

http://dev.mysql.com/doc/refman/5.0/en/full-table.html

Quote:

If as above there would be 5 rows for each sub-section. There are 10
sub-sections per section, so 10 x 5 = 50 rows.

The user can respond to up to 12 sections so a possible 12 x 50 = 600
rows per session.

If we have 100 users that gives a possible 100 x 600 = 60,000 rows.

Is the 60,000 rows a problem?

Certainly not.

Quote:

Presumably the sessionID could be INT/auto incremental?

Could be, but why? You're already storing username and date/time -- isn't
the timestamp sufficient to uniquely identify a session for any particular
user?

#9

On Sun, 29 May 2011 20:53:08 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

Doug,

Quote:

Does the number of possible rows matter?

Probably not in your application. All databases have limits on the size to
which a table can grow, and any file system has an inherent upper limit on the
size of any single file, as well, but it's very unlikely that you're going to
encounter them.

OK

Quote:

http://dev.mysql.com/doc/refman/5.0/en/full-table.html

'will read this.

Quote:

If as above there would be 5 rows for each sub-section. There are 10
sub-sections per section, so 10 x 5 = 50 rows.

The user can respond to up to 12 sections so a possible 12 x 50 = 600
rows per session.

If we have 100 users that gives a possible 100 x 600 = 60,000 rows.

Is the 60,000 rows a problem?

Certainly not.

OK

Quote:

Presumably the sessionID could be INT/auto incremental?

Could be, but why? You're already storing username and date/time -- isn't
the timestamp sufficient to uniquely identify a session for any particular
user?

True - thanks for your patience!

Cheers

Geoff

#10

On 5/29/2011 2:45 PM, Doug Miller wrote:

Quote:

In article<dom4u6lkarubp4bjtk9kg5752t8v77in98 (AT) 4ax (DOT) com>, geoff (AT) invalid (DOT) invalid wrote:
On Sun, 29 May 2011 14:07:46 GMT, dougatmilmacdotcom (AT) example (DOT) com (Doug
Miller) wrote:

How cruel !

What do you mean, "cruel" ? That's a perfectly legitimate question.

Doug,

'just meant it took me a while to sort out the links between the two
tables.

Why do you insist on using two tables? There is *no reason* why you need more
than one.

Actually, proper normalization would require at least two tables, and
very possibly more.

Possible tables would include users, groups, sections, subsections and
answers, depending on the entire design. But I would have at least two
tables - users and answers.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================