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

On 1 jun, 00:13, "Tony Toews [MVP]" wrote:
> Adriano Varoli Piazza 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...

Saludos
Adriano

Adriano Varoli Piazza 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/