Microsoft Philippines Community

 Guys please bear with an old man trying to regain his coding skills.. and normalization skills too :)

 Situation:

You have a cooperative who elects board of directors every fiscal year.The board of directors are also members of course. The way they vote is like this:

member has points, member can use these points to elect members of the board.  there are 13members of the BOD. a member can vote with his points. if a member has 100 points, he can give as many points as he wants to any of the candidates or he can abstain from voting any of the candidates. he can choose to distribute his 100 points to the 13 members as he wish or give 50 points to one member, 10 to another and 40 to another then just choose "abstain" for the other board members.members of the BOD will also go through this process.

I hope this is clear enough to understand. :)

I am in the process of designing the databas for this and I can't seem to be able to find a suitable "normalized" schema.

All answers are welcome no matter how deep or stupid :D 

Thanks in advance guys!

Here's my quick/draft design for your requirements.

Of course you'll have the Board table which should have columns like BoardID, Name, etc... the basics... this may also have the Points column.

Then you'll have a table for the votes which should contain columns like BoardID, BoardIDVoted (you can rename this, basically the BoardID of the person being voted), and then the VotePoints... Put any primary keys necessary. :)

That should allow you to enable voting and tallying the results.

dimebag0914:

WOW  ...bilis naman

 si sir edu ata gagawa ng taga bilang ng 2010 elections!!!

bmongtangco:

 si sir edu ata gagawa ng taga bilang ng 2010 elections!!!

Nice one Ed! 

Edu, why not try the domain-driven approach...design your objects first to model your problem, then worry about mapping it to the database later...you can use EF, NHibernate or any favourite tool to map it to DB

hi sir ed,

if i was to do it, it would do it this way.

MemberTable
MemberID
MemberName
PointsToGive
Abstain (Y/N)

CandidateTable
CandidateID
CandidateName

PointsTransactionTable
TransactionID
MemberID
CandidateID
TransactionDate
Points

 MemberTable ---< PointsTransactionTable >--- CandidateTable

 (one member has one to many transactions. one candidate has one to many transactions)

then just control the limits of how many points to give or has been given via coding

cruizer:

Edu, why not try the domain-driven approach...design your objects first to model your problem, then worry about mapping it to the database later...you can use EF, NHibernate or any favourite tool to map it to DB

I agree with sir cruizer.

jop:

I think I need two tables. 

Create table Boardmembers (id, name);
Create table Votes (voter_id, candidate_id, points);

Constraints:

• Primary key constraint on boardmembers.id
• Foreign key constraint on votes.voter_id = boardmembers.id.
• Foreign key constraint on votes.candidate_id = boardmembers.id.
• Check constraint on votes.voter_id != votes.candidate_id  -- I don't know if this is required. This is to prevent members from picking themselves.)
• Unique constraint on (votes.voter_id, votes.candidate_id). -- this is to prevent voters from voting the same candidate multiple times.
• Function-based check constraint -- I don't know if you can create a function based check constraint in SQLServer to enforce the points. You can either set it up as part of your Voting stored procedure or make it a trigger.

If you are going to reuse this for multiple elections, you can also create and Electionperiod table and link it to Votes via a  foreign key.

sir, we can't enforce that unique constraint above if we will have multiple elections (they will have multiple elections for sure)... possible solution is just to include the electionperiodid on the unique constraint...

and correction to the erd I made assuming the points allowed is the same between board member...  the allowed points info should be on the election table and not on the board table... we don't assign allowed points per board member and allowed points per election can change...

dimebag0914:

sir, we can't enforce that unique constraint above if we will have multiple elections (they will have multiple elections for sure)... possible solution is just to include the electionperiodid on the unique constraint...

Of course - if you have multiple elections you have to modify the constraints. That is left as an exercise to the reader.

 and multiple elections is not mentioned in edu's requirements.

CryptoKnight:

 and multiple elections is not mentioned in edu's requirements.

"You have a cooperative who elects board of directors every fiscal year"...  see edu's requirement...

and even if multiple election is not mentioned... I will still have my tables designed that way...

 uy! nagulat ako ah! andami nang sagot! Maraming maraming salamat sa inyo mga tol.

nasa kabilang PC yung DB eh pero ang kinalabasan eh para ding kumbinasyon ng mga naisulat dito. Nakakagulat talaga.

may table ako na Members (lahat ng members nung coop) tapos may tinyint na lang para sa Yes/No kung board member sya. Yun tsaka  isang transaction table na pinangalanan kong electionresults at dun magsasama sama sa isang row ang primary key ng transaction, bilang ng boto at membernumber ng ibinoto. Hindi kasama ang number ng kung sino ang bumoto para hindi malaman kung sino ang ibinoto ng kung sino (gulo!) hahaha! Para sa multiple elections, medyo magulo ang primary key ng transaction kasi kasama ang date ng election.

ayun! tapos sa BLL na lang masahe lahat tsaka sa RSS para mabasa ng mayos.

Ang nakakatuwa eh parang hawig nung sinabi ni cruizer ang nagawa ko (hindi sadya). Inuna ko na yung mga characters ng election tsaka sila parang isa-isang nalaglag na lang sa pwesto at nagka table na.

Para naman sa mga nagtataka kung bakit hindi ko nabibisita ang post ko na ito.. walang internet sa work. :(