I believe this really is pretty fundamental, however i am a new comer to this. I'm attempting to normalize a table. Can a relation be 3NF with multiple foreign secrets? Is 3NF:

  • TALENTPAYMENT (PAYMENTNUMBER, date, subtotal, tax, total, talentid, agentnumber)

or wouldn't it have to be divided more to:

  • TALENTPAYMENT (PAYMENTNUMBER, date, subtotal, tax, total)

  • TALENTPAYMENTID (PAYMENTNUMBER, talentid)

  • TALENTAGENT (TALENTID, agentnumber)

It isn't 3NF, although not due to your foreign secrets. You've some functional dependencies which the left side isn't a candidate key:

subtotal,tax   -> total
subtotal,total -> tax
tax,total -> subtotal

The formula for reducing to 3NF would tell split your schema into:

PAYMENTNUMBER | date | subtotal | tax | talentid | agentnumber

and

subtotal | tax | total

After which, presuming "talentid -> agentnumber" or even the reverse aren't dependencies, the schema is within 3NF, however your (subtotal, tax, total) table is essentially useless since storing the 3 an apparent redunancy. It might be better simply to use:

PAYMENTNUMBER | date | subtotal | tax | talentid | agentnumber

And never store total whatsoever. If you would like it inside a query you can easily SELECT (subtotal+tax) as total presuming subtotal and tax are generally number types.

It isn't in 3NF anyway since total = subtotal + tax (or the other way round I am bad in the distinction between total and subtotal).

To stay in 3NF, you mustn't have transitive functional dependencies with non-prime characteristics. Get rid of the derived attribute (total or subtotal) but your option would be A OK.

For your matter, the initial may be OK, as well as the problem I mention.

Your three part schema indicates that there can be a practical dependency talentidagentnumber, by which situation your relation can't be in BCNF (3NF) because talendtid isn't among the secrets for that relation.

However, talent/agent associations are usually fragile, so it wouldn't come as a surprise to locate the same talent is symbolized by different agents at different occasions. For the reason that situation, you most likely have to keep your 'agent from the talent during the time of the payment' on record, as well as your original schema is preferable to the 3-the beginning (because alterations in the information from the three-part plan changes historic records).

I believe the majority of the other solutions tend to be more oriented toward your example than toward your question.

To talk straight to the question, yes, a relation could be in 3NF even when it's multiple foreign secrets. The important thing (cough) reason for 3NF would be to remove transitive dependencies, to not reduce the amount of foreign secrets.

To place it one other way, there is no such factor as "I've a lot of foreign secrets" normal form.