1

Hi I want to ask about I have this DBD for my CRM I am trying to have the addresss in one table(customer and Employee Address) and both customerID and Employee ID are guid I have in address table FK is associate_ID to store the id (customer.ID or Employee.ID) just want to ask if is ok to do it like that ?enter image description here

1 Answer 1

1

A customer is a role played by a Party, which is an Individual or Organization (or Automated Agent).

Same with employee.

A party can have zero or more addresses, parties can share the same address, and addresses can exist without parties.

Your data model should look like this:

PARTIES -< ROLES

PARTIES -< PARTY_ADDRESSES >- ADDRESSES

create table parties (
  party_id bigint primary key,
  type text not null, --this should point to a party_type table in real life
  name text not null,
  ...party-type-specific into
);

create table party_roles (
  party_role_id bigint primary key,
  type text not null, --this should point to a party_role_type table in real life
  party_id bigint not null references parties(party_id),
  ...role-type-specific info...
);

create table addresses (
  address_id bigint primary key,
  type text not null, --this should point to a address_type table in real life
  ...address-type-specific info...
);

create table party_addresses (
  party_id bigint references parties(party_id),
  address_id bigint references addresses(address_id),
  role text not null, --this should point to a party_address_role table in real life

  primary key (party_id, address_id, role_id)
);

--demo data:

insert into parties values
(1, 'INDIVIDUAL', 'Neil'),
(2, 'ORGANIZATION', 'Acme, Inc');

insert into party_roles values
(1, 'EMPLOYEE', 1),
(2, 'CUSTOMER', 2);

insert into addresses values
(1, 'MAILING', '123 Main Street'),
(2, 'MAILING', '456 Bay Street');

insert into party_addresses values 
(1, 1, 'HOME'),
(2, 2, 'HEADQUARTERS');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.