Can this be further normalized

database-designnormalization

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

enter image description here

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 PropertyStrings are only valid for one specific PropertyType and not all of them. In that case you would still have a transitive dependency between PropertyString and PropertyType which can be further normalized.

To resolve this, make both PropertyTypeId and PropertyId the primary keys of table PropertyString. (This would mean PropertyId=1 PropertyTypeId=1 is a different value than PropertyId=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 PropertyStrings 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.