In a recent project I was asked to implement an events system. An Event
had to have a Location
which was originally specced out as simply a physical location with some optional extra notes. Then the spec changed (as they have a habit of doing) and we needed to have "online" events too. These would not have a physical address but would still need notes on how to attend (e.g. the URL, joining instructions).
We decided to adapt the existing Location table by adding an IsOnline
field and it wound up looking like this:
+-----------------+ +---------------+
| Event | | Location |
+-----------------+ +---------------+
| Id | .--+ Id |
| Name | | | Name |
| Summary | | | Address |
| Date | | | Postcode |
| Capacity | | | IsOnline |
| LocationId +--' | Notes |
+-----------------+ +---------------+
An example of a physical and an online entry in the Location table look like this:
+----+----------------+--------------------------------------+----------+----------+---------------------------------------+
| Id | Name | Address | Postcode | IsOnline | Notes |
+----+----------------+--------------------------------------+----------+----------+---------------------------------------+
| 1 | Physical Event | 10 Downing Street, London | SW1A 2AA | 0 | Ask the policeman to let you in |
| 2 | Online Event | http://programmers.stackexchange.com | null | 1 | You will need a stackexchange account |
+----+----------------+--------------------------------------+----------+----------+---------------------------------------+
It works (currently) for our simple use case but it is clearly a bit of a hack and it got me thinking - what would be the correct, normalized way to model this kind of relationship (Where an entity must have an A or a B but not both)?