Susan Harkins
ssharkins at gmail.com
Fri Aug 29 13:36:57 CDT 2014
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 >