An Introduction to Database Systems (8th Edition)

This is so fundamental (and has many years/decades still left) that I would go back to one of the sources e.g. https://www.amazon.com/Introduction-Database-Systems-8th-Edi...

a database table is a mathematical relation, in other words a predicate and a set of tuples ("rows") for which that predicate is true. that means each "row" in a "table" is a (true) proposition.

this may all look scary but the basic principles are really simple and worth knowing and applying rigorously: you'll better know what you're doing.

relations are simple if you start small, with the binary relation. for example, there's a binary relation > (greater than) on the set of all integers which "contains" all ordered pairs of integers x, y for which the predicate x > y holds true. note: you would not want to materialize this specific relation as a database table. :)

you want Books, Authors, Publishers and Users with their bookshelfs (Read, Currently Reading, Want to Read). what are the predicates in that? "user U has read book B", "user U is reading book B", "user U wants to read book B" would be some of them; "book B has ISBN# I, title T, author A" would be another, but some books have multiple authors. in that case, you'll do well to split it out into a separate predicate: "book B was written by author A".

CREATE TABLE book (
  id INT NOT NULL PRIMARY KEY
);
CREATE TABLE author (
  id INT NOT NULL PRIMARY KEY
, name TEXT NOT NULL
);
CREATE TABLE written_by (
  book INT NOT NULL REFERENCES book (id)
, author INT NOT NULL REFERENCES author (id)
);
CREATE TABLE reader (
  id INT NOT NULL PRIMARY KEY
);
CREATE TABLE has_read (
  reader INT NOT NULL REFERENCES reader (id)
, book INT NOT NULL REFERENCES book (id)
);
CREATE TABLE is_reading (
  reader INT NOT NULL REFERENCES reader (id)
, book INT NOT NULL REFERENCES book (id)
);
CREATE TABLE plans_reading (
  reader INT NOT NULL REFERENCES reader (id)
, book INT NOT NULL REFERENCES book (id)
);

etc etc.

edit: C. J. Date's Introduction to Database Systems

If you are new to the subject then Introduction to Database Systems by Chris Date is a good place to start before you get onto database design.

http://www.amazon.com/Introduction-Database-Systems-8th/dp/0321197844

For books specifically about design:

http://www.amazon.com/Information-Modeling-Relational-Databases-Management/dp/0123735688

http://www.amazon.com/Practical-Issues-Database-Management-Practitioner/dp/0201485559

A functional dependency defines a functional relationship between attributes. For example: PersonId functionally determines BirthDate (normally written as PersonId -> BirthDate). Another way of saying this is: There is exactly one Birth Date for any given given any instance of a person. Note that the converse may or may not be true. Many people may have been born on the same day. Given a BirthDate we may find many PersonId sharing that date.

Sets of functional dependencies may be used to synthesize relations (tables). The definition of the first 3 normal forms, including Boyce Codd Normal Form (BCNF) is stated in terms of how a given set of relations represent functional dependencies. Fourth and fifth normal forms involve Multi-Valued dependencies (another kettle of fish).

Here are a few free resources about Functional Dependencies, Normalization and database design. Be prepared to exercise your brain and math skills when studying this material.

The following are "slide shows" from various academic sites...

• Functional Dependencies
• Functional Dependencies and Normalization for Relational Databases
• The Relational Data Model: Functional-Dependency Theory

The following are academic papers. Heavier reading but well worth the effort.

• The Application of Functional Dependency Theory to Relational Databases
• A Simple Guide to Five Normal Forms in Relational Database
• Simple Conditions for Guaranteeing Higher Normal Forms in Relational Databases

If you are seriously interested in this subject I suggest you put out the cash for a good book on the subject of Relational Database Design. For example: An Introduction to Database Systems by C.J. Date

What I found from some quick googling. https://www.amazon.com/dp/0321197844

https://www.amazon.com/dp/1558605088

http://www.wiley.com/WileyCDA/WileyTitle/productCd-EHEP00071...