Susan Harkins
ssharkins at gmail.com
Fri Aug 29 13:41:55 CDT 2014
Dan, that's a good description of what I'm trying to do -- at this point, the individual animals are driving everything and I do have that main table and each individual does have a few attributes. We'll eventually add all kinds of activity -- health, diet, enrichment, safety protocols, but right now, we'd just like to get the animals entered! :) You know, I used to have all these rules memorized -- I felt like I could do this stuff in my sleep, but now... after all this time, I am truly feeling... like an idiot. :) I'm glad you guys are still around! Susan H. On Fri, Aug 29, 2014 at 2:18 PM, Dan Waters <df.waters at outlook.com> wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >