Allowing null to a foreign key

Question

I have two existing tables called VehiclePlants and VehicleModels.

Right now, the VehicleModels are linked to a possible multiple of VehiclePlants

The customer wants this changed to be just a one-to-one relationship.

My question:

When I add this column VehiclePlantId to the VehicleModels, knowing that this has to be chosen in the application, should I allow it to be null?

And moreover, there are already records in the database for both VehiclePlants and VehicleModels. Should I mock a VehiclePlantId and put the column to not allow null instead?

Answer 1

• When you model conceptually, relationships that are mandatory on both ends are possible, but in the physical world of tables, they do not exist, becuase that would prevent insertions, since you cannot insert A without a corresponding B and viceversa. So in the RDBMS one of the ends of the relationship must be not-mandatory, i.e., allow null.
• IMO the relationshipo between VehiclePlants and VehicleModels must be M:M, meaning a join table with foreign keys pointing to both table must be created. Obviously, both FK must be mandatory.
• The absence of a relationship between a given row in VehiclePlants and a given row in VehicleModels is given by the absence of a row in the join table, not by any null value.

My recommendation is this:

• At first, to make the customer happy, model as if the relationship is 1:M but make the PK of the join table so that it's a de facto 1:1 relationship despite the join table:

Note that as the PK is just the FK that points to VehiclePlants, it will allow only one row per VehiclePlants making it a de facto 1:1 relationship.

• After your customer realizes that the relationship is actually M:M, either because you talk sense him into him/her, or by realizing that a 1:1 relationship is impossible because there is already M:M data in the tables, then change de PK to allow M:M:

Note that now the PK is the composite of both FKs, making the relationship M:M. Obviosly, like every M:M relationship, it's optional in the phusycal RDBMS, meaning that you can insert into VehiclePlants and into VehicleModels without a corresponding row in the join table

Also:

Consider adding time dimension to it.

Answer 2

IMHO get your client to rethink this through before it's too late. A vehicle plant might assemble more than one model, and a model might be assembled in more than one plant. And that doesn't even touch the surface of where parts might be produced.

Without more context, the only sensible long-term option seems to be an n-n relationship between the models and plants tables. You'd model that with some kind of model2plant table with an ID tuple serving as a primary key - in which case null basically cannot be an option.

That n-n relationship related table could also hold extra information if relevant - e.g. a model might be produced in a plant from a start to end date, and yet again later on, or the production capacity might vary over time. For all you know they might want to reconstruct historical information at some point, and you'll want to anticipate that in your design. Look into slowly changing dimensions while you're at it, to get insights on how to model the type of stuff that slowly changes over time.

If your client insists on having a 1-1 relationship, note that you may then just as well merge your two tables, and get rid of your null related question altogether while you're at it. But stress that this will be simplistic and blow up in their face the moment they've a model or a plant that doesn't "fit".

If it's a 0-1 or 1-n/0-n relationship, which is what your question seems to imply, then by all means allow null for practical reasons even if you're not allowing it in the interface today: Murphy's law applies here as always - the question is not if you'll ever need to enter a model whose assembly plant is unknown, but when.

Answer 3

If you have rows in the table then you will need to let the column allow null. FK can be null.

After you get the column populated then depends. Is the plant always going to be known when model is created (inserted)?

Has to be chosen in the application, should I allow it to be null? Does not mean it has to be enforced in the DB. The application developers may want to insert a blank record. And enforce the requirement at the application level.

Answer 4

My approach would depend on just one thing: are models with no relation to a plant going to be a thing somewhere on the future?? if so I'd go with null values all the way.

In the other hand if it is not the case, as I see it, instead of allowing null values I'd join the intermediate table with vehicle models when relation is 'de facto' one to many, for existing many to many relationships and you can't get any help to weed out the undesired relations I'd stick with the mock as way to identify the records that need updating.