[AccessD] Normalization discussion

Decide if the data can be assembled in 1 common table with some fields not
entered, or set to specific values  "N/A" or 1/1/1900 
Or do you need different tables for the different sources ?
As in confiscated from another institution and they have the breeding records
from the institution they got it from -
 2 sets that give different information, maybe genetic modified combination of
genes with 3 parents!
(Yes - devilish view in'tnit)

Then will you have 1 or more than 1 of each of those entries for each animal?

Those entries will have a FK to point to the animal id (PK on the animal table) 
You then have to decide if there is to only be 1 of each kind of entry than you
can have foreign keys in the primary Animal id record so they can be selected on
the basis of the fk existing, or not
Or if you will need to include a SQL (whatever) search for entries in those
notes tables that are associated with the Animil id entry

So you can have keys in each level pointing to the other entry, or just have all
with the PK of the animal record and go looking for them 



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