is the table normalized?

[turkey]

i have a table for different models. there are 68 fields. i understand that
having 68 fields is a good indication that i don't have it organized well,
but i'm not sure. all the fields depend only on the model (pk). my concern
is that some of the fields repeat for almost all models and about 1/3 are
mostly blank. is this set up ok or should i consider a different design?

thanks for your help
--

 

[Craig Alexander Morrison]

Some things to look out for are:

1. Fields that you have numbered e.g. Telephone1, Telephone2, Telephone3

You could set up a 1 to Many relationship with a ModelTelephone table and
this would allow you to record 0,1 or more Telephones (or whatever your
fields are)

This could also reduce the number of empty fields.

2. Fields that are mostly empty

You could set up a 1 to 1 relationship to contain this data, it may be that
only certain types of Model have data in these fields, the approach a is
sometimes referred to as subtyping. Some designers have in mind the goal of
eliminating nulls (aka empty/blank fields for the purpose of this message).
Nulls can be a problem in designs and can be troublesome when porting a
database from one database engine to another.

What your is situation depends upon the user requirements for your data i.e.
If the user says each Model must have 2 telephone numbers no more no less
then you could legitmately retain them in your Model table.

Take these suggestions and see if they make sense against your design, data
and user requirements

 

[mnature]

There is an explanation of normalization here:

http://support.microsoft.com/kb/283878/EN-US/

The First and Second Normal Forms are similar.

For First Normal, you don't want to use multiple fields to store similar
data. If you have Vendor1 and Vendor2 and Vendor3, then you need to split
those out of your original table, and have a linking table that allows
multiple vendors for any record in your original table. The advantage of
this is that if you need to add another vendor, you don't have to add another
field.

For Second Normal, you don't want to have sets of values that apply to
multiple records. If you have a name or object that is repeated in many
place (either in different tables or in different records), then that name or
object should be in a separate table, and referred to, as necessary, with a
foreign key. The advantage of this is that if that name or object should
change, you only have to change it in one place, and that reduces the chance
of errors creeping into your data.

 

[turkey]

thank you, this made a lot of sense. a couple questions
1) refering to the telephone example, i have 2 connectors. there is always 1
and 1/2 the time there are 2. would connector 1 and connector 2 still be ok?

2) the 1-1 relationship for the fields that are mostly null. say i have 20
fields that are mostly null, should i have 20 more tables?

 

[Craig Alexander Morrison]

1. It really depends on your user requirements you can go either way. As the
repeating group is finite i.e. there is always one and maybe two but no more
then the flexibility offered by a 1 to Many relationship is not really
required. If you introduced such a table you would need to ensure that at
least one record existed for each model and that no more than two existed.
It would be simpler to implement one of the "connector" fields and mandatory
(Required=Yes) and the other as optional in the main table, this would
ensure that engine required 1 and could not have more than 2.

2. I would not have thought 20 tables would be that useful. If the data is
all unrelated to one another you could leave them where the are or put them
all in a new 1 to 1 related table. Or at least put the fields that concerned
themselves with a particular type of model in such a table, leaving the ones
that could not be grouped in the main table. The 1 to 1 structure is usually
introduced to deal with subtypes of the main table. A car manufacturer may
have a Car table and then have a set of 1 to 1 tables for Saloons, Estates,
MPV, SUV, Cabriolets etc these tables would contain fields that only applied
to that type of Car.