1
\$\begingroup\$

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`)
\$\endgroup\$
  • \$\begingroup\$ Would be nice if you described the schema. (ie tables and attributes marking the keys). \$\endgroup\$ – Martin York May 24 '14 at 17:49
  • \$\begingroup\$ @LokiAstari - sorry about that, added the structure for you \$\endgroup\$ – Rudiger Kidd May 24 '14 at 17:59
  • \$\begingroup\$ The 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\$ – Martin York May 24 '14 at 18:03
  • \$\begingroup\$ @LokiAstari you'd be surprised, I did it this way with that in mind, and it is indeed possible, for example uni students to be registered with uni and home address, rare as it is \$\endgroup\$ – Rudiger Kidd May 24 '14 at 18:08
1
\$\begingroup\$

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)

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ thank you, didnt know yer could have two fields as primary \$\endgroup\$ – Rudiger Kidd May 24 '14 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.