[AccessD] Re: Normalizing issue

Susan,

Then you have also painted yourself into a corner.  He gave you the 
ultimate in flexible design with the attribute definition system.  It 
allows you to define and classify attributes for any publication without 
the need for more tables later.  I was going to suggest it, but after 
reading his post, did not.

Jim was partially correct, but not totally.  When you are designing a 
database and you run into super-types and sub-types like what Susan has, 
you have to make a choice.  You can roll all of the sub-type attributes up 
into the super-type structure or, you can create them as separate 
tables.  The ultimate is the attribute schema that he proposed.

Super-Type             Sub-Types

tblPublication         tblPubBook          tblPubMagazine
PublicationID          PublicationID       PublicationID
Columns common to      Columns specific    Columns specific
all publications       to books            to magazines

In the super-type/sub-type schema, you would need to add a table for any 
new publication sub-type that did not completely share the attributes of an 
existing sub-type.

If you roll all the sub-type attributes into the super-type, you have to 
modify the super-type when you come across a new publication type that has 
different attributes you want to record.  You can solve the issue with 
seeing "book" attributes when entering a magazine by simply supplying the 
user with separate data entry screens.

If you use the attribute method, you never have to add a table. You can 
define new publication type and the attributes associated with them.  Your 
GUI can be a common GUI.  After selecting the publication type, your GUI 
can populate a temporary table with all the attributes for that publication 
type, giving the user the ability of entering the value for each attribute 
and then posting the results of that temporary table into the master table 
when data entry for the publication is complete.

With flexibility comes responsibility.  The idea for me is to shift the 
responsibility from me as the programmer to the user for the actual use of 
the program.  I no longer have to "think of everything" from the beginning, 
just show them how to use and report out of the system.

Susan, the following is NOT directed at you personally.

List, there are ways of making your designs totally flexible so your users 
never really need you to reprogram the solution again.  What they will need 
though is your guidance in how to use the solution you provide.  You also 
will need to become very proficient in GUI design to make them work 
well.  Anyone can slap together a table here and there and when something 
new comes up, add another table or even redesign the system because of all 
the tables added over time (like the one John was talking about).  A lot of 
us got into Access in the beginning because it was "easy."  But, good 
database design is not easy.  It takes a lot of thought and dedication to 
doing it correctly no matter what.

Robert


At 11:12 PM 6/23/2005, you wrote:
>Date: Thu, 23 Jun 2005 15:38:59 -0400
>From: "Susan Harkins" <ssharkins at bellsouth.net>
>Subject: RE: [AccessD] Normalizing issue
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <20050623193900.PGRZ8050.ibm62aec.bellsouth.net at SUSANONE>
>Content-Type: text/plain;       charset="us-ascii"
>
>Jim, I've already gone with separate tables because I got to thinking -- as
>this things grow I may run into other data that doesn't fit both books and
>magazines --
>
>Right now, we're just kind of playing with this. But if it works well and is
>flexible, a lot of different regions could end up using it -- so the more
>problems I resolve now, the better.
>
>Susan H.