Skip to main content
added 38 characters in body
Source Link
Neil McGuigan
  • 8.6k
  • 5
  • 40
  • 56

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'.

Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship.

I would do it like this (using STI here, but break it out to CTI if you like):

PARTIES
id
type (org or individual)
name

ADDRESSES
id
type
address_info...

PARTY_ADDRESSES
party_id
address_id
role (ex 'sales', 'service', 'mobile', 'work', ...)
priority

Your validation rule would be that a party must have an address of type 'email' whose priority is higher than any other of their addresses. Business rules are best enforced via triggerstriggers any applicable logic and not your data model.

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'.

Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship.

I would do it like this (using STI here, but break it out to CTI if you like):

PARTIES
id
type (org or individual)
name

ADDRESSES
id
type
address_info...

PARTY_ADDRESSES
party_id
address_id
role (ex 'sales', 'service', 'mobile', 'work', ...)
priority

Your validation rule would be that a party must have an address of type 'email' whose priority is higher than any other of their addresses. Business rules are best enforced via triggers and not your data model.

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'.

Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship.

I would do it like this (using STI here, but break it out to CTI if you like):

PARTIES
id
type (org or individual)
name

ADDRESSES
id
type
address_info...

PARTY_ADDRESSES
party_id
address_id
role (ex 'sales', 'service', 'mobile', 'work', ...)
priority

Your validation rule would be that a party must have an address of type 'email' whose priority is higher than any other of their addresses. Business rules are best enforced via triggers any applicable logic and not your data model.

Source Link
Neil McGuigan
  • 8.6k
  • 5
  • 40
  • 56

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'.

Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship.

I would do it like this (using STI here, but break it out to CTI if you like):

PARTIES
id
type (org or individual)
name

ADDRESSES
id
type
address_info...

PARTY_ADDRESSES
party_id
address_id
role (ex 'sales', 'service', 'mobile', 'work', ...)
priority

Your validation rule would be that a party must have an address of type 'email' whose priority is higher than any other of their addresses. Business rules are best enforced via triggers and not your data model.