Can this be further normalized?

Question

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?

Answer 1

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.

Answer 2

I don't see any way you could normalize this further. In fact unless each property could have more than one value I would denormalize a bit by combining the Property and PropertyString tables.

select I.ImageID, I.Data
  from Image I
  join ImageType IT on I.ImageTypeId = IT.ImageTypeId
  join Property P1 on I.ImageID = P1.ImageID
  join PorpertyType PT1 on P1.PropertyTypeId = PT1.PropertyTypeId
  join Property P2 on I.ImageID = P2.ImageID
  join PorpertyType PT2 on P2.PropertyTypeId = PT2.PropertyTypeId
 where IT.IsoCode = @ImageCode
   and PT1.Name = @PropertyName1 and P1.Value = @PropertyValue1
   and PT2.Name = @PropertyName2 and P2.Value = @PropertyValue2

I haven't tested it, but my guy says that this will perform better than having separate subqueries per property you are checking. If you're really want to maintain the PropertyString table you still could include it in this join.