Normal Form?

[Ryan Langton]

I know an average amount about data structuring to 4th normal form and
beyond, but have a question for the experts out there. In a database
application our company owns, the tables seem to me to be a bit oddly
structured. Heres just a hypothetical example of the "Animal" table the way
I would envision it.

Table: Animal
(PK) Animal Number
Animal Name
Animal Weight
Animal Sex
(FK) Animal Left Eye Color ID
(FK) Animal Right Eye Color ID

Heres how the tables are constructed

Table: Animal
(PK) Animal Number
Animal Name
Animal Weight
Animal Sex

Table: AnimalEyes
(PK) Animal Eye ID
(FK) Animal Number
(FK) Eye Color ID
Left Right (boolean field for left or right)

Both examples would have of course a third table (Eye Color)
Table: EyeColor
(PK) Eye Color ID
Color Name

The way the tables are currently constructed seems very extraneous to me and
it is causing difficulties in troubleshooting. For one, there are excessive
sub-forms so that all the data can be displayed on a single page. There are
about 10 subforms on the main user interface that just consist of a single
combo box! Is it me or are these tables structured poorly?

Thanks,
Ryan

 

[Steve Schapel]

Ryan,

You will probably get a variety of opinions on this one.

For a start, I would not have a Eye Color ID field. The Color Name is
unique, right? Ok, well that's the Color data, there is no purpose
served in complicating the picture with an ID field.

Second, in the AnimalEyes table, I would not use a Boolean field for
LeftRight. A Yes/No data type is for Yes or No, True or False, On or
Off, but using it for Left or Right doesn't make sense. Anyway, I know
that's not directly related to your main question.

In this type of situation, I think you have to take into account the
purpose and the uses to which the data will be put. If the colour of
the animal's eyes is primarily for identification purposes, and you can
say that the colour of the right eye is a discrete piece of information
about the animal and the colour of the left eye is a discrete piece of
information about the animal, then in my opinion they go in separate
fields, just as you have suggested. To take the view, as the original
developer of your system apparently has, that there is a one-to-many
relationship between the animal and its eye colour, may be applicable,
though, in some scenarios. If you are involved in some sort of
scientific research, for example, and you want your data to answer
questions such as "Of all the animals with two eyes, if one of the eyes
is blue and the other is not, is there a difference between whether the
blue is on the left or the right?", then I might consider using your
existing type of data structure.

 

[Van T. Dinh]

IMHO, this is what I called over-normalization and the structure used would
tend to complicate thing without additing much flexibility.

I would tend to go with the first structure since I know there are at most
two eyes. The original creator seemed to expect alien animals that could
have more than 2 eyes but then seemed to restrict to 2 using the Boolean
Field for left and right eyes.

Hoever, with normalization, there are many correct answers (and of course,
many incorrect answers).... My suggestion is to use the one you are
comfortable.

 

[Ryan Langton]

But what about the fact that the animal eye information is stored nowhere in
the main table (Animal)? Is this not bad data structuring in itself? After
all the animal eye color is fully dependant on the Primary key for the
animal table. For this reason I would expect it to be at least referenced
in the main table (Animal), as in my example:

Ryan

 

[Steve Schapel]

Ryan,

I am sorry if my previous reply was not clear. I would store the animal
eye colour information in the animal table. I would not use an ID
number for the colour information. This to me is a ridiculous idea. I
would just have the colour there, i.e....
Table: Animal
(PK) Animal Number
Animal Name
Animal Weight
Animal Sex
Animal Left Eye Color
Animal Right Eye Color

However, this is based on an assumption that Left Eye Color and Right
Eye Color are regarded as two separate and unrelated pieces of
information about the animal. I was also acknowledging that there may
be very specialised business purposes where it may be justified to
regard "Eye Color" as a data element which is in a many-to-one
relationship with Animals, with Left and Right as 'types' of the Eye
Color element, and in this case, the Eye Color would correctly go in a
separate table,

 

[Ryan Langton]

Steve,

I agree with you. I see no reason to use an ID when the color is unique
enough in itself. I just want to make sure I am right in that the Animal
Eye Color (or Eye ID) should at least be contained in the main table
(Animal). I am confused as to why it was not included in this table in the
first place and wonder if there would be any reason to structure tables this
way.

Ryan

 

[Steve Schapel]

Ryan,

It was not included in the Animal table in the first place because an
animal has 2 eyes. This opens up two possible ways of thinking about
the data:

1. The colour of the left eye, and the colour of the right eye, are 2
separate and independent pieces of information, unrelated to each other.
In this case it is appropriate to enter the data into 2 separate
fields in the Animal table.

2. There are more than one piece of eye colour information about the
animal, one piece for each of its eyes. In this case, it is appropriate
to enter the data into separate records in an EyeColor table, which is
related many-to-one to the Animal table.

To repeat what I said before, in my opinion the first approach is
applicable in almost every conceivable scenario. In this we apparently
agree. The second approach was the one taken by the original designer
of your database.