Re: Multiple tables refer to one -To use foreign keys or not?

Adriano Varoli Piazza <moranar_at_gmail.com> wrote:

>> >We have an "attachments" table, which can refer to "works_of_art",
>> >"people", "reproductions" (Let's say, more than three tables). So a
>> >work of art can have attachments, a person can have a different
>> >attachment, etc.
>>
>> What is an attachment?   I can understand the works of art, people and reproductions
>> but I don't know what you mean by attachment.  
>>
>
>An image, document or other file related to the entity in question.
>For a work_of_art, an image of a reproduction, a pdf copy of the
>invoice would be attachments. For a reproduction, a scan of the page
>in question where the work_of_art was reproduced (this is contrived,
>yes), for a person, a picture, a music file...

Ah, a file attachment. Too obvious. <smile>

I did almost exactly what you propose in a fleet management system. I have one table with a primary autonumber field (this is in Access), foreign keys pointing to the two other equipment and service order tables, a user entered date and description of attachment and the file extension.

An example of a file associated with a unit might be a scan of the vehicle registration, commercial inspection by a licensed mechanic or photographs. An example of the file associated with a service order might be a scan of a external invoice for windshield change or transmission work.. Or maybe some photos of the failed part.

The user was presented with a form listing all the files in their Incoming folder. They could view the attachments or process them. Processing involved associating the attachment with a particular unit or a service done on that unit. I then moved the file from their incoming folder into a network folder. I also renamed the file so it had the same value as the table's autonumber key although with leading zeros.

The file extension is required so the operating system can choose whatever software the user has installed to view that file.

I also had some fields allowing the user to optionally choose photo's which will be viewed in the app screens and on selected reports.

Now anyone can go into the system and view all scanned documents, photo's or other files assocated with each unit or service. No need to rummage around various file folders looking for specific invoices or other documents.

Tony

-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/