Member Avatar for Ken1234

i have a problem in choosing data type for this column in SQL..can anybody help me???

result (tournament_id*, player_id*, round)
i've done the first 2 columns, i'm confused about the data type of round=='...i wondered if it was cstring or text or numeric
here's my code :

tournament_id int,
    player_id int,
    PRIMARY KEY (tournament_id,player_id) REFERENCES tournament(id),player(id),
    round  ???

and the request is
The result round of a player at a tournament can be one of these values: ‘Won’, ‘Final’, ‘Semi’, ‘Quarter’, ‘4’, ‘3’, ‘2’ and ‘1 round’. Except for ‘Won’, the value denotes in which round of the tournament the player lost.

  • 3 Contributors
  • 2 Replies
  • 286 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by ugi.nagesh

Dani AI

Generated

Good direction from and — use an id rather than free text. Practical, production-ready approach: store a small numeric code in the results row and keep human labels and sort order in a tiny lookup table. That gives fast indexes, referential integrity, and a stable place to keep the display string or any UI code without risking typos in the results table.

A compact schema idea (MySQL-friendly):

CREATE TABLE rounds (
  id TINYINT UNSIGNED PRIMARY KEY,
  code CHAR(3) NOT NULL UNIQUE,     -- machine-friendly short code (W, F, SF, QF, R4...)
  label VARCHAR(20) NOT NULL,       -- display text ("Won", "Final", "Semi", "Round 4"...)
  seq TINYINT UNSIGNED NOT NULL     -- ordering for reporting (1 = best)
);

-- sample mapping (insert the eight values you listed, with seq chosen so queries sort properly)
INSERT INTO rounds (id, code, label, seq) VALUES
(1,'W','Won',1),
(2,'F','Final',2),
(3,'SF','Semi',3),
(4,'QF','Quarter',4),
(5,'R4','Round 4',5),
(6,'R3','Round 3',6),
(7,'R2','Round 2',7),
(8,'R1','Round 1',8);

Make the results column a small integer and FK it:

ALTER TABLE results
  ADD COLUMN round_id TINYINT UNSIGNED NOT NULL,
  ADD CONSTRAINT fk_results_rounds FOREIGN KEY (round_id) REFERENCES rounds(id);

Fetch readable output and order by progress:

SELECT res.player_id, rd.label AS round_reached
FROM results res
JOIN rounds rd ON res.round_id = rd.id
WHERE res.tournament_id = 123
ORDER BY rd.seq;

Notes and cautions:

  • ENUM is an alternative if values truly never change and you want fewer joins, but ENUMs are harder to alter later, can’t hold extra metadata (ordering/display code), and are less portable.
  • Normalize inconsistent text (e.g., store "Round 1" not both "1 round" and "1") by canonical codes/labels.
  • Declare your composite primary key and foreign keys correctly (don’t attempt to put REFERENCES inside the PRIMARY KEY clause). Use separate FK constraints for tournament_id and player_id.

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

I'd use a linked table so you can use an id, but also show the string.

Member Avatar for ugi.nagesh

Hi Ken,

You can maintain separate master table to store the different types of round data that you want

EX:

RoundMaster
(
Round_id integer primary key,
Round varchar(100)
)

Then reference the Round column in result table with Round_id of type integer.

Hope this will solve your problem

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.