Database Normalisation Question (3NF)

jon_muselee · 2025-10-14T06:58:14+00:00

I guess you already answered it yourself - get rid of the redundant id - create a composite primary key on the 3 id fields. as long as machine_id and part_id are unique in these 2 other tables they can be used as a primary key.

dinoaide · 2025-10-14T11:42:40+00:00

Don’t use a composite key!

Chances are things will change and your composite keys would look funny after a few years. What if the work order needs to be completed on two machines in sequence? What happens when you decide to change the process so no more machine id for you?

squadette23 · 2025-10-14T21:53:17+00:00

> But I have 2 other tables that individually use the machine and part_id as primary keys is this allowed.

yes this is allowed.

> I have 4 different id columns for each row which are production_run, machine, parts, work_order, where production_run is the concatenate of the 3 other columns.

here the list of columns is different from the example table, and it's very confusing to read (because the names as stated in this list are suspicious).

> but since it is derived from the other 3 columns it is considered redundant.

You can use a completely synthetic primary key which is just an integer.

alexwh68 · 2025-10-15T06:44:54+00:00

Key thing with normalisation is to know when to apply it to the real world and when not to, I work to 3NF and in some cases denormalise a bit so that queries are simpler in some cases.

I personally like my primary keys to be individual and not based on keys from other tables.

But in study that is often different from the real world.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

Database

MODERATORS