4

Like this:

CREATE TABLE persons(
    id serial8 NOT NULL PRIMARY KEY,
    name varchar,
    -- A lot of other fields
    date_of_birth timestamp with time zone,
    date_of_birth_precision varchar(16),
    CHECK (date_of_birth_precision IN ('Years','Months','Days','Hours','Minutes'))
);

date_of_birth_precision describes the precision of date_of_birth.

I wonder if it violates this rule (because I don't fully understand the rule):

Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every superkey of R.

3 Answers 3

2

I disagree with the other answers on this one and do not believe this violates 3NF. This is because date_of_birth_precision does not imply date_of_birth nor does date_of_birth imply date_of_birth_precision.

It's important to note the definition of a functional dependency:

Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value is associated with precisely one Y value; R is then said to satisfy the functional dependency X → Y. Equivalently, the projection is a function, i.e. Y is a function of X.1 In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x. Customarily X is called the determinant set and Y the dependent set.

So let's ask ourselves, for a given value of date_of_birth_precision, does that imply a date_of_birth? Certainly not. For a given value of date_of_birth, does that imply a date_of_birth_precision? It doesn't, otherwise you wouldn't even be asking this question. Since neither implies the other, there is no functional dependency between the two. Therefore, this does not break 3NF because 3NF only qualifies the rules for functional dependencies; it says nothing about attributes which are not functionally dependent on each other.

I find the alternate definition of 3NF much easier to comprehend, personally:

A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

  • X contains A (that is, X → A is trivial functional dependency), or
  • X is a superkey, or
  • Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each column in A-X is contained in some candidate key)
0

That's true, date_of_birth_precision does describe date_of_birth more than it does the persons entity. But from an overall design standpoint, I would leave it just like you have it, and here is why-

In following the rules of Normalization, you could reason that you should create a date_of_birth table to hold date_of_birth_id, date_of_birth, and date_of_birth_precision. Then you would include a date_of_birth_id on the persons table, with a foreign key referencing a date_of_birth table.

That path would make sense, until you analyze the data and realized that almost every row would be unique. It stands to reason that very few persons would share the same date_of_birth, so referencing it doesn't make a whole lot of sense, in that it will not do much to reduce redundancy.

You could also ask yourself about how that data will be used when queried. Will you want to see the date_of_birth_precision when the persons is queried? Or do you process date_of_birth separately? Again, your use cases may suggest that it is advantageous to keep it together, and save yourself from doing another query or JOIN.

Normalize where it makes sense for your application, not for the sake of Normalization itself.

0

My first thought was to create columns for each part of a date DOBYear, DOBMonth, DOBDay, and so on, where if the date of birth is only known to the year then month and day and so on contain a null marker. Yes, I know this could be considered a violation of 1NF.

A bit of playing around convinces me that it causes the constraints to make sure no-one put a null marker in DOBMonth and a value in DOBDay get a bit insane.

After a bit of thought, I'd go with what you have. I wouldn't care too much about validation of the data stored, I would limit the data returned by means of a case expression, something like the following (typed off the top of my head so untested).

select
    case date_of_birth_precision
        when 'years' then datename( year, date_of_birth )
        when 'months' then datename( month, date_of_birth ) + ", " + datename( year, date_of_birth )
        when 'days' then datename( month, date_of_birth ) + " " + datename( month, date_of_birth ) + ", " + datename( year, date_of_birth )
        …
    end as known_date_of_birth

Thus if the precision is "years" then the actual date of birth is "1985". If the precision is "months" then "March, 1985". If "days" then "25 March, 1985". And so on.

Something to consider is whether it is an error to have a precision column of "months" with higher precision actually specified? That is, is ("1965.04.25 5:43:28", "months") valid or does it have to be entered as ("1965.04.01 00:00:00", "months")? If the first answer is invalid then the check constraints get a bit lengthy.

As an aside, if this particular situation happened in many of the entities in the system and was used in a large number of select statements then I would consider creating a user-defined type (if using SQL server then it would be a CLR user-defined type) to avoid having to enter the same case expression over and over.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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