Column partially determining accepted values of another column, should be somehow normalized?

Question

I have a table that describes a set of properties (yes it's metadata, in this case there's a valid reason to have it in the database); among other things I tell the type of the data that can be assigned to them (Type) and a default value. The valid values for the default value are thus restricted by the Type column, but the default value is genuinely an attribute of the table, they are not uniquely determined by the Type column.

I'm not sure though, should this be somehow normalized or is it right as it is?

Edit: here's an approximate description of the current structure of the relevant part of the database, as requested. Don't mind the Values table, that's a separate issue. Just consider that ValueType restricts the set of DefaultValue permitted values.

Also, consider this only as an example, I'm interested in the problem in general, the problem being namely columns that limit the set of valid values of another column but that don't determine its exact value, thus as far as I understand don't constitute multivalued dependencies (but, as an aside, it would be useful to have an integrity constraint that enforce the limit - I'm not sure that this issue can be separated from the former).

Properties (
ID int PRIMARY KEY
Name varchar(100)
ValueType int REFERENCES ValueTypes(ID)
DefaultValue int REFERENCES Values(ID)
)

ValueTypes (
ID int PRIMARY KEY
Name varchar(100)
...
)

Values (
ID int PRIMARY KEY
...
)

Answer 1

Below I will take this structure through the normalization rules up to 4NF.

I'm going to assume for a minute that you are using properties.ID as a surrogate key and that properties.Name is a valid candidate key. By this I mean that in practice you will only ever have 1 value for a given "Name", such as by creating a unique constraint.

• 1NF: Passes; all values are atomic
• 2NF: Passes; the candidate keys are {ID} and {Name}; neither of these are compound keys.
• 3NF: Passes; 3NF requires functional dependencies on non-prime attributes and there don't appear to be any; please see my explanation of 3NF for a similar question.
• BCNF: Passes; like 3NF, it requires functional dependencies on non-prime attributes and there aren't any.
• 4NF: I will explain 4NF in detail below.

The rule for 4NF is:

A Table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

For this table, your superkeys are {ID}, {Name}, {ID, Name}, {ID, ValueType}, {ID, DefaultValue}, {Name, ValueType}, {Name, DefaultValue}, {ID, Name, ValueType}, {ID, Name, DefaultValue}, and {ID, Name, ValueType, DefaultValue}.

ValueType and DefaultValue are multivalued dependencies on both ID and Name. However, because these are both superkeys, this table is still 4NF.

Therefore, your database schema is normalized up to 4NF.

Answer 2

Assessing which normal form a design is in requires you giving us the relation schema(s) and the applicable FDs.

Furthermore, don't make the mistake of thinking that "normalized" implies "right". Bypassing the engine's type system, usually in a misguided effort to minimise the number of tables, is usually nowhere near "right". Usually smacks way more of going for the completely wrong abstractions.

Answer 3

You might still keep the definition of columns that you need metadata in the table, but as far as "normalizing", is it possible to more accurately retrieve the column data from the system tables. The below example assumes tsql SQL Server 2005+. I think this would be a better way to retrieve both the metadata about the column as well as the default values.

SELECT OBJECT_NAME(c.[object_id]), c.name, c.system_type_id, dc.[definition] FROM sys.columns c
INNER JOIN sys.default_constraints dc ON c.default_object_id = dc.[object_id]
WHERE c.[object_id] = OBJECT_ID(N'dbo.TableName')
AND c.name = N'ColumnName'