-1

I'm currently exercising with database normalization, and I find that a lot of sources differ in approach to get to 1NF.

For example, this is my UNF table:

customer
+----+--------+----------------------+
| id | name   | phone                |
+----+--------+----------------------+
| 1  | achmed | 06-101010, 06-111111 |
+----+--------+----------------------+
| 2  | jozef  | 06-232323            |
+----+--------+----------------------+
| 3  | maria  | 06-464646, 06-989898 |
+----+--------+----------------------+

One approach splits the multi-values into different tuples, which creates redundancy temporary:

customer
+----+--------+-----------+
| id | name   | phone     |
+----+--------+-----------+
| 1  | achmed | 06-101010 |
+----+--------+-----------+
| 1  | achmed | 06-111111 |
+----+--------+-----------+
| 2  | jozef  | 06-232323 |
+----+--------+-----------+
| 3  | maria  | 06-464646 |
+----+--------+-----------+
| 3  | maria  | 06-989898 |
+----+--------+-----------+

Another approach splits the multi-values directly into new relations, which could look like this:

customer
+----+--------+
| id | name   |
+----+--------+
| 1  | achmed |
+----+--------+
| 2  | jozef  |
+----+--------+
| 3  | maria  |
+----+--------+
customer_phone
+----+-----------+
| id | phone     |
+----+-----------+
| 1  | 06-101010 |
+----+-----------+
| 1  | 06-111111 |
+----+-----------+
| 2  | 06-232323 |
+----+-----------+
| 3  | 06-464646 |
+----+-----------+
| 3  | 06-989898 |
+----+-----------+

Both will end up similar after higher normal forms, but which approach should be 'the best practice' as 1NF and why?

  • Possible duplicate of [Normalization in database management system](https://stackoverflow.com/questions/40623169/normalization-in-database-management-system) – philipxy Oct 19 '17 at 00:37
  • Hi. What exactly is your question? You said it yourself: there's no standard "1NF" meaning. There's no standard "UNF" meaning either. Whatever you mean by "UNF" doesn't involve relational tables, since relational tables have *one value* per column & row, so *you* have to tell *us* what you or your course/reference intend such a data structure to correspond to relationally. Moreover, notions of "1NF" are confused & fuzzy & ultimately are about good design but different redundancy than normalization to higher NFs. What do they want you to do in *your* course? What do its references say? – philipxy Oct 19 '17 at 00:53

1 Answers1

0

There are three approaches to 1NF that you can take with your data.

Approach 1: (Your 1st option) Split the multi-values into separate tuples.

Approach 2: Add additional columns, ie. add phone1 and phone2

Approach 3: (Your 2nd option) Have parent/child relationship

Approach 3 is better as not only is is 1NF, but also 2NF and 3NF.

When normalizing a database, we usually want to end up in 3NF. We can either take the slow approach and o through 1NF, 2NF and then 3NF or we can just go straight to 3NF. After doing this task for 30 years, I naturally generate 3NF as the first step. 1NF and 2NF only happen when amending existing databases and project time constraints prevent us from implementing 3NF.

Steve Wright
  • 534
  • 1
  • 4
  • 14
  • So, if I understand correctly, the normal forms is more about "don't violate these rules" and there is no wrong approach, as long as the rules aren't violated? But approach3 will save some time on the longer run. –  Oct 18 '17 at 15:48
  • 1
    @ErwinOlie & SteveWright The question is about 1NF, which is irrelevant to higher NFs. Also normalization to a higher NF does *not* go through lower NFs other than one's desired 1NF; that can eliminate desired possible higher NF designs. – philipxy Oct 19 '17 at 00:42