Member Avatar for namratashukla

i want to design a database for cricket game where players will be selecting their players and while live cricket is goin on i want all user data base to get updated when the specefic player in his team i just want to know how to do this updation in data base where there will be large no of users having atleast 14 players. so how it is possible to update all the entry in all the tables

  • 3 Contributors
  • 2 Replies
  • 123 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by nikesh.yadav

Dani AI

Generated

— real-time scoring for many users is a scalability problem, not a mystery. and touched on DB design and client-side refresh; the practical solution is to stop trying to rewrite every user row for every ball. Instead: persist raw events, keep a small per-player aggregate, and derive each user’s total by joining that aggregate with the user’s team. This keeps writes tiny and reads fast.

Example minimal schema (focus on normalized, indexed tables):

CREATE TABLE user_teams (
  user_id INT NOT NULL,
  player_id INT NOT NULL,
  PRIMARY KEY (user_id, player_id),
  INDEX (player_id)
);

CREATE TABLE player_scores (
  player_id INT PRIMARY KEY,
  points INT NOT NULL DEFAULT 0,
  last_event_id BIGINT DEFAULT 0
);

CREATE TABLE player_events (
  event_id BIGINT PRIMARY KEY,
  player_id INT,
  event_type VARCHAR(32),
  runs INT,
  occurred_at TIMESTAMP
);

Worker pattern (pseudo-PHP): consume the live feed into a queue, compute the points delta for the event, apply the delta to the single player_aggregate row, mark the event_id so processing is idempotent, then publish the delta to a pub/sub channel so browsers can update instantly.

To read a user score on demand:

SELECT SUM(ps.points) AS total
FROM user_teams ut
JOIN player_scores ps ON ut.player_id = ps.player_id
WHERE ut.user_id = ?

Operational tips: use a message queue (Redis/Kafka) and background workers to avoid synchronous DB spikes. Push UI updates with WebSockets or Server-Sent Events so clients update only the teams they care about. Add an index on player_id in user_teams to find affected users quickly if you must push notifications. Batch writes, dedupe events by id, and keep monitoring (latency, queue depth, DB locks). This approach scales far better than trying to touch every user row for each live event.

Recommended Answers

All 2 Replies

Member Avatar for petr.pavel

Hi Namratashukla, I don't see a problem in updating a large number of records in database tables. You ask how - the answer is using UPDATE or INSERT statement.

I'm not sure if you're new to SQL (then google for "sql tutorial" or "sql reference") or if you didn't provide enough information. Why do you think that updating the db will be a problem? If you have many db tables then maybe your database design is bad and you should use less db tables (e.g. not one db table for each player).

Member Avatar for nikesh.yadav

hi namrata ,
Their should not be any problem in updating database.u should update database by simple update query.

I think u should use ajax so that on hit run by any user will update record of all user.

try and u get many peoples help u.but u should try

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.