Re: A database design question

From: Bjørge Sæther (bjorge_at_hahaha_itte.no)
Date: 10/23/03 

Date: Thu, 23 Oct 2003 09:37:54 +0200

"Stark" <starkwedder@virgilio.it> skrev i melding
news:cWwlb.331309$R32.10926952@news2.tin.it...
> 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 thought option 1 to be the soundest choice, since it gives greater
> flexibility (for instance, it allows changes in the author names with no
> impact on the BOOKS table).
> Then I found there are disadvantages also. Apart from the difficulty in
> keeping syncronized BOOKS and AUTHORS, the major thing is the
impossibility
> to define a BOOKS table index on the Author Name (I found I can define an
> authorName field in books as a lookup field, but I cannot define an index
on
> it. BTW I am using dBase, if this makes a difference..)
>
> Can annyone help with some thoughts or advice ?

First of all, you need to define whether Author is a "meaningful value" or
just info. If your application "does things with Authors", then you probably
should keep Authors in a separate table. If you're gonna handle more
authors, you have more choices:
- Use ID_AUTH_1, ID_AUTH_2 .. ID_AUTH_N fields, which makes lookups possible
- List ID_AUTHOR's commaseparated in a string field, which breaks your
lookup functionality and requires you to write the lookup code by hand
- Use a rendundant (superfluous) field in addition to ID_AUTHORS:
NAME_AUTHORS, which is updated whenever ID's are changed

To be able to sort on Author's Name, you would need to add the rendundant
AUTHOR_NAME field, this requires some functionality to update BOOKS table
whenever a name is changed in AUTHORS table.
The other option is using SQL for ordering the items. Then you need no index
(but it may be slow).

Or, you could of course use a TClien tDataSet if you have Delphi
Enterprise/Studio. Then all boks data will be loaded into memory, and
sorting is no problem. 

-- 
Regards,
Bjørge Sæther
bjorge@haha_itte.no
-------------------------------------
I'll not spend any money on American Software products
until armed forces are out of Iraq.

Relevant Pages

  • Re: A database design question
    ... As far as being able to sort on Author's Name, I exactly did what you are ... I want to see my books by title or by author name. ... > lookup functionality and requires you to write the lookup code by hand ... > To be able to sort on Author's Name, you would need to add the rendundant ...
    (comp.lang.pascal.delphi.misc)
  • Re: links update
    ... I have a situation when cells A1:A100 from the MainBook ... Everything is through LookUp. ... >>Tom Ogilvy ... >>> books have thier own links through LookUp with MainBook ...
    (microsoft.public.excel.programming)
  • Re: Search Form - text string.
    ... I believe I'm going to head out get one of your books. ... So if I do not use Lookup fields what should I use? ... than trying to teach me the basics on a newsgroup. ... Can repeat for any individual search using a combobox to ...
    (microsoft.public.access.formscoding)
  • Re: links update
    ... I have a situation when cells A1:A100 from the MainBook ... Everything is through LookUp. ... >the lowest level of a chain of links first and work ... >> books have thier own links through LookUp with MainBook ...
    (microsoft.public.excel.programming)
  • Re: links update
    ... the lowest level of a chain of links first and work upward in a breadth ... which have links through LookUp ... > function with many other books Book1,Book2,... ... > Set xlbook = xlapp.Workbooks.Open ...
    (microsoft.public.excel.programming)