Thanks rudy,
when a business is subscribing, they enter the addresses for billing, administrator, location etc as appropriate. (one business - many addresses)
So the addresses are entered to the table, addresses.
The business is entered to business_details
so as not to have duplicate addresses in table addresses, I thought I should have a third table to associate business, addresses and address type.
CREATE TABLE business_addresses
( address_id int(11) NOT NULL
, business_id int(11) NOT NULL
, address_type varchar(32) NOT NULL default 'Location'
, PRIMARY KEY (address_id,business_id,address_type)
, KEY reverse_ix (business_id,address_id,address_type)
, constraint business_addresses_business_fk
foreign key (business_id)
references business_details (business_id) on delete cascade
, constraint business_addresses_address_fk
foreign key (address_id)
references addresses(id) on delete cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate utf8_unicode_ci;
The other possibility I thought of was not having the ‘joining table’.
Instead, I could have each address listed and in its record, store what the purpose of the address is to be, eg admin, postal etc. But I moved away from that thinking because a business which has one address for all types (billing, admin etc), would have that same address listed numerous times in the address table where the only difference between one if its records and another, would be the field address_type
.
which is more correct? having the joining table or, making the address table like this (where one address may be repeated for each address type):-
CREATE TABLE address
( business_id int(11) NOT NULL
, address_type varchar(32) NOT NULL
, house_name varchar(64) NOT NULL
, house_number varchar(12) NOT NULL
, address_1 varchar(64) NOT NULL
, address_2 varchar(64) NOT NULL
, address_3 varchar(64) NOT NULL
, town varchar(168) NOT NULL
, townland varchar(64) NOT NULL
, county varchar(32) NOT NULL
, city varchar(60) NOT NULL
, post_code varchar(11) NOT NULL
, country_name varchar(90) NOT NULL
, grid varchar(12) NOT NULL
, latitude varchar(12) NOT NULL
, longitude varchar(12) NOT NULL
, northings varchar(6) NOT NULL
, eastings varchar(6) NOT NULL
, PRIMARY KEY (business_id,address_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate utf8_unicode_ci;
bazz