[AccessD] Accomodation Register - Was: On DB Bloat...

I would welcome comments from others regarding my approach.  Am I alone in
my thinking?  Should I be doing something different in my own development?
Because it seems there is a fundamental difference in the way we each
normalize data.  I was always taught that a junction table (like
tblPropertyPhotos) is the way to handle a potential many to many
relationship.  I don't see the table as being redundant at all.  For
instance the rare instance where a photograph taken from a street corner
shows two adjacent properties, or the obvious instance of multiple
photographs of a single property.  So to address your current situation,
what I was trying to explain was a structure like this:

tblPhotos		tblPropertyPhotos
pkPhotoID_________fkPhotoID			tblProperties
PhotoPath		fkPropertyID____________pkPropertyID
PhotoDate		 				etc.
PhotoComments	 				etc.

And this:

tblPhotos_1		tblAccomUnitPhotos
pkPhotoID_________fkPhotoID			tblAccomUnits
PhotoPath		fkAccomUnitID___________pkAccomUnitID
PhotoDate		 				etc.
PhotoComments	 				etc.

Where tblPhotos and tblPhotos_1 are the same table holding paths to ALL of
your photographs.  The bonus feature of handling it like this is that it
would allow you to add photographs of anything you would like to track in
the future, such as:

tblPhotos_2		tblRepairPhotos
pkPhotoID_________fkPhotoID			tblRepairs
PhotoPath		fkRepairID______________pkRepairID
PhotoDate		 				etc.
PhotoComments	 				etc.

Or:

tblPhotos_3		tblEmployeePhotos
pkPhotoID_________fkPhotoID			tblEmployees
PhotoPath		fkEmployeeID____________pkEmployeeID
PhotoDate		 				etc.
PhotoComments	 				etc.

The upshot is that one particular PhotoID could be associated with a repair,
a Property, an AccomUnit or anything else you care to add to your structure.

Mark


-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg] 
Sent: Thursday, May 27, 2004 6:47 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Accomodation Register - Was: On DB Bloat...


On 27 May 2004 at 9:22, Mitsules, Mark S. (Newport Ne wrote:

> Andy's interpretation of my comments is correct.  Here is an example:
> 
> tblPhotos		tblProperties
> pkPhotoID_________fkPhotoID
> PhotoPath		pkPropertyID
> PhotoDate		etc.
> PhotoComments	etc.
> 

This won't work - Properties->Photos and AccomUnits->Photos are both
OneToMany  

> Or
> 
> tblPhotos		tblPropertyPhotos
> pkPhotoID_________fkPhotoID			tblProperties
> PhotoPath		fkPropertyID____________pkPropertyID
> PhotoDate		etc.				etc.
> PhotoComments	etc.				etc.
> 

Since it's not ManyToMany. the tblPropertyPhotos is redundant, you could 
achieve exactly the same thing with a fkPropertyId in tblPhotos.

Unfortunately, neither deal with the current situation,  which is that some 
photos relate to tblProperties and some photos relate to tlbAccomUNits.

I'm actually looking for pointer as to the best way to handle child records 
which are identical except that they have parents in different tables (in
this 
case with a further parent/child relationship to consider)

AccomUnits are Children of Properties.
Photos can be either children of AccomUnits( therefore Grandchildren of 
Properties) or direct children of properties. (Sort of like a hillbilly
family 
<g>)

where you will have EITHER  a direct link through FKProp or FPAccomUnit (but

never both) and  where tblAccomUnit also has a FKProperty. 

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com