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

In my infallible opinion, you are quite right. :-)

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules,
Mark S. (Newport News)
Sent: Friday, May 28, 2004 8:55 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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