DB table structure for online election

[jasonsalas]

Here's what I'm trying to do: I'm running an online election service, so candidates under a senatorial race appear on a ballot, but logically, can be voted for (or not), using checkboxes. My problem in processing the info.

To display this in ASP, I need a single field populated in my DB to call from my derived recordset. I first tried using boolean values, but the resultant recordset then has a different field name per candidate (of which there are a lot), and it doesn't come out with values all under a single field, which I can't displaying in ASP.

I'd optimally like the DB table to have the following:
tblElection
- fieldID (autoID)
- fieldName (text)
- fieldPartyAffiliation (text)
- fieldVotes (number)

The main problem is getting all of those candidates with their respective vote totals all under one unified field name. I was thinking I might have to loop to SQL insert statement to go over the ballot and insert a "checked" value, and then in a DB query for presentation sum up all of the votes. But that's where I'm stuck.

I've racked my brain trying to figure this one out...any suggestions on the DB design?

Thanks!

 

[JJOHNS]

I have a suggestion. You could put the names into combo boxes. then the user could select only one per field. Another idea would be to use radio buttons instead of checkboxes to get the same result.

 

[jasonsalas]

Hi JJohns,

Thanks for the idea, but those won't work...it's a senatorial vote, so out of 43 candidates, only a maximum of up to (15) may be selected, so this rules out using radio buttons.

The form getting the info to add to the DB isn't the problem, nor is the script to actually insert a new record(s) to the DB...it's how the DB table will be structured.

Any ideas?

 

[AccessMoron]

Create a table to record the candidates info with each candidate haveing a unique numerical ID, use this ID to name your check box on your ASP page.

On the return form record the Elcection, VoterID and the ticked checkbox's that have been passed back in another table.