tags:

views:

36

answers:

1

Below are two schema which I believe achieve the same result, i.e. m:n relationship with the option for a default/preferred value. Is there any reason to use one over the other?

Schema #1

CREATE TABLE people (
  id serial,
  primary key (id)
);

CREATE TABLE names (
  id serial,
  first_name text not null,
  last_name text not null,
  primary key (id)
);

CREATE TABLE person_has_name (
  person_id integer not null references people (id),
  name_id integer not null references names (id),
  is_default boolean not null default false,
  primary key (person_id, name_id)
);

Schema #2

CREATE TABLE people (
  id serial,
  default_name_id integer references names (id),
  primary key (id)
);

-- this table has not changed
CREATE TABLE names (
  id serial,
  first_name text not null,
  last_name text not null,
  primary key (id)
);

CREATE TABLE person_has_name (
      person_id integer not null references people (id),
      name_id integer not null references names (id),
      primary key (person_id, name_id)
);
A: 

With the second schema you can't add people without first knowing their name. As long as this is ok (i.e. you know that whenever a person is added, they have to reveal their name), then I'd say that either of these schemas are fine, and that schema #2 might even be more optimized in the scenario where you need to query only for the default name.

PatrikAkerstrand
sorry, the FK shouldn't have a not null constraint
also id like to add, in first schema, person_has_name would need some constraint to ensure there is only one default=true per person_id. for this reason and the reason you describe i am favouring schema #2 too.
however, schema #2 has its own problem now i think about it. should default_name_id also exist in person_has_name table? it's possible that it won't.