Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Question designing a library database

    this is the problem in brief,

    i am designing a database for a law firm to archive their references. References are in 3 types: books, articles, collective works. There are common attributes to the 3 types, such the Call number, title, date and place of publication. However, there also attributes that are different. eg : books have an author, physical description, publisher. Articles have an author, source(where they appear), and page of start and end.

    I am having problems designing the tables, even before thinking of normalization. Should design 3 tables for each type of reference, or should i go for 1 table and add an attribute to indicate the type of reference?

    Another problem is arising from the fact that references have subjects or topics, the relationship being a many-to-many relationship ( can probably be solved by normalization and adding an extra table), but i couldn't think of this before getting the core design right.

    I have designed quite a few databases, but this is the first time i have found such difficulty in understanding the concept.

    Any help or ideas will be appreciated.

    Thanks
    Sal

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd use a decomposition, where one table has the PK and any attributes common to all of the sub-classes. Then you add a separate table for each sub-class that contains the attributes that are specific to that class.

    This allows simpler foreign key relationships, since everything descends from a single parent, and you can establish all of your foreign key relationships to that parent. It makes dealing with common attributes like title, author, publication date, etc easy since there is only one place to find them. It allows you complete freedom with sub-class specific attributes, since they are in their own table.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Thanks for the idea Pat .. it hadn't occured to me ... I'll think about it, and hopefully get back to you .. but I already see it's not going to be easy deciding on the foreign key ...

  4. #4
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42

    Here's a Data Model ...

    Here's a Data Model that reflects Pat Phelan's advice and might provide some insight :-
    http://www.databaseanswers.com/data_...yers/index.htm

    Barry Williams
    Principal Consultant
    Database Answers

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Barry pretty much "hit it on the head", and added some nifty embellishments just to kind of "flesh out" the idea. I'm not sure why the foreign key is a problem, I'd just use the reference_id and be on my way.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •