[AccessD] Normalization discussion

Susan Harkins ssharkins at gmail.com
Fri Aug 29 16:47:23 CDT 2014


I think I've been swayed -- as I stated earlier, I've really been
overthinking this. We just don't have enough animals for this to become
such a headache. I've got the lookups in place and I think I'm going with
the simplified version. :)

Susan H.


On Fri, Aug 29, 2014 at 4:52 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> I'm pretty much with Rocky on this, for a simple database with a limited
> number of records,
> having blocks of unused fields  is no "biggie".
>
> But I  wouldn't store  "Live Birth, Acquisition, Law Enforcement" directly
> in the table, I'd still
> make that a lookup. If you want to make it easy to see in the BE, queries
> etc make the Key
> characters rather than numeric.  "B,A,E" for example or "LB,A,LE"
>
>
> --
> Stuart
>
> On 29 Aug 2014 at 12:02, Rocky Smolin wrote:
>
> > I always engineer my dbs for the constraints of the application and to
> > make the user happy.  I do things the easy way - for me.
> > Normalization is great when you're talking 100k rows.  Makes the app
> > respond fast.  But I try not to over engineer my apps.
> >
> > "A good program is one that works."
> >
> > R
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan
> > Harkins Sent: Friday, August 29, 2014 11:52 AM To: Access Developers
> > discussion and problem solving Subject: Re: [AccessD] Normalization
> > discussion
> >
> > Seriously? I would love to do it this way -- nobody but me will ever
> > work on it.
> >
> > Susan H.
> >
> >
> > On Fri, Aug 29, 2014 at 2:42 PM, Rocky Smolin <rockysmolin at bchacc.com>
> > wrote:
> >
> > > Since this is a small database used by a few people, you can make
> > > life easy for yourself.  Put all the  fields you need for each of
> > > the three type of acquisition into the Animal table.  I would also
> > > not bother with an Acquisition Type table with a PK and an FK in the
> > > animal table. Just (gasp) put the words Live Birth, Acquisition, or
> > > Law Enforcement right in the Acquisition Type field (text).
> > > Unnormalized?  Yes.  Simple?  Yes.  And easy to see when you look in
> > > the back end.
> > >
> > > In the front end if you want to get fancy, you can appear and/or
> > > disappear the different fields you need depending on acquisition
> > > type.  Or enable/disable. But not necessary.
> > >
> > > R
> > >
> > >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan
> > > Harkins Sent: Friday, August 29, 2014 10:54 AM To: Access Developers
> > > discussion and problem solving Subject: Re: [AccessD] Normalization
> > > discussion
> > >
> > > What I'm struggling with today is acquisitions. There are three ways
> > > that we acquire an animal -- live birth here at the center, from an
> > > institution, or through law enforcement confiscation (almost never
> > > happens, but I'm allowing for the possibility). So, each animal has
> > > an acquisition type, but depending on the type the subsequent data
> > > will be different. For example, if a live birth here at the center,
> > > we will also track the dam and sire ids. If we acquire an animal
> > > from an institution, we'll track the institution's info, like name,
> > > address, phone, and a contact.
> > >
> > > So, an animal has an acquisition id by type. It's easy to keep an
> > > institution and live birth table -- that's no problem. But how do I
> > > relate them back to the individual since it can come from two
> > > different tables? I think this is probably easier than I'm making
> > > it.
> > >
> > > Susan H.
> > >
> > >
> > > On Fri, Aug 29, 2014 at 11:39 AM, Susan Harkins
> > > <ssharkins at gmail.com> wrote:
> > >
> > > > Right now, most of my foreign keys in the parent table are simple
> > > lookups.
> > > > For instance, species -- each individual has a species and those
> > > > are in a child table. We'll update that table infrequently -- only
> > > > if we add a new species. We have several individuals of each
> > > > species. Another is gender -- female and male, but the parent
> > > > table has a foreign key to the gender table. (Gender's probably
> > > > overkill, but I'm on autopilot I think). I'm also tracking
> > > > acquisition method -- there are only three, so again, it's a
> > > > lookup table and not a true child
> > table.
> > > >
> > > > Susan H.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > On Fri, Aug 29, 2014 at 10:18 AM, Charlotte Foust <
> > > > charlotte.foust at gmail.com> wrote:
> > > >
> > > >> A foreign key is a key that points to the PK in another table.
> > > >> You're mixing keys with parent child relationships, which are
> > > >> defined through keys.  In a data warehouse, you put a bunch of
> > > >> keys in the central table, but in a regular database you put the
> > > >> parent PK into the child tables so they know their mommy,  The
> > > >> idea is to put the keys
> > > where they are needed.
> > > >>
> > > >> Charlotte
> > > >>
> > > >>
> > > >> On Fri, Aug 29, 2014 at 6:07 AM, Susan Harkins
> > > >> <ssharkins at gmail.com>
> > > >> wrote:
> > > >>
> > > >> > I'll spend the morning rereading the book Martin and I wrote,
> > > >> > brushing
> > > >> up
> > > >> > on the normalization part. I've forgotten a lot of the basics.
> > > >> > I'm
> > > >> writing
> > > >> > an animal tracing database in Access and I'm trying to remember
> > > >> > if it matters where the fk goes. Now, I remember its purpose
> > > >> > and all that,
> > > >> but it
> > > >> > would be so much simpler if I could just drop them all into the
> > > >> > main
> > > >> table
> > > >> > instead of adding a fk to all the child tables to the main
> > > >> > table -- I
> > > >> think
> > > >> > anyway.
> > > >> >
> > > >> > So, I've got a main table of animals and all of the remaining
> > > >> > tables are child tables of a sort and a few lookup tables. Is
> > > >> > it reasonable to just add a fk to all those child tables in my
> > > >> > parent
> > > table?
> > > >> >
> > > >> > I just don't remember. I haven't built a database in...
> > > >> > seriously... 10 years? It's been long enough that I'm really
> > > struggling.
> > > >> >
> > > >> > Susan H.
> > > >> > --
> > > >> > AccessD mailing list
> > > >> > AccessD at databaseadvisors.com
> > > >> > http://databaseadvisors.com/mailman/listinfo/accessd
> > > >> > Website: http://www.databaseadvisors.com
> > > >> >
> > > >> --
> > > >> AccessD mailing list
> > > >> AccessD at databaseadvisors.com
> > > >> http://databaseadvisors.com/mailman/listinfo/accessd
> > > >> Website: http://www.databaseadvisors.com
> > > >>
> > > >
> > > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list