[AccessD] Normalization discussion

How can one single animal derive from two cities? If you can explain that,
then perhaps we need a M2M relationship table; but so far I do not see the
need, As far as I know, you are born in one place at one time, and that is
that.Of course, modern science being what it is, I could be way beyond the
historical picture.


On Fri, Aug 29, 2014 at 1:53 PM, Susan Harkins <ssharkins at gmail.com> wrote:

> 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
>



-- 
Arthur