I have to define a system where we will be storing Image data and which can have an arbitrary number of attributes for the Image added per client. I have designed the following table
Image
ImageID ImageTypeID Data
1 1 0x123...
2 1 0x123...
3 2 0x123...
ImageType - a predefined list of supported Image types
ImageTypeID IsoCode Name
1 12 Hitachi
2 1 Signotec
PropertyType - metadata for each arbitrary property. Can include columns for IsMandatory, datatype, minlength, maxlength etc but not needed at the moment
PropertyTypeID Name
1 AccountNumber
2 PIN
Property - binds PropertyType with Image
PropertyID PropertyTypeID ImageID
1 1 1
2 2 1
3 1 2
PropertyString - actual value of the property, right now we only care for string values as there is no need to do any computation on the values
PropertyID Value
1 0123456789
2 0123
3 9876543210
Given the above design I can query my database like so
DECLARE @ImageCode INT = 12;
DECLARE @PropertyName1 VARCHAR(64) = 'AccountNumber';
DECLARE @PropertyValue1 VARCHAR(1024) = '201602101821';
DECLARE @PropertyName2 VARCHAR(64) = 'PIN';
DECLARE @PropertyValue2 VARCHAR(1024) = '1234';
WITH prop1 AS (
SELECT P.ImageID, PS.Value
FROM PropertyType PT
INNER JOIN Property P ON PT.PropertyTypeID = P.PropertyTypeID
INNER JOIN PropertyString PS ON P.PropertyID = PS.PropertyID
WHERE PT.Name = @PropertyName1
), prop2 AS (
SELECT P.ImageID, PS.Value
FROM PropertyType PT
INNER JOIN Property P ON PT.PropertyTypeID = P.PropertyTypeID
INNER JOIN PropertyString PS ON P.PropertyID = PS.PropertyID
WHERE PT.Name = @PropertyName2
)
SELECT I.ImageID, I.Data
FROM Image I
INNER JOIN ImageType IT ON I.ImageTypeID = IT.ImageTypeID
INNER JOIN prop1 P1 ON I.ImageID = P1.ImageID
INNER JOIN prop2 P2 ON I.ImageID = P2.ImageID
WHERE IT.IsoCode = @ImageCode
AND P1.Value = @PropertyValue1
AND P2.Value = @PropertyValue2;
Each client will have a predefined set of extra Image attributes which will be used to narrow down the search (as shown above). The query itself will be generated based on the configured PropertyTypes.
Can this be further normalized? Is there any other way to provide the flexibility of having arbitrary number of field/values?