[AccessD] Normalization discussion

Hi Susan,

I know I'm jumping into this after a lot of discussion has already occurred.


You might consider having a single table for each animal with all its
one-time information.

I would treat this as a business process that has a beginning, middle
stages, and an end.  The tblAnimalMain table would carry the information for
each animal that would be recorded only once, like acquisition, animal
characteristics, current storage location, final disposition, etc.
tblAnimalMain would be your top-level table.

Because each animal is acquired in one and only one way, you could put the
acquisition information into the tblAnimalMain table.  Not all fields in
tblAnimalMain would be used for every animal.

I would be thinking of one-many tables like tblExamPlusResult (with another
one-many table for specific tests that were run and the result of each, for
each exam).  You'll also want to keep track of treatments/surgeries and the
results of those (could be another one-many).

Hope this is helpful,
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Friday, August 29, 2014 12:54 PM
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