[AccessD] Virtual library management database

Jürgen,
many thanks for your kind reply. I'll consider your words of wisdom ;-)
Ervin
----- Original Message ----- 
From: "Jürgen Welz" <jwelz at hotmail.com>
To: <accessd at databaseadvisors.com>
Sent: Monday, January 23, 2006 6:50 PM
Subject: Re: [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
>
>
>


--------------------------------------------------------------------------------


> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>