James Button
jamesbutton at blueyonder.co.uk
Fri Aug 29 14:04:08 CDT 2014
And I would include a searchable comments field on many of the entries so you can include a short set of notes - such as a "See documents in folder ___" Also consider that many of the 'acquired from' places will probably not exist, or have moved shortly after you get the animals from them - otherwise why would they not keep the animals Also - 'Technically' within the database you will identify the animal by the primary key of the entry in the 'Animal table' The rest of the detail - called by name, got from, are associated entries that may be in the Animal record or in an associated table as identified by: An entry in the Animal record giving the key to that other entry That entry having a FK that identifies the Animal record key (maybe more than 1 such entry as in vet attention) Possibly a marker in the Animal record to indicate that there are entries in the table associated with the marker Or even a count of those associated entries How you link the data is up to you - but again, consider how you are going to have the system generate reports and forms. And - will you be getting offspring from them - that will lead into a whole new concept of hierarchical entries for descendents of descendents of ... Think of the entries if you're breeding mice - or spiders! JimB -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Friday, August 29, 2014 7:37 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Normalization discussion No, it can't. It can derive from here, by actual birth at our center. Or, we can acquire it from another institution. When an animal is born here, we will know its parents. When we acquire it from another institution, we won't. I'm just identifying how we acquired the animal -- where it came from, how it came to be here. So, at this point, what I have follows: A table of individual animals identifying them by the name they go by here, species, and a few other fields. I'm also identifying each animal with an acquisition type. I have an acquisitionbirth table that includes the individual as a foreign key. I'll include the dam and sire ids as well. I have an acqsitioninstitution table that includes the individual and institution as a foreign key. There is a second table for institution information -- address, phone, and all that. This seems to make sense to me -- but I haven't tried to work with it yet. I'm not sure the acquisition type is actually needed -- but I need someway for the users to id the acquisition, so the app knows what forms to supply for data entry purposes. Susan H. On Fri, Aug 29, 2014 at 2:14 PM, Arthur Fuller <fuller.artful at gmail.com> wrote: > 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 > -- > 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