Re: A database design question

From: AlanGLLoyd (alanglloyd_at_aol.com)
Date: 10/22/03 

Date: 22 Oct 2003 17:56:30 GMT

In article <cWwlb.331309$R32.10926952@news2.tin.it>, "Stark"
<starkwedder@virgilio.it> writes:

>To put my question, I find it easier to give you an example:
>Say I have a BOOKS table; each record represents a book and stores info
>such as author, library, and so on..
>Take the author field (but the same applies to library): I have two choices
>to represent it: 1) either I directly store the AUTHOR NAME, or 2) I store
>an AUTHOR ID, linked to another table (AUTHORS) where the names are stored.
>

I think in a real world you would have to delve deeper than that <g>.

If you think of Authors and Books and Libraries, then one Author can write many
Books, but one book may be written by more than one Author. Similarly one book
can be in many Libraries, and one library can have many Books.

Database design rests on reducing all data linkages to a one-to-many
relationship, and here you have two many-to-many relationships.

This paradox is resolved by having "link" databases to resolve the many-to-many
relationship into two one-to-many relationships. So one has ...

Authors - this has the AuthorsId, and other individual author specific data
AuthorBook - this has two fields, AuthorId and BookId
Books - this has BookTitle, and other individual book specific data
BookLibrary - this has two fields, BookId and LibraryId
Libraries - this has the LibraryId and other individual library specific data

Authors is then one-to-many to AuthorBook
Books is one-to-many to AuthorBook

... and...

Books is one-to-many to BookLibrary
Libraries is one-to-many to BookLibrary

Using such a related database, each piece of information (Author, Book,
Library) generates entries into more than one data tables. But information can
be extracted for any Author, or for any book, or for any Library.

An engineer named Codd defined a number of rules about database design with
related data - some quite esoteric, but basically common-sense (although it
wasn't common until he wrote it down <g>).

Some databases are simplified by coalescing some tables, but this always brings
limitations to data purity or data access. Sometimes these limitations are
acceptable and this acceptance results in an easier design of the storage and
access code.

Alan Lloyd
alanglloyd@aol.com

Relevant Pages