thanasissdr thanasissdr - 1 month ago 8
SQL Question

Third (?) normal form of a relation

Let's say I have a website, where any person can buy and sell items and 2 registered users can send messages to each other regarding a product. One of the relations in my database is:

Message
--------
idMessage (PK)
Sender
Recipient
idObject
Subject


Of course one of the sender or the recipient should be either the seller or the buyer of a product. My question is if this relation is in 3rd normal form. Of course it is in 2nd normal form, because every non - key column is fully functionally dependent on the primary key, but I think it is not in 3rd normal form since there is functional dependency between the
idObject
and either the
Sender
or the
Recipient
of a message. What I want to say is that the
idObject
on which there is a message sent determines either the
Sender
or the
Recipient
, right?

Example:

There is an item with
idObject
#1234
. There are 2 registered users that talk about this item. Let's say that
userA
who is a potential buyer asks
userB
who is the seller what the size of the item
#1234
. This
idMessage
is
1
. After a while,
userB
replies to a
userC
about this specific item. This
idMessage
is
2
. Apparently, this 'idObject' in the
Message
relation determines the
recipient
of the message (who is the seller of this specific item) in the first case and the 'idObject' determines the sender of the message (who is the seller of this specific item).

idMessage| Sender| Recipient | idObject| Subject
________________________________________________
1 | userA | userB | #1234 | size
2 | userB | userC | #1234 | discount

Answer

So, the question should be, is there a transitive functional dependency? if the answer is yes, then this is not a 3NF.

Not sure if I completely understand the idObject attribute, but if [idMessage] determines [idObject] via [Recipient] or [Sender] then we have a transitive functional dependency hence this would not be a 3NF. If [idObject] is determined by the [idMessage] attribute then all non-key attributes are fully functional dependent only on the primary key [idMessage] and this would be a 3NF.

You may want to add some explanation on What is idObject ? Hope this helps.

Comments