开发者

1NF - trying to understand the wiki article

I know what is 1NF in practice, this are my rules when designing DB. I have not done much on enterprise level, but my designs till now have done well. I check following when taking care of 1NF. Let me take the example of a phonebook where people have multiple email addresses and multiple contact numbers like home, mobile, work1, work2 and can add more.

This is my schema, not normalized: phonebook first_name, middle_name, last_name, email_1, email_2, email_3(may be more will be added in future), mobile_1, mobile_2, work开发者_运维问答_1, work_2, (and more will get added).

  1. If data is getting duplicated in a column say email_address then it has to be moved to a separate table email details and the id has to be used in the master table.
  2. I avoid groups, say when people have multiple email accounts like email_1, email_2 to avoid querying how many email addresses a person is having or performing deletion.
  3. Always put a primary key to uniquely identify a row, this assists in deletion of records.

As per First Normal Form

  1. There's no top-to-bottom ordering to the rows.
  2. There's no left-to-right ordering to the columns.
  3. There are no duplicate rows.
  4. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
  5. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

I am not able to understand the meaning of this points.


1&2 are saying that the order the data is presented does not matter. There aren't rows or columns that are indicating what kind of data should be in other rows or columns.

3 is pretty self-explanatory.

4 means you aren't storing a combination of data in a field, ie an AnimalAndVegetable with values like 'HorsePear', instead you'd have an Animal column and a Vegetable column.

5 is similar to 4, it just means you aren't parsing through field values to get the "real" data, ie storing a field that contains an id surrounded by tokens that are to be discarded before the id is used


  1. There's no top-to-bottom ordering to the rows.
  2. There's no left-to-right ordering to the columns.

This is just a fundamental tenet in the relational world: all sets and relations represented by the table rows and columns are unordered. You should not have any dependency on the particular order of rows or columns within your code.

  1. There are no duplicate rows.

Relations should be unique, otherwise you will soon have data integrity problem. Imagine what would happen if your phonebook contained your friend's data twice! When your friend changes his phone number, one of the associated rows surely gets updated, but the other probably not. Now guess what happens when people try to look up his phone number...

  1. Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).

I.e. you shouldn't contain values like "x,y,z", where a single text field is in fact composed of a list of more primitive values.

  1. All columns are regular [i.e. rows have no hidden components such as row IDs, object IDs, or hidden timestamps].

I believe this says that your rows should be identified only by their key(s), and these should be "normal", full column values, not contained within e.g. markup text.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜