I'm looking for a better way to manage employee photos for employee records in our database, and a way to query for the employee photos while having them on disk I have used a
FileTable to store all the
Then in my Employee View I created a join using substring, which I am concerned about. I am wondering if there is a better way of doing this.
Here's the View:
CREATE VIEW [emp].[ViewEmployees] AS SELECT ue.Id, ue.Active, ue.Comments, ue.Created, ue.IsLockedOut, ue.LastLoggedOn, ue.NameIdentifier, e.FirstName, e.MiddleName, e.LastName, e.DisplayName, e.Duties, e.EmailAddress, e.FullPart, e.JobTitle, e.LegacyId, e.OtherPhones, e.Phone, e.Profile, e.StartDate, e.ShowOnDirectories, ep.name as PhotoName, ep.file_stream as PhotoBytes FROM [security].[UserEntity] as ue Full Join [emp].[Employees] as e on ue.Id = e.Id Left Outer Join emp.EmployeePhotos as ep on SUBSTRING(e.EmailAddress, 0, CHARINDEX('@', e.EmailAddress, 0)) = SUBSTRING (ep.name, 0, CHARINDEX('.', ep.name, 0)) where ue.UserEntityType = 0
When you add files to the folder, it automatically creates a record in the file table with a new guid. We want to allow administrators access to that folder as a network share so they can drop new employee photos in it, or edit them. As such I can't really think of a better way to join it on my employee view.
Or maybe I shouldn't have it in the view at all, and instead do a separate query in the application to the file table for the photo.