If I want to model say a book where I have a root element of book represented as
n pages, can I do something like this:

table BOOK
-----------------------------
pageid FK

and

table PAGES
-----------------------------
pageid PK
content text

i.e do I need to have a (f.e.) primary key in the BOOK table? The only reason I
can see that I would want to is if I want to have many different books but then
I would have to have BOOK, and then a BOOK_PAGES joining table no?

Danke!

-Tomas

,%%%,
,%%%` %==--
,%%`( '|
,%%@ /\_/
,%.-"""--%%% "@@__
%%/ |__`\
.%'\ | \ / //
,%' > .'----\ | [/
< <<` ||
`\\\ ||
)\\ )\
^^^^^^^^"""^^^^^^""^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^
THE DUKE

etp wrote:


As long as you only have one book you have no need for a primary
key ;-) You should always have a primary key. In fact many
DBMS require one.

You do not need a BOOK_PAGES joining table. You could add a
column for BOOK_ID to your PAGES table.

--
Once I learned how to spell DBA I became one -- Jerry Gitomer

etp wrote:
That's the wrong question. You should first ask if you really need the BOOK
table. Since it is always going to be a projection of the PAGES table on
pageid it doesn't really add any information that is not already in the
PAGES table.

No. All you would need is a FK in PAGES that points to the PK of BOOK and
indicates to which book the page in question belongs.

Bitte.

-- Jan Hidders

PS. Homework: Explain the relationship between Wittgensteins famous last
proposition (#7) in the Tractatus Logico Philosophicus and the
requirement that every table should have at least one candidate key.

In the last exciting episode, etp <etp (AT) spamoutgun (DOT) com> wrote:
You almost certainly should have a UNIQUE primary key on every relation.

Relations are used to express facts, and it doesn't normally make
sense to have duplicate facts. Duplicates tend to lead to trouble...

You might have multiple copies of a book in a library; in that case,
it would make sense to have those books keyed on (ISBN, COPY_NUMBER),
as the books are physically distinct even if they appear identical.

In the case of your BOOK relation, it might make sense for the
relation to look something like:

create table book (
isbn isbn,
page integer
primary key (isbn, page)
);

(Where the ISBN domain is one that characterizes ISBN ids. You might
alternatively use an internal identifier, and have the ISBN in a "book
catalogue" table...)
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/postgresql.html
Lisp stoppped itself
FEP Command:

cbbrowne (AT) acm (DOT) org says...


Maybe this true in theory, but not essential in practice?

I'm not being facetious here.


Take an example that I recently worked on - we have a lookup table of 26
counties (in Ireland) which really has a very small about of data in it,
say max. 1.5K.


Now, as I understand it, RDBMS's will look at the size of a table before
scanning it to see if it's worthwhile using an index, and if the table
is too small, it'll just perform a straigh scan anyway and not bother
with the index, even if you've gone to the trouble of putting one in.


AFAIK, this is true for the RDBMS's that I use (Interbase, FireBird and
PostgreSQL).



Any thoughts, rants, references, URLs on this topic welcomed.


Paul...


--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

p.s. just lacerated a tendon in left hand, so pls
excuse typos and tricky abbrevs - TIA.

Paul wrote:
As far as anyone can tell, this is only true for some subset of dbms's
right now. Whether it holds true for any dbms in general or any of the
aforementioned at some other time is impossible to say. The
implementation that some specific db uses at some point in time is
entirely irrelevant to the topic at hand. The existence of a primary key
is a topic related to the structure of the data, not an implementation
strategy in some specific dbms.


Shane

"Paul" <paul (AT) not (DOT) a.chance.ie> wrote in
Paul,

You are confusing an index with a key. Index is physical, while a key is
logical. Not all keys need to have a related index. Even if the key has an
associated index, it need not be used in the queries. For example, some
RDBMSs don't create indexes (indices) for the foreign keys, while some of
them create the associated index automatically. In some RDBMSs, even the
unique indexes for the primary/unique key have to be explicitly created.

Here is one URL that might be of use:
http://www.aisintl.com/case/relational_keys.html


Hope you get your tendon fixed soon.

-- Venkat

Paul <paul (AT) not (DOT) a.chance.ie> wrote in
news:MPG.19b1b482826a20ab989723 (AT) news1 (DOT) eircom.net:

Aside what Venkat points out, the more complex the query (more tables
in the join) it's possible that the above becomes grossly
inefficient. In-memory table scans can be very costly when paired
with (millions of) nested iterations.
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com

Yes, otherwise it is not a table by definition. Assuming that you
have more than one book, use the ISBN as the industry standard
identifier and use this table:

CREATE TABLE Books
(isbn CHAR(10) NOT NULL,
page_nbr INTEGER NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (isbn, page_nbr));

if you had only one book, use its title for the table name:

CREATE TABLE "Moby Dick"
(page_nbr INTEGER NOT NULL PRIMARY KEY,
content TEXT NOT NULL);

You are splitting attributes in the original schema. That is, you put
the attributes of a book in (n > 1) table, so the data model was not
complete.

"Paul" <paul (AT) not (DOT) a.chance.ie> wrote

Uniqueness is a logical constraint independent of any physical structure
like an index. It is true that SQL generally confuses the two levels by
making uniqueness an attribute of an index.

Without any uniqueness, how do you plan to distinguish between rows?