0

I have a table item in which different columns are filled depending on the template type of item that it is e.g.

|item_id|template_id|brand|model|heading|usp_1|usp_2|icon_1_asset_id|icon_2_asset_id|
|-------|-----------|-----|-----|-------|-----|-----|---------------|---------------|
| 1     | 1         | ABC | xyz |       | ddd |     | 1             | 2             |
| 2     | 1         | ABC | xyy |       | eee |     | 3             | 4             |
| 3     | 2         | BCD | ghj | opqrs |     |     | 2             |               |
| 4     | 3         | FGH | stu |       | jjj | kkk |               |               |

In reality, it has about 40 columns. template_id has a foreign key to the template table, icon_1_asset_id and icon_2_asset_id both have foreign keys to the asset table. The columns filled depend on the template_id, in this example:

  • All items have a template_id, brand, model.
  • template_id = 1 items have usp_1, icon_1_asset_id, icon_2_asset_id
  • template_id = 2 items have heading, icon_1_asset_id
  • template_id = 1 items have usp_1, usp_2

As you can see, there is a large amount of wasted space of cells that will never hold data.

I have considered altering the structure to:

Table item

|item_id|template_id|
|-------|-----------|
| 1     | 1         |
| 2     | 1         |
| 3     | 2         |
| 4     | 3         |

Table property_type

|property_type_id| description     |
|----------------|-----------------|
| 1              | brand           |
| 2              | model           |
| 3              | heading         |
| 4              | usp_1           |
| 5              | usp_2           |
| 6              | icon_1_asset_id |
| 7              | icon_2_asset_id |

Table template_property_type

|template_id|property_type_id|
|-----------|----------------|
| 1         | 1              |
| 1         | 2              |
| 1         | 4              |
| 1         | 6              |
| 1         | 7              |
| 2         | 1              |
| 2         | 2              |
| 2         | 3              |
| 2         | 6              |
| 3         | 1              |
| 3         | 2              |
| 3         | 4              |
| 3         | 5              |

Table item_property

|item_property_id|item_id|property_type_id| value |
|----------------|-------|----------------|-------|
| 1              | 1     | 1              | ABC   |
| 2              | 1     | 2              | xyz   |
| 3              | 1     | 4              | ddd   |
| 4              | 1     | 6              | 1     |
| 5              | 1     | 7              | 2     |
| 6              | 2     | 1              | ABC   |
| 7              | 2     | 2              | xyy   |
| 8              | 2     | 4              | eee   |
| 9              | 2     | 6              | 3     |
| 10             | 2     | 7              | 4     |
| 11             | 3     | 1              | BCD   |
| 12             | 3     | 2              | ghj   |
| 13             | 3     | 3              | opqrs |
| 14             | 3     | 6              | 2     |
| 15             | 4     | 1              | FGH   |
| 16             | 4     | 2              | stu   |
| 17             | 4     | 4              | jjj   |
| 18             | 4     | 5              | kkk   |

Although this gives more efficient storage and an amount of database-level control over which properties can be filled, you loose the foreign key constraint on the property value due to the fact that some properties are linking to another table and some are plain text.

Is there a way to put some database-level constraint on the value column - possibly by breaking it out onto another table, or re-working the structure?

Or is there a completely different way to approach this type of data?

I'm using SQL Server, although the fundamental problem is not tied to any specific relational database management system.

  • re: the current item table ... what do you mean by 'wasted space'? if the columns are defined as nullable then there's little space usage (perhaps a single-byte null pointer at the physical data layer) for 'empty' columns; as for the redesign ... do you envision a regular need to modify templates that would require going back and a) updating older records in the item table and/or b) modifying the structure of the item table (eg, add/drop columns, redefined column datatypes)? – markp Oct 3 '19 at 15:50
1

You can enforce the column nullability based on the template_id by using CHECK CONSTRAINTS, for example:

ALTER TABLE [item] ADD CONSTRAINT CK_Template_Columns
CHECK (
    (template_id = 1 AND usp_1 IS NOT NULL AND icon_1_asset_id IS NOT NULL AND icon_2_asset_id IS NOT NULL AND heading IS NULL AND usp_2 IS NULL)
    OR (template_id = 2 AND heading IS NOT NULL AND icon_1_asset_id IS NOT NULL AND usp_1 IS NULL AND usp_2 IS NULL AND icon_2_asset_id IS NULL)
    OR (template_id = 3 AND usp_1 IS NOT NULL AND usp_2 IS NOT NULL AND heading IS NULL AND icon_asset_1_id IS NULL AND icon_2_asset_id IS NULL)
)

This CHECK CONSTRAINT enforces the columns that cannot be NULL and those that must be NULL based on the chosen template_id. An attempt to fill an invalid column, or leave a valid column blank, for a given template_id would cause the INSERT/UPDATE to fail.

And if you're concerned regarding storage, within SQL Server you have the option of defining SPARSE columns to help reduce storage space for mostly NULL columns. You can also leverage Data Compression and Filtered Indexes to reduce overall space consumption.

For example, a filtered index for each template_id value would allow you to only index the columns that are not null for that template, thereby reducing the number of rows and columns stored in an index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.