Normalization Problem

Could someone have a look at this normalization problem for me? Specifally,
what rule is being broken by the first normalization attempt? I know it's
wrong, but why?
/*
DROP TABLE #farm_spreadsheet
DROP TABLE #farm_animals
DROP TABLE #farm_animals2
DROP TABLE #farms
*/
-- Original Farm Spreadsheet
CREATE TABLE #farm_spreadsheet ( farm_id INT, farm_name VARCHAR(50),
farm_animals VARCHAR( 500 ) )
SET NOCOUNT ON
INSERT INTO #farm_spreadsheet VALUES ( 1, 'Farmer Giles'' Farm', '2 Hens, 1
Goat' )
INSERT INTO #farm_spreadsheet VALUES ( 2, 'Jimmy''s Farm', '1 sick Goat' )
-- My lazy colleague wants to 'normalize' to this, but what rules is he
breaking?
CREATE TABLE #farms ( farm_id INT PRIMARY KEY, farm_name VARCHAR(50) NOT
NULL )
CREATE TABLE #farm_animals ( farm_id INT, farm_animal VARCHAR(50) NOT NULL,
animal_count INT )
INSERT INTO #farms
SELECT farm_id, farm_name
FROM #farm_spreadsheet
INSERT INTO #farm_animals VALUES ( 1, 'Hen', 2 )
INSERT INTO #farm_animals VALUES ( 1, 'Goat', 1 )
INSERT INTO #farm_animals VALUES ( 2, 'Goat', 1 )
SET NOCOUNT OFF
-- Try and reproduce original spreadsheet to prove lossless decomposition
SELECT f.farm_id, f.farm_name, fa.farm_animal, fa.animal_count
FROM #farms f
INNER JOIN #farm_animals fa ON f.farm_id = fa.farm_id
ORDER BY f.farm_id, fa.farm_animal
GO
-- I want to normalise to this, but is it any better?
CREATE TABLE #farm_animals2 ( farm_id INT, farm_animal VARCHAR(50) NOT NULL,
animal_status VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #farm_animals2 VALUES ( 1, 'Hen', 'OK' )
INSERT INTO #farm_animals2 VALUES ( 1, 'Hen', 'OK' )
INSERT INTO #farm_animals2 VALUES ( 1, 'Goat', 'OK' )
INSERT INTO #farm_animals2 VALUES ( 2, 'Goat', 'Sick' )
SET NOCOUNT OFF
-- Try and reproduce original spreadsheet to prove lossless decomposition
SELECT f.farm_id, f.farm_name, fa.farm_animal, COUNT(*) AS animal_count
FROM #farms f
INNER JOIN #farm_animals2 fa ON f.farm_id = fa.farm_id
GROUP BY f.farm_id, f.farm_name, fa.farm_animal
ORDER BY f.farm_id, fa.farm_animalHi Damien,
In simple terms, first normal form is "No repeating group of elements".
So, to my knowledge, the first approach using #farms and #farm_animals
agrees with the first normal form.
and the second approach with #farm_animals2 has repeating rows. And if you
ask me its in the -1 normal form :) No offence.
Hope this helps.|||Your "lazy" colleague is not breaking any normalization rules with the
#farm_animal table.
#farm_animals2, on the other hand, has no way to tell one row from
another. If each individual animal is going to be identified by an
individual row, you need some way to uniquely identify each of them.
Some sort of animal_id column, for example.
The most bothersome issue between the two alternatives is that they
have different information. It seems rather premature to be talking
about tables when you have not even decided on the facts, such as
animal_status, to be stored.
Roy Harvey
Beacon Falls, CT
On Wed, 26 Apr 2006 02:57:01 -0700, Damien
<Damien@.discussions.microsoft.com> wrote:

>Could someone have a look at this normalization problem for me? Specifally
,
>what rule is being broken by the first normalization attempt? I know it's
>wrong, but why?
>
>/*
>DROP TABLE #farm_spreadsheet
>DROP TABLE #farm_animals
>DROP TABLE #farm_animals2
>DROP TABLE #farms
>*/
>-- Original Farm Spreadsheet
>CREATE TABLE #farm_spreadsheet ( farm_id INT, farm_name VARCHAR(50),
>farm_animals VARCHAR( 500 ) )
>SET NOCOUNT ON
>INSERT INTO #farm_spreadsheet VALUES ( 1, 'Farmer Giles'' Farm', '2 Hens, 1
>Goat' )
>INSERT INTO #farm_spreadsheet VALUES ( 2, 'Jimmy''s Farm', '1 sick Goat' )
>
>-- My lazy colleague wants to 'normalize' to this, but what rules is he
>breaking?
>CREATE TABLE #farms ( farm_id INT PRIMARY KEY, farm_name VARCHAR(50) NOT
>NULL )
>CREATE TABLE #farm_animals ( farm_id INT, farm_animal VARCHAR(50) NOT NULL,
>animal_count INT )
>INSERT INTO #farms
>SELECT farm_id, farm_name
>FROM #farm_spreadsheet
>INSERT INTO #farm_animals VALUES ( 1, 'Hen', 2 )
>INSERT INTO #farm_animals VALUES ( 1, 'Goat', 1 )
>INSERT INTO #farm_animals VALUES ( 2, 'Goat', 1 )
>SET NOCOUNT OFF
>-- Try and reproduce original spreadsheet to prove lossless decomposition
>SELECT f.farm_id, f.farm_name, fa.farm_animal, fa.animal_count
>FROM #farms f
> INNER JOIN #farm_animals fa ON f.farm_id = fa.farm_id
>ORDER BY f.farm_id, fa.farm_animal
>GO
>
>-- I want to normalise to this, but is it any better?
>CREATE TABLE #farm_animals2 ( farm_id INT, farm_animal VARCHAR(50) NOT NULL
,
>animal_status VARCHAR(10) )
>SET NOCOUNT ON
>INSERT INTO #farm_animals2 VALUES ( 1, 'Hen', 'OK' )
>INSERT INTO #farm_animals2 VALUES ( 1, 'Hen', 'OK' )
>INSERT INTO #farm_animals2 VALUES ( 1, 'Goat', 'OK' )
>INSERT INTO #farm_animals2 VALUES ( 2, 'Goat', 'Sick' )
>
>SET NOCOUNT OFF
>
>-- Try and reproduce original spreadsheet to prove lossless decomposition
>SELECT f.farm_id, f.farm_name, fa.farm_animal, COUNT(*) AS animal_count
>FROM #farms f
> INNER JOIN #farm_animals2 fa ON f.farm_id = fa.farm_id
>GROUP BY f.farm_id, f.farm_name, fa.farm_animal
>ORDER BY f.farm_id, fa.farm_animal|||How about :
CREATE TABLE dbo.farms (
farm_id int PRIMARY KEY ,
farm_name varchar (50)
)
CREATE TABLE dbo.farm_animals3 (
farm_id int ,
Animal_ID int
PRIMARY KEY (farm_id, Animal_ID) ,
animal_type varchar (50) ,
animal_status varchar (10)
CONSTRAINT FK_farm_animals3_farms FOREIGN KEY
(farm_id) REFERENCES dbo.farms (farm_id)
)
Roy makes a good point that until you have decided exactly which data you
are storing, you can't really begin working on the normalization.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:DBC8BEC8-E9A7-4C0D-BF4A-4596433B6212@.microsoft.com...
> Could someone have a look at this normalization problem for me?
Specifally,
> what rule is being broken by the first normalization attempt? I know it's
> wrong, but why?
>
> /*
> DROP TABLE #farm_spreadsheet
> DROP TABLE #farm_animals
> DROP TABLE #farm_animals2
> DROP TABLE #farms
> */
> -- Original Farm Spreadsheet
> CREATE TABLE #farm_spreadsheet ( farm_id INT, farm_name VARCHAR(50),
> farm_animals VARCHAR( 500 ) )
> SET NOCOUNT ON
> INSERT INTO #farm_spreadsheet VALUES ( 1, 'Farmer Giles'' Farm', '2 Hens,
1
> Goat' )
> INSERT INTO #farm_spreadsheet VALUES ( 2, 'Jimmy''s Farm', '1 sick Goat' )
>
> -- My lazy colleague wants to 'normalize' to this, but what rules is he
> breaking?
> CREATE TABLE #farms ( farm_id INT PRIMARY KEY, farm_name VARCHAR(50) NOT
> NULL )
> CREATE TABLE #farm_animals ( farm_id INT, farm_animal VARCHAR(50) NOT
NULL,
> animal_count INT )
> INSERT INTO #farms
> SELECT farm_id, farm_name
> FROM #farm_spreadsheet
> INSERT INTO #farm_animals VALUES ( 1, 'Hen', 2 )
> INSERT INTO #farm_animals VALUES ( 1, 'Goat', 1 )
> INSERT INTO #farm_animals VALUES ( 2, 'Goat', 1 )
> SET NOCOUNT OFF
> -- Try and reproduce original spreadsheet to prove lossless decomposition
> SELECT f.farm_id, f.farm_name, fa.farm_animal, fa.animal_count
> FROM #farms f
> INNER JOIN #farm_animals fa ON f.farm_id = fa.farm_id
> ORDER BY f.farm_id, fa.farm_animal
> GO
>
> -- I want to normalise to this, but is it any better?
> CREATE TABLE #farm_animals2 ( farm_id INT, farm_animal VARCHAR(50) NOT
NULL,
> animal_status VARCHAR(10) )
> SET NOCOUNT ON
> INSERT INTO #farm_animals2 VALUES ( 1, 'Hen', 'OK' )
> INSERT INTO #farm_animals2 VALUES ( 1, 'Hen', 'OK' )
> INSERT INTO #farm_animals2 VALUES ( 1, 'Goat', 'OK' )
> INSERT INTO #farm_animals2 VALUES ( 2, 'Goat', 'Sick' )
>
> SET NOCOUNT OFF
>
> -- Try and reproduce original spreadsheet to prove lossless decomposition
> SELECT f.farm_id, f.farm_name, fa.farm_animal, COUNT(*) AS animal_count
> FROM #farms f
> INNER JOIN #farm_animals2 fa ON f.farm_id = fa.farm_id
> GROUP BY f.farm_id, f.farm_name, fa.farm_animal
> ORDER BY f.farm_id, fa.farm_animal|||>> The most bothersome issue between the two alternatives is that they have
Very true. Stated simply, with limited or no knowledge of the underlying
business model, attempting to normalize some schema would be mostly
meaningless.
Anith|||
In your example you have created a column that is a multivalued fact about
the key and thus violates 2nd normal form.

nonclustered index fields

Hi folks,
I was wondering if it should be taken as a rule of thumb to allways avoid
the us of the fields that conform the primary key when we are designing any
nonclustered index, as in reality they allready have this key in "their
inside".
For example, say we have a pk composed of the fields: date, id
and then we create an index1 with fields : field1, date
and yet another index2 with just field: field1
Then, whenever I search in this simplistic example for field 1 between a
range of dates I allways get better performance with just the simple index.
The question is, will it happen allways or will it depend on the particular
queries and or situations?
Thanks in advance,
Tristan."Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
>
It depends. Often, for instance, you will have a non-clustered index on the
trailing column of a two-column clustered primary key. in your example a
non-clustered index on id would probably be appropriate to enable lookups by
ID. Even though the ID is replicated in the index leaf data, since it is
not the leading column in the clustered primary key, the clustered primary
key does not provide an efficient access path for lookups or sorting by ID.
David|||Tristan
You are confusing PK with clustered index. They are not the same thing.
Clustered index keys are contained in every NC index, but the clustered
index might not be on the primary key column(s).
However, if we assume you meant clustered index when you said pk, then you
answered your own questions. It completely depends on your queries and your
data distributions. A very general rule of thumb is that if you do a lot of
modifications, you want to keep your indexes to a minimum, so you wouldn't
have indexes with overlapping keys. But if you do mainly SELECTs, more
indexes can be useful, and having different leading columns can be a BIG
help. However, in your specific example, your index1 and index2 will be
identical in every way, so there is little reason to have both of them.
I wrote an article for SQL Server Magazine about a year ago on the reasons
why you might want to explicitly list one of your clustered index columns in
your nc index definitions.
http://www.sqlmag.com/Article/Artic...rver_44807.html
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
> Thanks in advance,
> Tristan.|||Thanks lot Kalen & David for your replies. Indeed I pretendet to say
clustered when I used pk, thanks por pointing that out. Your answers have
being most clarifiying, thanks again,
Tristan.
"Kalen Delaney" wrote:

> Tristan
> You are confusing PK with clustered index. They are not the same thing.
> Clustered index keys are contained in every NC index, but the clustered
> index might not be on the primary key column(s).
> However, if we assume you meant clustered index when you said pk, then you
> answered your own questions. It completely depends on your queries and you
r
> data distributions. A very general rule of thumb is that if you do a lot o
f
> modifications, you want to keep your indexes to a minimum, so you wouldn't
> have indexes with overlapping keys. But if you do mainly SELECTs, more
> indexes can be useful, and having different leading columns can be a BIG
> help. However, in your specific example, your index1 and index2 will be
> identical in every way, so there is little reason to have both of them.
> I wrote an article for SQL Server Magazine about a year ago on the reasons
> why you might want to explicitly list one of your clustered index columns
in
> your nc index definitions.
> http://www.sqlmag.com/Article/Artic...rver_44807.html
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
>
>