Attachment data type

[Ant_Snell]

Hi


I have a database which is a HR system that includes several tables with attachment data type fields used to upload documents such as sick notes, contracts, and qualifications etc. I will be eventually converting the back-end tables to MySQL and all my research suggests that attachment data type should not be used and will slow down the application. It would be a lot of work and less user friendly to convert all the attachment fields to one-to-many tables with file links and as the number of file uploads will be minimal (maybe 6 to 10 per year per staff member). I was thinking of creating a separate DB containing just the attachment fields in its own table then linking this back to the original tables using a one-to-one relationship. Leaving this as Access (not My SQL back end). This would mean all the form design etc. could stay as is and the user experience would remain simple. I know this is probably not the best solution but not being a developer was just looking for comments/ suggestions as to whether this would work or have pitfalls before I start working on it.

Thanks for your feedback

[Minty]

That's actually a quiet clever solution to the problem, but it does mean the attachment data wouldn't be available for any of the MySQL processing. e.g. a count to determine if there were any attachments.
You could maintain an index of the attachments in MySql as a reference I suppose, generated/maintained from the Access FE.

The other option would be to investigate storing the objects in the backend as binary objects, I know SQL Server can do this.

[Ant_Snell]

Thanks Minty, I'll give it a go then, I don't think at this stage I will need any counts etc. on the attachments.