Designing a table with multiple column types and different foreign keys 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.
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.