I have 3 tables, 1 where voters information is stored, 2 where their address is stored and 3 where voter id
s are matched with address id
s. 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`)
Assigned Table
is only needed if a voter can live in more than one location. Which is technically possbile. But I would think an over-design as you can only have one registered voting address. \$\endgroup\$