Jim Dettman
jimdettman at verizon.net
Sat Aug 30 10:12:28 CDT 2014
Susan, I'd say I have to be the dissenting voice. While I understand the constraints, that this is small, you will be the only one working on it, etc, you do want to try and do it as right as possible because as we all know on this list, many things have a habit of turning into much more. I agree with the others; just list out everything you want to know about these animals, and then go from there. For example, every animal has a father and mother. You may not know it because it was an acquisition or that it came from law enforcement, but it does have one, you just don't know it. That's where a null comes in and is the difference between not having an attribute and not knowing the value for it. So me, I would have a sire and dam FK fields in the animal record and they remain null for anything other than a live birth. All records in a table should have the same "shape". That is, you should be able to fill in a value for every column (assuming you know it). If you cannot to that for a record, then your describing more than one "thing" in your table. I think by the time you make up your list, break things up into groups, you'll find it's just common sense. But to forgo normalization entirely will give you a database that is difficult to work on, have poor performance, and other issues. If your going to go that route, you might as well use a spreadsheet. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins Sent: Saturday, August 30, 2014 08:54 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Normalization discussion We have less than 50. 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 > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com