Case Study · Sports & federations
TBF: database design for the Basketball Management System and migration from the old system
While the Turkish Basketball Federation was writing a new Basketball Management System with its own development team, we designed the database the system would run on and moved the old system's 454 tables and five million records into the new structure module by module. The work covered a naming standard, 1,685 column mappings, 226 foreign keys, document export, the cut-over migration and documentation. The BYS the federation uses today runs on this database.
- 454 → 121
- Tables
- 1,685
- Column mappings
- 226
- Foreign keys
- ~5 M
- Records
From the old system to the new modular structure
Old column → new column, in a single mapping file
With create and drop scripts
Licence transactions, people, matches, statistics
Problem
In 2019 the Turkish Basketball Federation had begun replacing the management software it had used for years with a new Basketball Management System written by its own developers: .NET, SQL Server, a responsive web application. The screens were being designed, but the database layer underneath them was still empty.
The old system’s database held 454 tables and close to five million records: people, licence transactions, teams, matches, statistics, fee entitlements, phone numbers, addresses. Its structure was a vendor product’s generic schema. Table names carried the product’s prefixes, columns followed the product’s rules, and documents were stored inside the database as BLOBs. Moving that structure as it was would have made the new system a copy of the old one; designing from scratch and leaving the data behind would have meant losing decades of registration and licensing history.
Our task was to design the tables the new application needed to a clean standard, move all of the old data into them correctly, and hand the work over in a way the federation’s own team could sustain.
Naming rules
We wrote no code in the first meeting; we discussed naming rules. Every table, column and procedure in the new database would follow the same rule:
| Rule | Decision |
|---|---|
| Table prefix | modul_ for business tables, tanim_ for reference tables, sistem_ for infrastructure tables |
| Identity | ID in every table; the old system’s key carried alongside as ExID |
| Reference | Where a table holds a reference’s ID, the reference’s value sits next to it, so screens read without joins |
| Data types | nvarchar for names, ntext for descriptions, smalldatetime for dates; date columns descriptive, such as Talep_Tarih, Onay_Tarih |
| Procedures | sp_ prefix; a table’s insert/update procedure carries the table’s name |
| Documents | Not in the database but on the file system: /Yuklemeler/Icerik/[Folder]/[File]-[row]-[GUID].[ext] |
With ExID, every new record knows its old one. Any inconsistency after migration can be traced back to the old system, and on cut-over day only newly added records need to be told apart.
Module by module
The new system’s modules were known: registration and licensing (person, identity, licence transactions, events), club operations (teams, legal entities, team transactions), match, fixture, statistics. The same loop ran for every module:
- A task is opened in the tracker; the tables feeding the module in the old system are identified.
- The table list is sent to TBF; it is confirmed or corrected.
- Tables are created to the naming standard; the old column → new column mapping is entered in a single file.
- Data is migrated, checked for correctness, handed to TBF for testing.
- When TBF confirms, the task is closed; on to the next module.
The result was 121 tables (42 module tables, 78 reference tables) and 1,685 column mappings from 119 old tables. Each table’s script lives in its own file, and all of them were combined in the right order into a single migration script. That script went through 17 versions. After every TBF test it was corrected and run again from the start, so on cut-over day no step had to be done by hand.
Technical details
Documents. The old system kept contracts, photos and forms as BLOBs inside tables; one of the largest tables was nothing but attachments. During migration a cursor walks every record, writes the attachment to the file system through a stored procedure and puts the file path in the new record. The database shrank and documents became files that can be backed up.
Integrity and speed. 226 foreign keys were defined, each written as “create if missing / drop if present” so they can be removed before a migration run and restored afterwards. The 122 indexes on the old key were moved to the new ID columns. The old system’s triggers and the most frequently run queries were reworked for the new structure.
Duplicates. Over the years the same person had been opened more than once. Scripts that find and merge duplicate records were written; faulty records were presented to the federation in a separate list.
Cut-over day. The migration ran several times: first a test with the full data, then, at the agreed day and hour, a final run of the most current data from the old system. The migration scripts, the mapping file and the documentation were handed to TBF, followed by 60 days of question support by e-mail.
Outcome
The BYS written by the federation’s own team went live on this database and still runs on it today. Registration, licensing, club, match and statistics data moved without interruption, and every record from the old system stayed traceable through ExID. The naming rules were also handed over as the standard for the team’s later development.
Six years later we built the referee and evaluator assignment system for the same federation.