CODEWITHSUNDEEP
databasedatabase-design
Leo
Leo

Reputation: 313

Database Design - transitive dependency

Here I have a table like this:

enter image description here

This is a simplified scenario. Assume one customer has a service requirement or not. In this requirement table, isServiced is a binary value, if it is true, we need to let the customer choose one service provider company from provider company table(a lookup table) if it is false, it means the customer doesn't want to have a service, so just leave ServiceCompany as null.

My question is does this violate 3rd normal form. Whether it has a transitive dependency problem, which is service company depends on isServiced depends on requirement_id.

If it has the problem, how could I solve it?

Upvotes: 0

Views: 184

Answers (3)

reaanb
reaanb

Reputation: 10065

If NULL is considered a value, then there's a transitive dependency as @WalterMitty pointed out.

However, if NULL is not a value then it's a physical symbol representing metadata, and has no business in a logical relation, in which case the table actually represents two base relations:

(Requirement_id, FALSE)
(Requirement_id, TRUE, ServiceCompany_id)

I'd say both are in 3NF, the constant notwithstanding, but I'd be hesitant to say the same of a table that represents different relations.

Note that isServiced is redundant - it can be derived from the value or lack thereof of ServiceCompany_id. So, that leaves us with:

(Requirement_id)
(Requirement_id, ServiceCompany_id)

Unless you have other data dependent on Requirement_id, you could consider only recording rows for customers who want a service, in which case you'd be safely in 3NF territory.

Upvotes: 3

Walter Mitty
Walter Mitty

Reputation: 18940

The value of isServiced is determined by ServiceCompanyId. If ServiceCompanyId is NULL then isServiced is FALSE. If ServiceCompanyId is anything but NULL, then isServiced is TRUE.

This dependency is backwards from the one you raised in the question. But it's still a transitive dependency.

However, in all the cases I've seen of departure from 3NF, they've never dealt with dependency on whether or not another value is NULL. If it works the way I think it does, then Requirement is not in 3NF.

Upvotes: 1

Renzo
Renzo

Reputation: 27424

The relation is in Third Normal Form, since the value of ServiceCompany_id is not uniquely determined by isServiced.

In fact a functional dependency A → B means that if two or more rows have the same value of A, then they must have the same value of B (but this is not the case here, since when isServiced is true, you have different values for ServiceCompany_id).

So, you don't need to modify the relations.

Upvotes: 0

Related Questions