Re: Help!

laura_andrews69_at_hotmail.com (laura) wrote in message news:<89631d0.0303121348.5b0bbd65_at_posting.google.com>...
> I am trying to build a library database that will eventually be
> searchable on an intranet.
>
> I need to catagorise four different types of artifacts: books, maps,
> brochures and industry standard documents.
>
> books: Title, author(s), publisher, year of publication, keywords,
> classification
> maps: area, code, publisher, year, keywords, classification
> brochures: name of supplier, title, year, keywords, classification
> industry standards: title, author, standard number, year of
> publication, keywords, classification.
>
> have i set up the tables correctly? can anyone suggest how i go about
> creating the relationships so i can start inputting the data? i

May i suggest that you design a different set of tables?

Entity tables:
Author (AuthorID, ....)
Publisher (PublisherID, ....)
Keyword (Keyword, .... ) probably no artificial KeywordID needed Classification (ClassificationID, Classification_Category, Class) Artifact (ArtifactID, Title, Year, ....)

Link tables:
Authors, Publishers Keywords and Classifications are N:M linked (each Artifact may have more than one author etc., each author may  have contributed to more than one artifact), requiring four linktables.

Author_Artifact (AuthorID, ArtifactID)
Publisher_Artifact (PublisherID, ArtifactID) Keyword_Artifact (Keyword, ArtifactID)
Classfication_Artifact (ClassificationID, ArtifactID).

Each link table consists of two foreign keys that reference Artifact and one of the other tables.

> envisage that most users will search using the keywords field. the

> classification field is a library system of letter and number that
> represents topics.

This would be recorded in table Classification. One of the Classification_Categories is 'Topic', with ClassificationID being the existing system of letters and numbers, and Class the name or description of topics.

Another Classification_Category is 'PublicationType' where Class contains either 'book', 'map', 'brochure', or 'industry standard'. As it stands, you have to extend ClassificationID with just four codes.

Taking this further, even 'Keyword' or 'Area' could be Classification_Categories recorded in table Classification. This means table Keyword is not needed anymore.

Sample rows from table Classification:

'A0001','Topic','Wildlife'
'A0002','Topic','Architecture'
'123Z1','PublicationType','Book'
'123Z2','PublicationType','Map'
'999AB','Keyword','Snow'
'999XX','Area','Greenland'

Sample rows from Classification_Artifact:

'A0001','00001' [artifact 00001 is about reindeer]
'123Z1','00001' [artifact 00001 is a book]
'123Z2','00002' [artifact 00002 is a map]

Sample rows from Artifact:
'00001','My book on reindeer', ....
'00002','Map of Lapland', .... Received on Thu Mar 13 2003 - 10:38:05 CET