0

I have an association called Includes between Parkings and Cars.

This is a one-to-many association that is partial both sides, because a parking can contain zero or more cars and a car can be contained by zero or one parking.

I'd add a FOREIGN KEY related to a parking in the Cars table, but it's not true that a car is always inside a parking.

How can this situation be managed?

matt
  • 15
  • 3
  • 1
    Leave the FK `NULL` when a car doesn't belong to a parking. – sticky bit Jul 06 '20 at 09:56
  • What about the guy who parks so sloppily that he takes up two spaces? – Rick James Jul 06 '20 at 17:24
  • This is a FAQ: there's about a gazillion texts how to deal with 'missing information'. I said 'missing' because that's the usual term, but it's misleading: an empty `lot` does not mean any `car` is missing; a `car` not currently in a `lot` does not mean that car is missing from any particular `lot`. So in this case I'd rather say 'not applicable' or 'not given'; in other examples you might say 'unknown'. – AntC Jul 06 '20 at 21:09
  • AntC and I seem to have a different understanding of the model. Is this about cars and parking lots? Give a short dataset that exemplifies at least the valid cases of 1:many, missing, zero, zero on the other side, etc. – Rick James Jul 06 '20 at 21:12
  • I'm going to resist the invitation in @RickJames comment to his answer: StackOverflow does not need more answers to what is a very common 'problem'. Not a problem: a entirely everyday set of database design requirements. Search SO for 'missing information' and/or 'avoid NULLS'. Here's some starters, which include more links https://stackoverflow.com/questions/21208459/how-should-missing-information-in-relational-databases-be-modeled-properly https://stackoverflow.com/questions/3079885/options-for-eliminating-nullable-columns-from-a-db-model-in-order-to-avoid-sql – AntC Jul 07 '20 at 05:27
  • https://stackoverflow.com/questions/4336687/how-can-i-avoid-nulls-in-my-database-while-also-representing-missing-data – AntC Jul 07 '20 at 05:27

1 Answers1

-1

So, the car has a column saying where it is currently parked. Or NULL if it is roaming the roads?

And the parking lot (car park) can have lots of cars? But it needs no column(s) to indicate such. The information can be had by a JOIN with the other table.

A FOREIGN KEY is 2 things:

  • An integrity check. You wan't want a car parked in a nonexistent lot.
  • An INDEX to make the above JOIN more efficient.

We can't judge the indexes needed without seeing the SELECTs (and other queries).

To find all the cars in one lot:

SELECT *
    FROM cars
    WHERE lot = 123

That needs INDEX(lot) (or the FK) in cars.

To find where one car is:

SELECT lot FROM cars WHERE id = 456;

Presumable cars has PRIMARY KEY(id).

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • There's a need for two integrity checks: no `car` in a nonexistent `lot`, as you have; no more than one `car` in any `lot`, that needs a `UNIQUE` constraint on the `cars.lot` column. `SELECT * FROM cars WHERE lot = 123` should return at most one row: that arises from the structure of the business situation, it's nothing to do with efficiency of indexing. – AntC Jul 06 '20 at 21:00
  • In general, a `FOREIGN KEY` that's nullable is problematic (any `JOIN` from Null to the `REFERENCE`d table will fail); a `UNIQUE` constraint on a nullable column is problematic (if there's two Nulls, do they count as unique?). So I would not do this with an extra (nullable) column on `car`; I would make a separate linking table. – AntC Jul 06 '20 at 21:04
  • @AntC - Spell all that out, and more, in an Answer. – Rick James Jul 06 '20 at 21:08