Member Avatar for iamy1204

I am writing a sports event result analyse of a database, there are few awards will be given
1. individual prize:
suppose getting champion get 5 marks , second runner up get 3 marks , etc
2. the most active athlete award
every student in a House participating every event gets 1 mark
3. the overall champion of a House in a school

what tables and schema do i need if i want to calculate marks from different event to present the awards...thank you

  • 2 Contributors
  • 3 Replies
  • 82 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by SKANK!!!!!

Dani AI

Generated

you want three things: position-based individual points, participation (most-active) points, and a house total. was right to point to recording events and people, but avoid keeping a single "marks" column that you manually update — that gets inconsistent. Record raw results and keep scoring rules separate so totals can be recomputed reliably.

A minimal, flexible schema (simplified):

CREATE TABLE houses  (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE students(id INT PRIMARY KEY, name VARCHAR(100), house_id INT);
CREATE TABLE events  (id INT PRIMARY KEY, name VARCHAR(100), event_type_id INT);
CREATE TABLE scoring_rules (event_type_id INT, position INT, points INT, PRIMARY KEY(event_type_id, position));
CREATE TABLE results (id INT PRIMARY KEY, event_id INT, student_id INT, position INT NULL, participated TINYINT(1) DEFAULT 1);

Calculate totals with GROUP BY and joins so scoring changes apply automatically. Examples:

-- individual total (position points)
SELECT s.id, s.name, SUM(COALESCE(sr.points,0)) AS total_points
FROM students s
JOIN results r ON r.student_id = s.id
JOIN events e ON r.event_id = e.id
LEFT JOIN scoring_rules sr ON sr.event_type_id = e.event_type_id AND sr.position = r.position
GROUP BY s.id, s.name;

-- most active (participations)
SELECT s.id, s.name, COUNT(*) AS events_participated
FROM students s JOIN results r ON r.student_id = s.id
GROUP BY s.id, s.name;

-- house total
SELECT h.id, h.name, SUM(COALESCE(sr.points,0)) AS house_points
FROM houses h
JOIN students s ON s.house_id = h.id
JOIN results r ON r.student_id = s.id
JOIN events e ON r.event_id = e.id
LEFT JOIN scoring_rules sr ON sr.event_type_id = e.event_type_id AND sr.position = r.position
GROUP BY h.id, h.name;

Notes: keep scoring_rules per event type (so relays vs individual can differ), version rules by season if needed, and index results on student_id/event_id. For live aggregates use scheduled jobs or triggers; see MySQL GROUP BY and Triggers docs for details (, Triggers).

Recommended Answers

All 3 Replies

Member Avatar for SKANK!!!!!

a table for events and a table for users

Member Avatar for iamy1204

i know i have to set the above two tables....but how to calculate marks...or what sql i need to use?

Member Avatar for SKANK!!!!!

have a column in users for marks. and when someone gets a mark update it

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.