Searching for voters by address

Question

I have 3 tables, 1 where voters information is stored, 2 where their address is stored and 3 where voter ids are matched with address ids. I thought this approach was best as several voters can occupy the same address.

Now I have Query that selects all the voters on a given street / road. Although it works, I'm not sure if I should be using joins or keep using where, and if locate is the best way to search a field.

SELECT `voter_name`, `address_full`
FROM `tr_voter`, `tr_voter_address`, `tr_voter_address_assigned`
WHERE tr_voter_address_assigned.voter_id = tr_voter.voter_id 
AND tr_voter_address_assigned.address_id = tr_voter_address.address_id 
AND LOCATE('insert street name here', `address_full`) 
ORDER BY `address_2`, `address_1`
LIMIT 0 , 200

address_full is the full address, where each component is broken down into address_1, address_2 etc. This does mean however, in some cases with house names, that a street may be stored in 1 2 or 3, so searching the full field seemed the full proof way to do it.

Voter Table

`voter_id` int(11) NOT NULL AUTO_INCREMENT,
`voter_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`voter_number` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
 `voter_title` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`voter_first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`voter_last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`voter_notes` text COLLATE utf8_unicode_ci NOT NULL,
`voter_last_vote_date` datetime DEFAULT NULL,
`voter_entry_date` datetime NOT NULL,
`voter_last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`voter_id`),
UNIQUE KEY `number` (`voter_number`)

Address Table

`address_id` int(11) NOT NULL AUTO_INCREMENT,
`address_full` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'address name or number',
`address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_6` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_lat` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_long` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`address_id`),
UNIQUE KEY `address_full` (`address_full`)

Assigned Table

`assigned_id` int(11) NOT NULL AUTO_INCREMENT,
`voter_id` int(11) NOT NULL,
`address_id` int(11) NOT NULL,
PRIMARY KEY (`assigned_id`)

Answer 1

OK Assuming you need the "Assigned Table" to support the many to many relationship of a voter to address.

`assigned_id` int(11) NOT NULL AUTO_INCREMENT,
`voter_id` int(11) NOT NULL,
`address_id` int(11) NOT NULL,
PRIMARY KEY (`assigned_id`)

You don't need assigned_id. Remove this. The primary key is (voter_id, address_id)