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

If I understand correctly, you already have separate tables for Properties
and AccomodationUnits.   Why not just have a separate table for photos?

tblPhotos
PhotoID
PhotoPath
PhotoDate
PhotoComments

PhotoID would be the FK in each of those tables.  I think that would allow
the greatest flexibility.  You could have photos of anything you track in
your db...staff, repairs, scanned documents, etc.


Mark


-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg] 
Sent: Tuesday, May 25, 2004 10:51 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


I'm just starting a new project and I'm in two minds about one aspect 
of the database design.  Since this has been discussed so much in the 
last few days, I thought I'd throw it in for discussion.

It's an Accomodation Register type thing for an organisation that has 
lots of staff housing.

AccomodationUnits are children of Properties (Sometimes there will be 
a house on a property, sometimes a block of several units, possibly  
dormitory style with individual rooms etc)

The client wants to store difital photos.  Photos could be of a 
particular unit or of the whole property. I will be storing the name 
of the photo file and pulling the image as required from it's storage 
location.

As I see it there are several possibilities including:

tblPhotos:
AccomUnit/PropertyID
AccomUnit/PropertyType
Filename.......etc

or 

tblPhotos:
AccomUnitID  
PropertyID   
Filename.......etc
(only store ID for the appropriate Property Type)

or

tblAccomUnitPhotos:
AccomUnit  
Filename.......etc

AND
tblPropertyPhotos:
PropertyID  
Filename.......etc



Any comments/suggestions as to how far I should normalize <g> and 
pros/cons of the various approaches - taking into account 
establishing relationships between tables in the BE, maintaining 
referential integrity, ease of data retrieval/maintenance in the FE?



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