Susan Harkins
ssharkins at gmail.com
Fri Aug 29 16:47:23 CDT 2014
I think I've been swayed -- as I stated earlier, I've really been overthinking this. We just don't have enough animals for this to become such a headache. I've got the lookups in place and I think I'm going with the simplified version. :) Susan H. On Fri, Aug 29, 2014 at 4:52 PM, Stuart McLachlan <stuart at lexacorp.com.pg> wrote: > I'm pretty much with Rocky on this, for a simple database with a limited > number of records, > having blocks of unused fields is no "biggie". > > But I wouldn't store "Live Birth, Acquisition, Law Enforcement" directly > in the table, I'd still > make that a lookup. If you want to make it easy to see in the BE, queries > etc make the Key > characters rather than numeric. "B,A,E" for example or "LB,A,LE" > > > -- > Stuart > > On 29 Aug 2014 at 12:02, Rocky Smolin wrote: > > > I always engineer my dbs for the constraints of the application and to > > make the user happy. I do things the easy way - for me. > > Normalization is great when you're talking 100k rows. Makes the app > > respond fast. But I try not to over engineer my apps. > > > > "A good program is one that works." > > > > R > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan > > Harkins Sent: Friday, August 29, 2014 11:52 AM To: Access Developers > > discussion and problem solving Subject: Re: [AccessD] Normalization > > discussion > > > > Seriously? I would love to do it this way -- nobody but me will ever > > work on it. > > > > Susan H. > > > > > > On Fri, Aug 29, 2014 at 2:42 PM, Rocky Smolin <rockysmolin at bchacc.com> > > wrote: > > > > > Since this is a small database used by a few people, you can make > > > life easy for yourself. Put all the fields you need for each of > > > the three type of acquisition into the Animal table. I would also > > > not bother with an Acquisition Type table with a PK and an FK in the > > > animal table. Just (gasp) put the words Live Birth, Acquisition, or > > > Law Enforcement right in the Acquisition Type field (text). > > > Unnormalized? Yes. Simple? Yes. And easy to see when you look in > > > the back end. > > > > > > In the front end if you want to get fancy, you can appear and/or > > > disappear the different fields you need depending on acquisition > > > type. Or enable/disable. But not necessary. > > > > > > R > > > > > > > > > -----Original Message----- > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan > > > Harkins Sent: Friday, August 29, 2014 10:54 AM 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 > > > > > -- > > 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 >