[AccessD] Virtual library management database

Ervin:

This is a subject on which opinions will vary along lines of what people 
perceive the priorities are.  A flatter file will generally provide better 
performance and a more normalized one will ordinarilly be be both slower and 
more complex to query yet provide greater flexibility.

Generally, it is easier to query if you include general items such as Title 
and Author in a single table that encompasses all categories and types of 
media.  Where a object may not have an attribute  not universal to other 
objects, it makes sense to use an unequal join to such tables to return 
those attributes.  A user search could then return all medai types and all 
relevant attributes on a generic search on a word in the title.  You can 
normalize this as much as necessary by joining tables containing attributes 
of specific media categories.

For example, if you store information about an article in a magazine, your 
base table containing an periodical article name and author could easily be 
queried and be joined to a magazine article table that stores the containing 
Magazine information and page references.  This would allow the user to be 
able to include all types of media in a single search.

If you break your tables along media type lines, it would be necessary to 
union your search results across all the tables to provide an encompassing 
search.  Union queries cannot (in versions of Access I am familiar with) 
include memo fields that may be necessary to contain descriptive informaion.

Using a report as output simplifes things in that you can use a sub report 
for each category, each with its own SQL source.  However, my preference is 
to always provide a list output where a user can click on an item and have 
it open the appropriate form that contains all relevant information with 
appropriate joins, perhaps with the foreign keyed data in subforms.  
Although the Union restriction on memo types will not apply in the case of 
search results in a list box, I find it easier to return results from a 
single table and show the media type and allow futher filtering on type of 
media in the list result.

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com


>From: "Ervin Brindza" <viner at EUnet.yu>
>
><second attempt>
>I was asked to do some voluntary work for some community. I need some ideas 
>in designing a virtual library management database. The database will 
>manage various matherial for amatheur folk dancers, mostly with their home 
>made videos and sound matherial, books, magazines, video tapes, CDs(with 
>many short video films), DVD discs, sound recordings(LP discs, sound 
>casettes etc.) etc.
>So there are two types of entities: "simple"entity(e.g. books or DVD discs 
>with one film) and "complex" (e.g. magazines with articles of various 
>category, CD or DVD discs with more than one video film). The last entity 
>need a main table in a 1 to many relationship with the "sub" table, but the 
>first will have nothing on many side, if I put all of them into these two 
>tables.
>For books and DVD discs are important: Title, Author and Category
>For video tapes and sound cassettes are important(for each short film): 
>Order No., Name of the film(or song), Lenghth, Category
>For magazines(for each article): Title, Author and Category
>The main goal of the system is to search on category, so when the user 
>"ask" the system for e.g. Irish step to get all the book, videos, books, 
>articles in one report.
>So, my question is: is it clever to put all those various types of entities 
>into one table(and connected table) or to separate each type of entity into 
>own table.
>Many thanks for suggestions,
>     Ervin