Fwd: Table relationships - Mailing list pgsql-sql

I'm having trouble determining the best way to implement the following scenario for a customer database.

 

Given the following tables what is the best way to link an address table to both the "Master" and the "Detail" tables. Basically there can be many addresses for each customermaster record and also many address for each customerdetail record. Will this require two many-to-many tables? Or is there a better solution I am missing? Is there any easy way to build in a "Default" address or would that be something to do logically in client code?

 

Thanks,

Curtis

 

CREATE TABLE testing.customermaster

(

  customermasterid int4 NOT NULL DEFAULT nextval('testing.customermatser_customermasterid_seq'::regclass),

  name varchar NOT NULL,

  description varchar,

)

 

CREATE TABLE testing.customerdetail

(

  customerdetailid int4 NOT NULL,

  customermasterid int4 NOT NULL,

  notes varchar,

  closed bool,

  customerdepartment varchar(3),

)

 

CREATE TABLE address

(

  addressid int4,

  name varchar(40),

  addr varchar,

  city varchar(42),

  st varchar(2),

  zip varchar(30)

)

 

 

Possible many-to-many tables

CREATE TABLE customermaster_address

(

  addressid int4,

 customermasterid int4

)

 

CREATE TABLE customerdetail _address

(

  addressid int4,

 customerdetailid int4

)