1

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?

  • What do you got now? a many to many relationship with a separate table to join them? Based on the names I'm also guessing that what you actually want is a one to many relationship, as in a model can relate to just one plant but a plant can relate to many models. Is this correct? – Zalomon Aug 22 '16 at 6:55
  • Yes this is correct, I mixed it up. Sorry about that. Right now I have a many-to-many with an intermediate table which is going to be removed with the change I'm doing right now – Tikkes Aug 22 '16 at 7:06
  • @Tikkes: Are you asking if you should allow the column to be NULL in the database even though the application requires it to have a value? In that case the answer is no, you should not allow it to be NULL. – JacquesB Aug 22 '16 at 7:17
  • @JacquesB The table has rows. You cannot create a column with not null in that situation. – paparazzo Aug 22 '16 at 9:30
  • When was the last time a customer used words such as "one-to-one" and actually meant it? Be nice to them, but keep them away from your garden. Bring the subject of the color of that button on the table - should it be light or dark gray? Let them take that decision. They'll be happy. (side note: if you don't have such a button, create one, or discuss the wording of an obscure dialog box they'll never see or read) – user44761 Aug 22 '16 at 13:10
2
  • 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:

enter image description here

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:

enter image description here

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.

enter image description here

  • This is a great answer yet I have one more question concerning the time constraint. If, at any time, I want to re-enable a plant/model combination but on a different timeframe. How would you go about that considering that I would like to keep a history for example? – Tikkes Aug 22 '16 at 13:11
  • 1
    @Tikkes I changed the model so FROM_DATE is part of the PK to allow same plant/model combinations but on a different timeframe. You have to implement a sanity check trigger of method to avoid date overlaps. Time overlaps cannot be avoided by design alone. – Tulains Córdova Aug 22 '16 at 13:24
  • +1 for keeping as much of the current schema as possible. – JeffO Aug 22 '16 at 20:30
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.

  • 1
    It is not 1-1. Clearly there is not 1 plant per car. It is 1 to many. – paparazzo Aug 22 '16 at 10:36
1

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.

  • Down vote what is the problem? You must allow null to create a column is the table currently has rows (as stated in the question). – paparazzo Aug 22 '16 at 9:23
  • Don't know who downvoted... seems off. I indeed have chosen not to enforce it through the database but through the application instead – Tikkes Aug 22 '16 at 10:51
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.