Creating a PostGresSQL Database and winding up with some unusual issues and I'm curious who else might have experience with this.
I'm building an inventory management system and we have three tables that I'm trying to figure out how to handle qw(customer country state). A country might have states, it might not. For a given customer's address, if their country does not have states/provinces (Monaco, perhaps?), what do I do? I can't insert a NULL value on a field with a foreign key constraint. I suppose I can create a blank field in the state table and reference that, but then I'm treating this field as "special" and I'm concerned that I'll need to hardcode that ID into my application. How is this usually handled where either one needs to have an FK constraint or NULL?
I've also realized that for a given customer, there's no way to stop the database from having a state of "California" and a country of "France." The interface doesn't allow this to occur, but that doesn't guarantee that future bugs won't occur. I suspect a mistake in my database design, but for the life of me, I can't figure out what I might have done wrong. Or it's possible that this is simply a situation that has to be handled on the application level