CODEWITHSUNDEEP
databasedatabase-normalization
Franklin
Franklin

Reputation: 1801

Normalizing Two Column Table

I'm trying to normalize a database, and we currently have a table called BOOK, where ISBN (FK) and CoverType are the columns, and they're concatenated together to make a PK.

i.e.

BOOK

|       ISBN       |  CoverType  |
|__________________|_____________|
|  978-0132354790  |   Hardback  |
|  978-0132354790  |   Paperback |

Is this table already normalized? I'm assuming it is, but I don't really have much of a justification behind it. Thanks

Upvotes: 1

Views: 609

Answers (5)

Chris
Chris

Reputation: 21

Assuming more rows will be added, you can anticipate that values like "Hardback" and "Paperback" will be duplicated. Doesn't it make sense to Move those into a separate table such as "CoverType" and join on ID?

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

Is this table already normalized?

First a caution, then an answer.

A caution

Certain terms in relational modeling have pretty specific meanings. Normalized isn't one of them, at least not when someone uses it the way you did.

It makes sense to ask, "Is this table in 3NF?", or "Is this table in 5NF?", but it doesn't make sense to ask whether it's normalized. On SO alone, you can find answers where "this is normalized" means

  • it's in 3NF
  • it's in 5NF
  • it has an id number
  • it has less than 20 columns
  • all text has been replaced with id numbers

Only the first two make sense. The rest have nothing to do with normalization at all.

Finally, my answer

I'm assuming that your data makes sense. I've never dealt with books in more detail than at the accounting level, so I've never needed to know how ISBNs and cover types work together.

You could build your table like this.

create table books (
  isbn varchar(13) not null,
  cover_type varchar(10) not null,
  primary key (isbn, cover_type)
);

If you did that, you'd have no non-prime attributes (all columns are part of at least one candidate key), so you're in at least 2NF. No transitive dependencies, so you're in at least 3NF. No multi-value dependencies, so at least 4NF. No join dependencies at all, so you're at 6NF, or "ultimate normal form".

In real life, you'd want more constraints on those columns. I'd recommend at least two.

  1. Either a check constraint or a foreign key constraint on "cover_type".
  2. Calculate and compare the check digit on "isbn".

If you're only importing, you can write an external program to validate the check digits before you import.

Upvotes: 1

dwalldorf
dwalldorf

Reputation: 1379

Actually there is not much to normalize on that table.. You do not have redundancies nor you have relations. Looks fine.

Upvotes: 0

Romain
Romain

Reputation: 12819

As it is in your post BOOK(ISBN, COVERTYPE) is normalized because all your fields are single-valued, and no part of the primary key can be derived from a subset of it (e.g. you can't tell which are all the possible CoverTypes for an ISBN just by looking at the ISBN itself, and so is the other way round).

Upvotes: 2

Naftali
Naftali

Reputation: 146310

I'm not sure what is not normalized about it....

If there is anything else then I don't know, but from what I see, it looks fine.

Upvotes: 0

Related Questions