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

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

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 ...

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

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