For example:

   PK PostCode

   PK HouseNumber
   PK PostCode FK

Seems reasonable. But can we carry on indefinitely?

    PK HouseNumber FK1
    PK PostCode    FK1
    PK RoomNumber

    PK HouseNumber FK2
    PK PostCode    FK2
    PK RoomNumber  FK2
    PK DoorId

Obviously I'm repeating data, but technically its part of a single identifier. Is there some point where this breaks normal form, or some other database rule, or is it just fine nothing to see here?

  • My original gut feel was "but of course, redundancy is bad", but my suspicion is that such a table does indeed meet 3NF, because that only concerns non-key attributes. I think the "lowest" form it fails is 4NF, because columns within the keys have other kinds of dependency (e.g. not all house numbers are valid for all post codes). I don't have the depth of understanding to give a definitive answer, though.
    – IMSoP
    Jul 8 '21 at 17:47
  • i didnt not even know BCNF, 4NF and 5NF existed
    – Ewan
    Jul 8 '21 at 18:10

It is not about repeating data but about functional dependencies.

A relation scheme R is in 3NF with respect to a set of functional dependencies F if it is in 1NF and no nonprime attribute in R is transitively dependent upon a key of R.
A database scheme D is in 3NF if every relation scheme R in D is in 3NF with respect to F.

  • David Maier in The theory of relational databases

To prevent misunderstandings:

  • The “relation scheme R” means a table in an RDBMS.
  • A prime attribute of R is an attribute contained in some key of R. Nonprime are the others.
  • A is transitively dependent on a key K means that there is an attribute (a column) X that is dependent on the key (i.e the value of K determines X) and A is dependent on X (i.e. the value of X determines A).

The redundancy that 3NF tries to prevent, is that in the same table you have redundant depedencies, i.e. that a same attribute is on one side determined directly by a key and at the same time indirectly via another attribute.

Example: imagine a table with columns userid, name, cc (country code), country. In this example, the userid determines name, cc and coutry. The problem is that looking closer, cc also determines country. So here a nonprime is transitively dependent upon a key and it’s not 3NF.

Now, get rid of the last column, and create a separate table with cc and country, removing all the duplicates. This new database scheme would be 3NF, despite the fact that cc is repeated with same values in two tables.

In your example, no nonprime in any table seems transitively dependent upon a key in the same table, since all the attributes shown are part of the primary key. So it’s 3NF.

Your final question about breaking any database rule seems a little bit too broad and would need refinement. What exactly are you worried about?

  • i guess my worry is that it seems like a bad design. if I take a door out of my house and put it in another one. is it the same door? If the postoffice changes my postcode the Ids of all child objects change.
    – Ewan
    Jul 9 '21 at 1:18
  • @Ewan Indeed, if the post-office changes the PO code, since it’s a prime, you’ll have to change it everywhere. (fortunately, if FK constraints are well defined, the RDBMS could propagate the change for you). On the other side, if the if the city name changes (you laugh, but I live in a city that has 3 orthographies, depending on the language) you have just to change it in one place. The problem of the changeable prime key is not normalization related: it’s the question a choice of natural key vs surrogate key.
    – Christophe
    Jul 9 '21 at 5:48
  • 2
    @Ewan the door question raises another question, but with the same surrogate key remedy: it’s the question of the identity. What door do you want to track? Is it the location in a building (which is what your current design seems made for)? Or is it the movable piece of wood or iron that allows to close that location? In this second case, your design —although 3NF— would be questionnable. Because the physical door can be moved and you cannot identify it properly while it is in transit, unless your door number is already a unique identifier, in which case the location should be nonprime.
    – Christophe
    Jul 9 '21 at 5:58
  • 1
    @Ewan, on the same note as the comment by Christophe, if you reposition the hole in the building that people are meant to walk through, is that the same door as previously or is it a different door? Jul 9 '21 at 6:04
  • 1
    thanks guys I think your comments about door in transit vs holes in walls nails it for me
    – Ewan
    Jul 9 '21 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.