CODEWITHSUNDEEP
databasedatabase-designdatabase-normalization3nf
Christopher
Christopher

Reputation: 63

3NF one-to-one relationship controversy

I am running into a problem with my database when trying to design it in 3NF. A database in 3NF has the following characteristics: -it is in 2nd normal form. -It's tables contain only columns that are non-transitively dependent on the primary key

I have been seeing many people online claim that if you have a one to one relationship you should strongly consider analyzing why it is not just one table, yet my understanding of 3NF is just to do that. Here is my database so far: enter image description here I have tried to create this database in 3NF but so far I am not sure if splitting up the BOOK_STATS and BOOK_DETAILS tables is done correctly. I have analyzed it and determined that things like the file type and quality are not dependent on the book at all so I had to separate them. Is this proper or do I need to take a stronger look at why they are being split?

Upvotes: 0

Views: 2684

Answers (1)

TAM
TAM

Reputation: 1741

Splitting a table into more than one table with the same primary key neither violates any normal form, nor does it heal potential violations. This can easily be made plausible by regarding the steps necessary for normalizing a not yet normalized table: All these methods introduce tables with a primary key different from that of the original table. So introducing a table with the same primary key can't be a normalization step.

I belong to the fraction claiming that 1-1 relationships should be considered to be merged to one table, unless there are strong semantic or physical reasons to separate them. However, the following questions might be asked about your book_details table:

  • The notion of "book" can mean two things: A logical entity, i.e. authors, title, content, or the physical instance. The same distinction holds for books contained in files, as your model indicates. Orwell's 1984 might be present in more than one file, a PDF in good quality, and an epub in mediocre quality, having different file sizes. Or a large book might be split in more than one file. I don't know the purpose of your model and application, but I would try to cover real-world phenomena like this, as adding them later will affect large parts of the application and be costly. So I could imagine to transform your book_detail table to a media table having a 1:n or even m:n relationship to the book.

  • Are there pre-defined value ranges for file_quality and file_type, like "excellent, good, mediocre, bad", "pdf, epub, txt, ..."? In that case, these values should be part of another table, and your book or book_detail would just contain the id in that table as foreign key.

Upvotes: 2

Related Questions