3

Consider this schema in a relational database:

There are two tables A, B, both having two columns X, Y. The constraints are:

  1. X in both tables are the primary keys. (The values are unique in each table.)
  2. The data in both tables must be exactly identical. (Each insert, delete, update request must be applied to the two tables together.)

It is obviously not an ideal design. But does it not conform to some normal forms? Which normal form and why?

Does the same hold if there is another column in both tables but not identical? Or some rows are removed in one table?

13
  • 3
    Why is this relevant?
    – Doc Brown
    Commented Jun 16, 2021 at 5:10
  • @DocBrown I never really understood the NFs and never designed a database with NFs in consideration, but only do what is "right" and explain why the non-ideal cases (i.e. constraints) should be better left there. Until, I wanted to find some theory to prove whether a set of database operations and definition mechanisms is complete (actually I haven't designed it yet, just some initial ideas). And I found this. Maybe it means I shouldn't have too much confidence on the existing theories, or maybe I missed something obvious because I didn't understand it well.
    – user23013
    Commented Jun 16, 2021 at 12:27
  • 1
    NFs are not an end in themselves, they are a means to an end. This is about avoiding redundancy, for keeping data in a form where there is only a "single source of truth", to make a system less error prone. But some redundancy can be sometimes necessary or useful, and just because some design confirms or violates certain NFs is not a reason for or against this design. I heavily recommend you edit your question and tell us what kind of problem you want to solve with these two tables, then we may be able to make suggestions if it is a good solution or if there are alternatives.
    – Doc Brown
    Commented Jun 16, 2021 at 12:38
  • 2
    ... in the current form, this questions appears to be an XY problem.
    – Doc Brown
    Commented Jun 16, 2021 at 12:40
  • 2
    This question seems to be asking how to invent data replication from scratch. The short answer here is "don't". Most common database servers have been designed to do so. It is also vastly easier to have your application update one table and let the second one be automatically mirrored/replicated, as opposed to tasking your application with performing both updates, as a matter of consistency and avoiding forgetful behavior by developers.
    – Flater
    Commented Jun 16, 2021 at 12:44

3 Answers 3

Reset to default
2

Yes, this schema is at least in the first normal form, since it complies with the following statement, considering all the columns (i.e. attributes) of all its tables (i.e. relations):

A relation scheme R is in first normal form if the values in dom(A) are atomic for every attribute in R. That is, the values in the domains are bot lists or sets of values or composite values.
- David Maier in The theory of relational databases

I think it could also be viewed as compliant with the second normal form, which still considers each relation (table) separately:

A relation scheme r is in second normal form with respect to a set of functional dependencies F if it is is in first normal form AND every non-prime attribute is fully dependent on every key of R. A database scheme R is in the second normal form with respect to F if every relation scheme r in R is in second normal form with respect to F.
- Ibid

I have however a small theoretical doubt about whether the duplication you described should not be viewed as a functional dependency that would weaken the “fully dependent”. Maybe some academic or more advanced expert could comment on that.

However, it seems not compliant with the third normal form which requires:

Every non-prime attribute of R is non transitively dependent on every key of R.

I think that in your second table with duplicates, the second attribute does depend on the key and at the same time is transitively dependent on the same key in first table. This redundancy is not acceptable in 3NF, nor in higher normal forms which imply 3NF.

This being said, colloquially when normal forms is mentioned without further details, it is in general 3NF and higher that are meant and the redundancy in your scheme would be considered as non-compliant. Your question is therefore somewhat provocative and interesting.

4
  • After reading the definitions more carefully, I think it violates the spirit of 3NF, but some of the definitions may have technical problems. For A.X → B.X → B.Y → A.Y, B.X might be considered a candidate key. If not, if we have two tables A(X, Y) and C(X,Z), where A.X is a subset of C.X, A.X → C.X → A.X → A.Y. If we use B.Y instead, the problem is B.Y contains A.Y. And the definitions seems vague about whether the links between tables are considered a dependency, and what counts as a dependency in a table. Some may say if A.Y is the primary key X of another table D, D.X → A.Y.
    – user23013
    Commented Jun 16, 2021 at 14:34
  • The real important part should be B.X → B.Y, and that it is a part of the "transitive dependency". A possible better description is possibly: It should be in the simplest description of the partial order of the strongly connected components of the functional dependency relation. (But I'm not sure how similar it is to the original, such as how it works for the cases that only violates higher NFs.)
    – user23013
    Commented Jun 16, 2021 at 14:48
  • And there is still a problem left: What if each table has a different set of rows removed, but the rows sharing a primary key must be identical? (The ideal design would be a table C(X,Y) covering everything, and A(X), B(X) both being subsets.)
    – user23013
    Commented Jun 16, 2021 at 14:48
  • 2
    @user23013 In fact, the question is what do A and B represent as relations. If each table can have different rows removed, they are no longer identical. Are they really identical at all time? Or is the one supposed to be a version of some data at a given time? Or are both different truths from different sources that can sometimes overlap?
    – Christophe
    Commented Jun 16, 2021 at 15:39
0

The tables don't seem to violate any of the normal forms, yet:

  • The fact that both columns and data are exact duplicates could point to the fact that both tables represent one and the same entity, which means this design could conceptually lead to unconsistent data. This is a smell.
  • In the case both tables are not identical, that one-to-one relationship is either a sign both tables are the same entity or you've denormalized them for some legit reason, like is the case of certain attributes pertaining to certain specializations of the same more general identity (like attributes in "customer" or "employee" table that are not in the "person" table.
0

If I understand correctly, you have two tables like:

Book_A   Book_B  
------   ------
ISBN#    ISBN#
Title    Title

Which contain exactly the same information. This is a violation of the project-join normal form, which (paraphrased) says to eliminate tables or columns which can be replaced with a query. SELECT * FROM Book_A will give the exact same information as Book_B, and therefore Book_B can be eliminated (or Book_A for that matter, but only one of them!).

If the tables had additional columns, then you would still have a violation. E.g.

Book_C   Book_D  
------   ------
ISBN#    ISBN#
Title    Title
Year     Author

Now we can't eliminate a whole table, but we can still eliminate one of the duplicate columns, so we get (for example):

Book_C   Book_E  
------   ------
ISBN#    ISBN#
Title    Author
Year

Because the information in Book_D can be recreated by a join of Book_C and Book_E.

This schema is in 3NF, but it is in non-optimal 3NF because the same information could be expressed in fewer tables. An optimal 3NF would be:

Book 
------  
ISBN# 
Title
Author    
Year

In case some rows are removed in one of the tables, we need to preserve the information about which rows are present, even if we remove the duplicate title. E.g. if we have:

Book_A   Book_B  
------   ------
ISBN#    ISBN#
Title    Title

Where title is the same for a given ISBN, but Book_B is a subset of the rows in Book_A. Then we can eliminate the title, but not the key, like this:

Book_A   Book_B  
------   ------
ISBN#    ISBN#
Title

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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