0

One-to-one relationships are sometimes used to split a table into multiple parts, and everything I have read says to have the "extra data" (address) use the primary key of the "main entity" (thing_with_address) along with a foreign key back to it.

thing_with_address
- id (PK)
- other_data

address (value object and not shared)
- id (PK, FK to thing_with_address.id)
- street
- city
- state_id (FK to state.id)

But what if I didn't have one thing_with_address, but several?

I obviously don't want to add multiple columns in address to reference each thing_with_address. Another option is to create a separate address table for each thing table, however, doing so is not desired.

One option is to make all the thing_with_address's supertype/subtype entities and then have address reference the common supertype. My only real concerns with this approach are adding address to an existing thing without an address will be a pain, and inheritance gets a little messing if there is more than one fork.

thing_with_address_super
- id (PK)
- discriminator
- other_common_data

thing_with_address_1
- id (PK, FK to thing_with_address_super.id)
- other_unique_data

thing_with_address_2
- id (PK, FK to thing_with_address_super.id)
- other_unique_data

address
- id (PK, FK to thing_with_address_super.id)
- street
- city
- state_id (FK to state.id)

Alternatively, one could supertype/subtype the value object. More tables but less of an issue if adding an existing thing without an address and less issues with inheritance.

thing_with_address_1
- id (PK)
- other_common_data

thing_with_address_2
- id (PK)
- other_common_data

address_super
- id (PK)
- street
- city
- state_id (FK to state.id)

address_1
- id (PK, FK to address_super.id)
- thing_with_address_1_id (FK to thing_with_address_1.id)

address_2
- id (PK, FK to address_super.id)
- thing_with_address_2_id (FK to thing_with_address_2.id)

Another option is to move the FK to the main table. My main concern with this approach is I've never seen it done and there probably is a good reason why. Expect it has to do with multiple things using the same address, and maybe more.

thing_with_address_1
- id (PK)
- address_id (FK to address.id)
- other_data

thing_with_address_2
- id (PK)
- address_id (FK to address.id)
- other_data

address
- id (PK)
- street
- city
- state_id (FK to state.id)

How should this be modelled when the entity address could and could not be NULL?

user1032531
  • 24,767
  • 68
  • 217
  • 387

1 Answers1

0

It doesn't really matter if it's a one-to-many or a one-to-one relationship, the main table should still reference the address table, since the "thing_with_address" should reference the record containing it's address, not the other way around.

If it is truly a one-to-one relationship, I suppose you could even put a unique constraint on the FK side of the relationship, but I'm not sure that's how I'd go about it.

Morten Ankerstjerne
  • 51
  • 3
  • Thanks for your response Morten. Most of the solutions I've seen have it backwards to what you show. At least for 1 to 0.1 that is. https://stackoverflow.com/questions/10292355/how-to-create-a-real-one-to-one-relationship-in-sql-server doesn't really address my question but is a good post which discusses 1 to 0.1 versus 1 to 1. – user1032531 Apr 20 '22 at 13:28
  • 1
    To be honest I've _never_ seen it done the other way around (for things like address at least). If your entity has an address, that entity would reference the address table, not the other way around. Can you provide more information about the data model and why you need one-to-one relationships in the first place? – Morten Ankerstjerne Apr 20 '22 at 16:01
  • Another way to think about it is this: The entity "has" an address, the address does not "have" entity, it just exists. It might be that you need something like your thing_with_address_super, with that entity pointing to the separate thing_with_address entities, as well as the address entity – Morten Ankerstjerne Apr 20 '22 at 16:04