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?
Best Answer
I suspect that most if not all of your
PropertyString
s are only valid for one specificPropertyType
and not all of them. In that case you would still have a transitive dependency betweenPropertyString
andPropertyType
which can be further normalized.To resolve this, make both
PropertyTypeId
andPropertyId
the primary keys of tablePropertyString
. (This would meanPropertyId=1 PropertyTypeId=1
is a different value thanPropertyId=1 PropertyTypeId=2
).But the question is if a further normalization is not just possible but actually beneficial. Denormalizing your database schema as jrhutch suggests might be the better direction to take. But that depends on your exact requirements. If all your
PropertyString
s are unique (or at least more often unique than duplicates), that additional table to store them would just be unnecessary complication without adding any real value.