CODEWITHSUNDEEP
sqlsql-serverforeign-keys
LeopardSkinPillBoxHat
LeopardSkinPillBoxHat

Reputation: 29421

How can I store metadata about columns in my database in a normalized fashion?

Let's say that I have a simple table that looks like this:

CREATE TABLE [dbo].[Product]
(
    ProductID INT -- PK
   ,ProductName NVARCHAR(100)
   ,ProductDescription NVARCHAR(MAX)
   ,ProductOwner NVARCHAR(50)
   -- Extra fields <snip>
)

I want to be able to define flags for each column in [dbo].[Product] and store these in another table.

I can introduce a FlagType table to define the possible set of flags:

CREATE TABLE [dbo].[FlagType]
(
    FlagTypeID INT -- PK
   ,FlagTypeName NVARCHAR(50)
)

And then a Flag table which provides the mapping between columns, flag types and values:

CREATE TABLE [dbo].[Flag]
(
    FlagID INT -- PK
   ,FlagTypeID INT -- FK => [dbo].[FlagType] (FlagTypeID)
   ,FlagColumn ??? -- FK
   ,FlagValue BIT
)

My question is, how can I define the FK relationship for the FlagColumn column above, such that it must always reference a valid column in the database?

My initial thought was to make it a foreign key to INFORMATION_SCHEMA.COLUMNS but this table doesn't appear to have a primary key that I can reference.

Upvotes: 2

Views: 252

Answers (1)

gbn
gbn

Reputation: 432261

A database schema and data shouldn't really not know about itself
That is, your model and implementation shouldn't need to reference it's own metadata.

It makes no sense if you think about it

A column in a row stores a single, indivisible bit of information that has some business meaning for some unique entity (row). The app knows this. Adding "flags" for a column means you are storing information about the entire column that the app should already know.

However, there is a way if you insist: extended properties.

This is how you attach metadata to columns. But it isn't relationally consistent

CREATE TABLE dbo.foo (bar int);
GO
EXEC sys.sp_addextendedproperty 'Display Label', 'Barbarella', 'SCHEMA', 'dbo', 'TABLE', 'foo', 'COLUMN', 'bar'
EXEC sys.sp_addextendedproperty 'Display Description', '60s scifi movie starring Jane Fonda', 'SCHEMA', 'dbo', 'TABLE', 'foo', 'COLUMN', 'bar'
GO
SELECT EP.name, EP.value FROM sys.extended_properties EP
       WHERE class=1 AND EP.major_id= OBJECT_ID('dbo.foo') AND 
                EP.minor_id = COLUMNPROPERTY(OBJECT_ID('dbo.foo'), 'bar', 'ColumnID')
GO

Upvotes: 1

Related Questions