Case Study · Sports & federations
TBF: the Basketball Management System's database — architecture and migration from the old system to the new
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. A naming standard, 1,685 column mappings, 226 foreign keys, document export, 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 they would run on was empty.
The old system’s database carried 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 with the product’s prefixes, columns under the product’s rules, documents 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.
The job was this: design the tables the new application needs to a clean standard, move all of the old data into them correctly, and hand over in a way the federation’s own team could sustain.
Naming first
No code was written in the first meeting; naming rules were discussed. 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 — 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] |
The ExID decision was the migration’s insurance: 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.
The module-by-module loop
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; all of them, in the right order, were combined 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 that on cut-over day no step was 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 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 not once but 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; 60 days of question support by e-mail followed delivery.
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; every record from the old system stayed traceable through ExID. The naming standard was 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.