Surrogate vs. natural keys specific example

Question

After lots of reading, I decided to make surrogate keys for my tables. Now I have a dilemma on creating a 1:1 relationship on two tables, "Orders" and "Form_VSA_albums". 1:1 because one form can only have one order and one order can only have one form. Note, there CAN be orders with NO form but there CANNOT be a from with no order so the Order table is the parent table and it holds the PK.

When using Natural keys, I made the company's Order_id the PK of both tables and I was going to make this value a PROGRAM-GENERATED varchar(5) (so I don't have to worry about user error entries messing up the PK - a main issue when using natural keys). So each time an Order would need a Form, the ID of the new FORM table row would get the same ID of the ORDER row. No problems.

But now that I am switching to surrogate keys, the PK of both tables will be computer generated and I have no control of the values being generated when Order and Form rows are being created. So my question is, what can I do to make sure that when an Order needs to create a new Form, that the PK of the Form should be the SAME as the PK of the calling Order? If it's not the same it will violate the 1-1 relation so it will fail.

p.s. The company's internal order_id that I wanted to use as a natural key, I now moved to a new attribute called order_id - varchar(5)).

Answer 1

In a well-designed database, tables will have surrogate keys about half the time. However, don't fall into the trap of thinking that just because something works perfectly well in most general cases, it should be adopted in all cases. Examine what this is doing to your own design. You have a perfectly good design with the same key value being used to enforce a 1-1 relationship. But your dogmatic desire to force a unique surrogate key into every table means you must dismiss a perfectly fine method of enforcing the relationship. An effective alternate will not be easy and will inevitably force you to get much more involved in maintaining the integrity of the relationship, as you have already noticed, and the effects could very well cascade into other compromises in design.

The solution is simple: recognize that any rule or aspect of design, though it may be the perfect solution in most instances, cannot be the perfect solution in all instances. You currently have a design that is simple, elegant and does everything you need it to do. Stick to it.

And always keep more than one tool in your toolbox. Your designs will be better for it.

Answer 2

Form should be the SAME as the PK of the calling Order? If it's not the same it will violate the 1-1 relation so it will fail.

You wouldn't. Each table would have its own primary key value, and the Form would have a foreign key to the order table's primary key.

You would enforce the 1:1 relationship by adding a unique key to the foreign key column in the Form table.