Derived Key, Natural Key and 3rd NF

Let’s say I have a many-to-many relationship like this…

ORDER --||-----|<-- ORDER_DETAILS -->|--------||-- PRODUCTS

And in my junction table, I have these fields…

ORDER_DETAILS table
- id
- order_no
- product_no
- quantity
- order_price
and so on...

Questions:

1.) Does having both a Derived Key (“id”) and Natural Keys (“order_no”, “product_no”) invalidate 3NF?

2.) If there was only a Natural Key, does having a Composite Primary Key invalidate 3NF?

3.) Is there anything that would prevent this table from being in 3NF?

Sincerely,

Debbie

3NF is best described as saying everything in the table depends on the primary key, the whole primary key and nothing but the key.

Another way to look at is to say there should be no redundant cells in your table.

Sent from my XT316 using Tapatalk 2

Yes - you wouldn’t have the derived key in your 3NF logical design - you’d add it to the physical design if there are reasons for not using the natural key as the primary index.

No.

Yes, to convert that to 3NF you’d delete the derived keys. Derived keys only exist in 3NF when there is no combination of fields that could form a natural key.

Your logical database design should be fully normalised but the actual physical design that you implement may undo normalisations for a variety of reasons (eg, the natural key being too long or involving too many fields, or the database accesses being 99% writing and almost no reading of data)

I don’t think I agree. There is certainly a reason not to have wide tables but I have not found a good reason yet to have duplicated data in tables (which is basically what non-3NF causes or allows to happen).

Also having alternate or secondary keys in a table doesn’t, in itself, break 3NF.

Sent from my XT316 using Tapatalk 2

1.) Does having both a Derived Key (“id”) and Natural Keys (“order_no”, “product_no”) invalidate 3NF?

Not really, because 3NF is not about how you create the PK, it is about how the rest of the data depends on the PK.
Or as Codd put it: “Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key.”

Your surrogate key would be generated to be unique in the table so this table itself makes the id dependant on the candidate key of order_no+product_no.

In fact, your id would be an orderline_no, which is something you want as way for your customers to reference in their order.

2.) If there was only a Natural Key, does having a Composite Primary Key invalidate 3NF?

Composite keys are just keys, they change nothing about the first three NFs.

3.) Is there anything that would prevent this table from being in 3NF?

Not as long as you put your constraints in place properly.

In fact, without the id column joining requires fun queries like:

SELECT * FROM ORDER_DETAILS INNER JOIN othertable ON (ORDER_DETAILS.order_no, ORDER_DETAILS.product_no) = (othertable.product_no, othertable.product_no);

As a sidenote to make your life easier:
Don’t use the nondescriptive name “id” for the surrogate key. The reason is that many tables will get this ‘id’ column and when you join them, the result will get several ‘id’ columns that you cannot tell apart in your application. You can work around that by aliassing the columns in the queries (SELECT orderdetails.id AS orderdetail_id), but you can imagine how that’s going to work; you’ll forget aliases, you’ll alias the wrong id’s, you’ll use different aliasses, poop will fly. But the simplest argument is that if you are going to provide a different name for the id colujmns in just about every query, why not just name the column so that you don’t have to alias it in the first place?

you make a great case, but i’m gonna go ahead and disagree anyway

use “id” for the PK when it’s an obvious surrogate key (like an auto_increment)

use “entity_id” for every FK that references the PK

that way, in your queries, you can immediately discern the one-to-many relationships involved

nothing worse than trying to understand a query and every key is named foo_id and bar_id and qux_id

trust me, a column alias is the least of your worries, clear and unambiguous relationships are worth it

trust me, a column alias is the least of your worries, clear and unambiguous relationships are worth it

In my experience nondescript names like ‘id’ and aliases provide 90% of the ambiguity.

But we agree that you should use names that make clear what you are referering to. How you do that is a matter of personal preference.

by this comment, i’m not sure you understood my point

“id” used only for PK, and “foo_id” used only for FK

that’s clearly unambiguous

“id” used only for PK, and “foo_id” used only for FK
that’s clearly unambiguous

Untill you do a join, when you’ll have two “id” columns and where you cannot alias foo.id to foo_id anymore because that’s already a real column in the other table.

Yes, I am pedantic

Ideally, and I admit I rarely do this, you’d prefix the PK and FK with pk_ and fk_ so you’d get pk_foo_id and fk_foo_id. Then when you join foo to bar you have pk_foo_id, pk_bar_id and fk_bar_id. The only occasion where you can get ambiguity then is if you join multiple tables that have an FK to the PK, and you’re doing an OUTER JOIN. Yopu could solve that by mentioning the relation in the FK, but that’s taking it very very far indeed: fk_bar_foo_id, which just looks silly.

But, this is above all a matter of taste and what you’re used to. I put this in here mainly to show that there is no generally accepted “best practice” for this, each method has it’s pro’s and con’s.

you wouldn’t need to see both foo.id and bar.foo_id, because in a join they’d be equal!!

unless of course you’re using the dreaded, evil “select star” to bring in more columns than you really need

We’re getting a little distracted by this discussion

They wont be equal in a LEFT JOIN or in any other join that does not require that foo.id=bar.foo_id.
In fact, they will only be equal if you do a straight INNER JOIN on foo.id=bar.foo_id. Which are the simplest queries where you typically don’t get confused about where the fields are coming from.

unless of course you’re using the dreaded, evil “select star” to bring in more columns than you really need

You do have a colourful way of saying “it’s usually not the best choice”.

Yes, Vinny, you are being very distracting… (This thread isn’t about how you like to label fields.)

Debbie

you started it… “As a sidenote to make your life easier:”

Yes, Vinny, you are being very distracting… (This thread isn’t about how you like to label fields.)

I’m so sorry i tried to help.

you started it… “As a sidenote to make your life easier:”

I just gave some extra advice, but apparently contradicting the local experts is “not done” here, as it appears to be on most forums.

Well nevermind, I’ll leave you to handle it.

So having a Derived Key (“id”) and a Natural Composite Key (“order_no” + “product_no”) in the Physical Design breaks 3NF? :-/

I used to know this stuff cold, but when I went to refresh my memory last night, I found link after link of confusing and often very theoretical descriptions.

From what I was able to gather, if you have a Composite PK, you need to make sure that every non-Key field is dependent on the entire Composite PK.

Is that correct?

And if it is, and I had to defend the fields in my OP, then can you help me figure out how to do that?

I guess my response would be that “quantity” is a function of the Order and Product together. Likewise, “order_price” is a function of the Product at the time of the Order.

Is that true for the Physical Model as well?

Sincerely,

Debbie

Start another thread on what you think about Field Naming and I’ll tell you my thoughts there.

My goal here is to make sure my rusty memory of 3NF is correct.

Sincerely,

Debbie

Ok, I see where I went wrong.

1NF is that table data should be atomic (MyBB forum software heartily breaks this rule.)

2NF is every table field depends on the key (but not that you can’t have secondary keys or composite keys.) This was my error in my first post to this thread.

3NF is that no table field should depend on only part of a composite key because that would cause duplicates.

1NF, 2NF are the obvious cases. 3NF is about not duplicating table data in order to satisfy a bad table design.

There is nothing in 3NF to say you can’t or shouldn’t have secondary keys.

PS: I’m no resident sitepoint expert. I doubt anyone is censured for disagreeing with me. Maybe if we learn to leave our egos at the front entrance we might enjoy the conversation more. After all its only a forum and why do we want to let the odd spate of immaturity ruin our day. Time to get a bit of perspective folks.

Sent from my XT316 using Tapatalk 2

where did this come from? i loved our interchange, and i hope to have more

there are no normal forms in physical design – just tables

correct

see? this stuff is easy

So is the table in my OP in 3rd NF (if the “id” field was removed)?

Here it is again…

ORDER_DETAILS table
- order_no (PK1)
- product_no (PK2)
- quantity
- order_price

Sincerely,

Debbie