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\$
4
  • \$\begingroup\$ Would be nice if you described the schema. (ie tables and attributes marking the keys). \$\endgroup\$ May 24, 2014 at 17:49
  • \$\begingroup\$ @LokiAstari - sorry about that, added the structure for you \$\endgroup\$ May 24, 2014 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\$ May 24, 2014 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\$ May 24, 2014 at 18:08

1 Answer 1

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)

\$\endgroup\$
1
  • \$\begingroup\$ thank you, didnt know yer could have two fields as primary \$\endgroup\$ May 24, 2014 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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