Peter Ryder wrote:
Dear all,
I am a newbie to OO Base (and data base programming in general). I am
trying to create a data base for a book collection. So far I have two
tables: "books" and "publishers". The "books" table has a field
"publisherID" which is linked to the primary key of the "publishers"
table. So far so good. Now I want to do the same for the authors. The
problem is that many books have more than one author, so I have the
fields "author1", "author2" etc. in the "books" table. Now if I have a
table "authors", the assistant will not let me link its primary key to
all the author fields of the "books" table, but only to one field. I
assume there is some good reason for this. So do I have to have
separate tables for "author1", "author2" etc., or is there a solution
which requires only one table of authors?
Hi Peter,
Well, regarding the GUI relation designer not allowing you to make
multiple connections between two tables - yes, there is a good reason
for that and it is spelled BUG...it has been a long standing one at
that, but is actually fixed in this next release. Which is not to say
that you can't do it in Base, just that you have to resort to doing so
with SQL commands and not the GUI.
That said, there are two things worth mentioning.
1) The 'formal' way to handle this situation is not with multiple
columns ( Author1, Author2...) - because - what happens when you need
Author3 or AuthorX...do you keep adding columns? So the 'formal' way
would be to use a middle table to handle this relation.
Book( BookID....) [BookID is the PK field for this table]
Author( AuthorID...) [AuthorID is the PK field for this table]
BookAuthors( BookID, AuthorID) [BookID AND AuthorID are the PK fields
for this table and most often the only fields]
This BookAuthors table allows you to have as many authors as you need
for a book. Now I used the term 'formal' twice above, because the use of
multiple fields in the fashion you describe is common enough for small
databases, and using the extra table adds a bit of complication to
building your forms and reports. One of these complications is the
situation of a book without an author. What I mean by that is that if
you add the authorID fields to the Book table then you can easily insure
that you will always have at least one AuthorID entry by just declaring
AuthorID_1 as "required". When you use the relation table this is a bit
more difficult to enforce.
Which brings me to point two.
2) Referential Integrity is not always that big a deal...( oh the heresy
;>). What I mean is that in a desktop database for personal use you can
often get by with just using the features supported in Form->SubForm
functions without actually creating the FK relations. If you are not
going to be importing data from other databases and not going to be
doing updates with SQL INSERT statements then not having FK's setup for
the tables is not really that much of a problem. The form->subForm
mechanisims still work, you can still do joins in queries to generate
reports and the like, just fine.
So - for what it's worth my advice would be to go ahead and leave the
AuthorID fields in the Book table. Make the first one "required" and go
ahead and make it a FK to the Author table in the GUI relation designer
if you like. Then when you create your form for Books just use a drop
down list box for each of the AuthorID fields and you will be fine.
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
