which table design is more correct?

[mm nn]

Hi,

I want to create a table like this:

ID Autonum
Datefld Date
Cat Text
Itm Text
tCount Number

Data would look like this:

Data would look like this:

ID Datefld Cat Itm tCount
1 1/1/03 A itm1 3
2 1/1/03 A itm2 4
....
20 1/1/03 A itm20 2
21 1/1/03 B itm1 4
22 1/1/03 B itm2 5
....
40 1/1/03 B itm20 7
....
121 1/1/03 G itm1 8
....
140 1/1/03 G itm20 14
141 1/2/03 A itm1 7
....
160 1/2/03 A itm20 16
161 1/2/03 B itm1 9
....
281 1/3/03 A itm1 22
....

There are 7 categories A, B, C,... G. Each category
contains 20 Items per day, and a count of each item in
each category for each day. Someone at my place is telling
me that the correct way to create my table to to have 2
tables Master Table, Detail Table.

The Master table would contain the ID field, Date field,
and Cat field. The Detail table would contain the ID
field, Itm field and Count field. The argument is that
each category should be listed only one time per day, and
relate to the Detail table by ID. Data would now look
like this:

Master Table
ID Datefld Cat
1 1/1/03 A
2 1/1/03 B
...
7 1/1/03 G
8 1/2/03 A

Detail Table
ID Itm tCount
1 itm1 ...
1 itm2 ...
....
1 itm20 ...
2 itm1 ...
....

This seems a little redundant to me. You have less rows
in the Master table but the same number of rows in the
Detail table as with my original plan of using just one
table. The second design contains an extra column and an
extra table and joins I would now have to deal with.

So my question is - for my purposes - just to query count of items per
category per date range (real basic queries),
which of the 2 designs described here is more
correct/practical? Any suggestions appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dennys G.]

Seems to me that your friend is correct. Since you said "each Category has
20 Items per day" that tell me you have a one-to-many relationship between
the items and the categories. If you make a form for your Category info and
a linked subform for your items data, then you will only enter the data in
the Date and Category fields once for all 20 items, etc. This is the idea
of a relational database. One of the ways you can check this is to go to
the Tools.Analyze.Table feature in your MS Access database and analyze your
table. It will help you to decide. Let me know who wins.

Denny G.
"mm nn" <bb******@hotmail.com> wrote in message
news:3f***********************@news.frii.net...

Hi,

I want to create a table like this:

ID Autonum
Datefld Date
Cat Text
Itm Text
tCount Number

Data would look like this:

Data would look like this:

ID Datefld Cat Itm tCount
1 1/1/03 A itm1 3
2 1/1/03 A itm2 4
...
20 1/1/03 A itm20 2
21 1/1/03 B itm1 4
22 1/1/03 B itm2 5
...
40 1/1/03 B itm20 7
...
121 1/1/03 G itm1 8
...
140 1/1/03 G itm20 14
141 1/2/03 A itm1 7
...
160 1/2/03 A itm20 16
161 1/2/03 B itm1 9
...
281 1/3/03 A itm1 22
...

There are 7 categories A, B, C,... G. Each category
contains 20 Items per day, and a count of each item in
each category for each day. Someone at my place is telling
me that the correct way to create my table to to have 2
tables Master Table, Detail Table.

The Master table would contain the ID field, Date field,
and Cat field. The Detail table would contain the ID
field, Itm field and Count field. The argument is that
each category should be listed only one time per day, and
relate to the Detail table by ID. Data would now look
like this:

Master Table
ID Datefld Cat
1 1/1/03 A
2 1/1/03 B
..
7 1/1/03 G
8 1/2/03 A

Detail Table
ID Itm tCount
1 itm1 ...
1 itm2 ...
...
1 itm20 ...
2 itm1 ...
...

This seems a little redundant to me. You have less rows
in the Master table but the same number of rows in the
Detail table as with my original plan of using just one
table. The second design contains an extra column and an
extra table and joins I would now have to deal with.

So my question is - for my purposes - just to query count of items per
category per date range (real basic queries),
which of the 2 designs described here is more
correct/practical? Any suggestions appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dennys G.]

A good example of our discussion is the Categories form in the Northwind
Sample Database that ships with your version of MS Access. This form has
Categories as the main form and Products in the subform.

Denny G.
"mm nn" <bb******@hotmail.com> wrote in message
news:3f***********************@news.frii.net...

Hi,

I want to create a table like this:

ID Autonum
Datefld Date
Cat Text
Itm Text
tCount Number

Data would look like this:

Data would look like this:

ID Datefld Cat Itm tCount
1 1/1/03 A itm1 3
2 1/1/03 A itm2 4
...
20 1/1/03 A itm20 2
21 1/1/03 B itm1 4
22 1/1/03 B itm2 5
...
40 1/1/03 B itm20 7
...
121 1/1/03 G itm1 8
...
140 1/1/03 G itm20 14
141 1/2/03 A itm1 7
...
160 1/2/03 A itm20 16
161 1/2/03 B itm1 9
...
281 1/3/03 A itm1 22
...

There are 7 categories A, B, C,... G. Each category
contains 20 Items per day, and a count of each item in
each category for each day. Someone at my place is telling
me that the correct way to create my table to to have 2
tables Master Table, Detail Table.

The Master table would contain the ID field, Date field,
and Cat field. The Detail table would contain the ID
field, Itm field and Count field. The argument is that
each category should be listed only one time per day, and
relate to the Detail table by ID. Data would now look
like this:

Master Table
ID Datefld Cat
1 1/1/03 A
2 1/1/03 B
..
7 1/1/03 G
8 1/2/03 A

Detail Table
ID Itm tCount
1 itm1 ...
1 itm2 ...
...
1 itm20 ...
2 itm1 ...
...

This seems a little redundant to me. You have less rows
in the Master table but the same number of rows in the
Detail table as with my original plan of using just one
table. The second design contains an extra column and an
extra table and joins I would now have to deal with.

So my question is - for my purposes - just to query count of items per
category per date range (real basic queries),
which of the 2 designs described here is more
correct/practical? Any suggestions appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rpng123]

Thanks for your reply. I am just thinking that dealing with one table is
easier than dealing with two tables. I guess either way would be correct, but
I just don't see any benefit from having a master table and a detail table. One
table would seem like less work because I would not have to deal with
relationships, it would be one less column to deal with (link ID to ID). It
looks like it could be one or the other.

[TC]

"Rpng123" <rp*****@aol.com> wrote in message
news:20***************************@mb-m22.aol.com...

Thanks for your reply. I am just thinking that dealing with one table is
easier than dealing with two tables. I guess either way would be correct, but I just don't see any benefit from having a master table and a detail table. One table would seem like less work because I would not have to deal with
relationships, it would be one less column to deal with (link ID to ID). It looks like it could be one or the other.

You need to read
http://support.microsoft.com/support...es/Q100139.ASP

HTH,
TC

[mm nn]

That was a very interesing article. So I am now convinced. My actual
question should have been - which is better? Less tables or less
repeating rows?

I was thinking less tables, but the literature appears to say that less
repeating rows is correct for rdbms. So I will go with that.

How about this? The place where I am at they have the master table and
detail table, but the data in the detail table is stored horizontally.
Like 20 int fields. These fields are what I was calling Itm fields.
Each category has 20 Items. Instead of storing data like this in the
detail table:

ID Itm1 Itm2 Itm3 ... Itm20
1 3 17 13 8
2 4 6 5 19
...

wouldn't it be more correct to store it this way in the detail table?

ID Itm tCount
1 itm1 3
1 itm2 4
...itm20 8
2 itm1 4
2 itm2 6
...
2 itm20 19
...

Then you pick the data you need and pivot it horizontally. Isn't that
the correct way to do it? Just asking.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!