SQLTeam.com | Weblogs | Forums

Are repeating rows also repeating groups? (1NF)

Is this a violation of 1NF? Are those repeating groups?

+--------+----------+--------------+-------------+
| ItemId | ItemName | ItemCategory | Department  |
+--------+----------+--------------+-------------+
|      1 | Keyboard | Hardware     | Monaco      |
|      1 | Keyboard | Hardware     | Switzerland |
|      2 | License  | Software     | USA         |
|      2 | License  | Software     | Russia      |
|      3 | Arduino  | Hardware     | Russia      |
+--------+----------+--------------+-------------+

And, how about this?

+--------+----------+--------------+--------------+--------------------+
| ItemId | ItemName | ItemCategory | DepartmentId | DepartmentLocation |
+--------+----------+--------------+--------------+--------------------+
|      1 | Keyboard | Hardware     |            1 | Monaco             |
|      1 | Keyboard | Hardware     |            2 | USA                |
|      2 | License  | Software     |            3 | Switzerland        |
|      2 | License  | Software     |            4 | Russia             |
|      3 | Arduino  | Hardware     |            4 | Russia             |
+--------+----------+--------------+--------------+--------------------+

Yes, too many repeated values. They should be stored in separate tables and referenced with IDs. Imagine if the Location of Department 1 changed to somewhere else or ItemID 1 changed its name, you'd have to update several tables which is not good

No, those are not a violation of 1NF. They do contain violation(s) of 2NF and higher.

A 1NF form violation consists of multiple values in the same column, like so:

Department contains 'Monaco, Switzerland' in the same row.

Thank you Scott. Do you have an idea why Wikipedia separates the concept of "no repeating groups" from "atomic columns (cells have a single value)" when it comes to 1NF? You can see it if you scroll down to the table in the "Normal forms" section.

I'm not allowed to include links, the wikipedia path is /wiki/Database_normalization

Yes.

Some "designs" also initially start out as:

ItemId=1 Supplier1=A Supplier2=B Supplier3=C

Even though the different Suppliers are spread across multiple fields, they are still a violation of the same basic concept, that a "field"/specific column not repeat within a row. The structure above is technically different from Supplier='A,B,C' but it is logically the same thing, and thus also not allowed in 1NF.

So, to upgrade my 1NF statement above:

A 1NF form violation consists of multiple values in the same column OR the same column repeating more than one time in a single row