Developer Forum

Hello,

I want to create the following table

CREATE TABLE [dbo].[tbl1] (
ID int IDENTITY (1, 1) NOT NULL ,
Datefld datetime,
Cat nvarchar (50),
Itm nvarchar (50),
tCount int
)
GO

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 - as described above,
which of the 2 designs described here is more
correct/practical? Any suggestions appreciated.

Thanks,
Rich

Rich,


What is your purpose?

--
- Anith
( Please reply to newsgroups only )

>> Basic data storage, query data for count of items per category, by Date,
very basic, just a lot of data. <<

Strictly based on these requirements, any of the designs you mentioned in
your initial post, should do the job.

However, in general, database designers look at much more crucial
functionalities like preservation of integrity, preventing data corruption
that can cause by update/delete anomalies. In such cases, you start with a
clean analysis of the business model, identify your entities & their
attributes and begin with identifying functional dependencies among the
attributes. In practice, well designed tables will have a key without
exceptions (which you do not have, btw) which generally results in 1NF. And
depending on business model, you may want to remove the non-key dependencies
(leads to 2NF) and any transitive dependencies (which leads to 3NF, loosely
put, A->B & B->C leads to A->C, where A, B & C are attributes). If you find
these terms alien, please refer to a good database design text book.

Without keys, both your designs are under-normalized. Based on general
assumptions on keys, dependencies etc. your first design is not in 3NF and
can cause update anomalies due to transitive dependencies of items on
categories or count on categories. So, as mentioned before, normalization is
not done based on purposes of 'data storage', but based on your functional
dependencies and to do that accurately, you will have to go through your
entire business model first.

--
- Anith
( Please reply to newsgroups only )

Thank you for your reply. The key field(s) would be the
ID field and the Cat field. Maybe I should not have
ommitted that information. I was checking to see if there
was a difference in the two design types. Based on your
explanation it appears either of the two designs would
work. So the only difference I could see would be that
the first design would just be a little less work, only
have to worry about one table instead of two. That was my
main thing - if there would be a difference, overall, with
only using one table or if I should use the two as
suggested to me from someone else.

Any other data sources I would have to work with would
deal with the Cat field. I just feel that in the long
run, one table would be easier to manage than two tables,
but I wanted to get a second opinion before I move on with
this one project.

Thanks,
Rich

category, by Date,
you mentioned in
crucial
preventing data corruption
you start with a
entities & their
dependencies among the
key without
results in 1NF. And
non-key dependencies
leads to 3NF, loosely
attributes). If you find
text book.
Based on general
is not in 3NF and
of items on
before, normalization is
on your functional
go through your

>> So the only difference I could see would be that the first design would
just be a little less work, only have to worry about one table instead of
two <<

No exactly, you are mistaken, it is not just about work you have to do. It
is about your specific business model, data requirements and integrity you
need to preserve that decide this. To reiterate what I said, keeping the
critical factors of design aside, based on the brief narrative you provided
you may go with any design, but if you have not done your homework right,
depending on your functional dependencies, you may pay a price for data
corruption.

me from someone else. <<

The other person may have done better research to identify the data
requirements and may be more familiar with the business model and functional
dependencies than you are, in which case, his suggestion to normalize the
schema to 3NF is correct and warrented.

than two tables,... <<

"Ease" of management, either with one table or with two tables is not a
relevant criteria and is highly subjective. As mentioned before, logical
accuracy and correctness of data is primary goal of database design, not
what seems to be easy to deal with in one shot.

--
- Anith
( Please reply to newsgroups only )

You have problems in the data elements; fix them before you worry about
the tables. Your names are useless, the datatypes are wrong and you
are swimming in NULL-able columns. You have no key and the business
rules you gave are not enforced. It also looks like you cannot tell the
difference between fields and columns and that Standard SQL uses
ISO-8601 date formats. This thing might have been written in ACCESS,
with all these mistakes.

. <<

So enforce those rules in your table:

CREATE TABLE ItemCategorySummary
(receipt_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
cat_code CHAR (1) NOT NULL
CHECK (cat_code BETWEEN 'A' AND 'G')
item_description CHAR(20) NOT NULL,
qty INTEGER DEFAULT 1 NOT NULL CHECK (qty BETWEEN 1 AND 20),
PRIMARY KEY (receipt_date, cat_code));

I made assumptions about defaults and that you never use long Chinese
poems (i.e. NVARCHAR(50) datatypes) as descriptions or to hold CHAR(1)
codes.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

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

Yes, I am coming from an Access environment. So your post was actually
quite informative. I will have to start getting used to creating tables
using methologies you suggest. I have probably developed some bad
habits in Access.

The data sample I included here is just pseudo data. The cat field
actually varies from 40 to 200 chars, etc. But I like the way you
limited/constrained the data types. This will definitely reduce
discrepancies. I'm sure in the long run, that a little extra effort at
design time will pay off in less headaches.

Thanks for your suggestion(s).

Rich

Rich

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

>> I have probably developed some bad habits in Access. <<

It is almost certain :) But ACCESS exposure can be treated, and the
victim can learn to live a normal life.


--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

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

Actually, I was discussing the same topic with the Access people, and
they were giving me the same response as you SqlSrv Folks, about 3rd
normal form and soforth.

Truthfully, my problem with 3rd normal form for this particular project,
which does not contain data that is too sophisticated, is that I have to
involve multiple tables to reduce redundancy. But the ID's, keys I use
in the subsequent detail table could be the same data I use in the
primary table. This is where I really don't see the difference if I
list cat A 10 times in the Detail table or the ID for cat A 10 times.
The difference that I see for using one table is that I don't have to
deal with joins and relationships. What I am actually looking for in a
response may require divine intervention. Everyone is telling me the
same thing from both groups (Access/Sql Srv) but I'm just not getting
it.

Rich

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