[AccessD] Normalization discussion

Agreed that you would not need to be tracking species -
Accepting that for your purposes the animal entry is the primary data record 

I was using species as an example of a grouping (parent) level for a collection
of animals
Have a google for database cascade delete 
Basically the concept is that you define a set of primary tables with 'higher
level', or generic  entries that, under referential integrity  will be defining
conditions or classes, and then you enter in a child table the detailed data
that describes a specific thing that is associated with the entries in the
earlier defined tables

Perhaps more relative to your setup
You have a row in a table detailing an animal

Then you create a table of vets and enter into that the vets associated with the
establishment (credentials checked etc.) and then you enter that:
the vet (foreign key) 
did procedure (description) 
on animal (foreign key) 
on date and will be 
billing the agreed cost 
under workorder reference ___

The accounts dept will have additional details for that vet -
Local Address
Contact Phone
Office address
Office Phone
Bank account
Invoices paid
Due
Billed
Paid
Total business 
VAT
Payment terms used
Etc.

And when the invoice comes in it is entered under a bills table with
vet(foreignkey) workorder (foreignkey) for action on date(foreignkey) to animal
(foreignkey)

So there are checks that the billing is from authorised people for agreed work
done on known dates  on specific animals 

So you can get  reports on - a vet's billing, animals treated, dates attended
etc...
 
(And - as an aside - 
Note - nothing in the database related to comments on the work that could be
considered defamatory as a court search order would find them, but postit notes
- fell in the bin when the glue dried.)

BUT

Remember earlier I indicated that I believe that rather than being strictly
normalised, the structure is up to you and should be appropriate to the forms
and reports you want.

And - I would say actually avoid cascade delete because of the danger of losing
whole blocks of data because of 1 inadvertent deletion. 


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 6:39 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Normalization discussion

But you're talking in broader terms. In this database, I'd not be tracking
species without the animals. I would have no reason whatsoever to track
species.

I'm listening because you make sense -- but I'm not certain what you've
said actually applies to what I'm doing. The animals are the parent table
-- everything in the database applies to an individual animal.

Susan H.

> JimB
> 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
> >