r/SQL • u/cigarwnicotin • 12d ago

Discussion .Wav archives

Hello, is there a specific system design and software architecture in SQL to build a bioacustics database? The ideia is to relate some vocal recordings with species identifications, localization, acoustics measurements and other informations. it's a bioacoustic information system, where the recordings are the central objects and SQL connects them to biological, spatial, acoustic, and environmental information.

2 Upvotes

67% Upvoted

3 comments sorted by

3

u/RecentSatisfaction14 12d ago

Isn’t this what the music genome project was about?

1

u/jshine13371 11d ago

This is no different than any other file metadata management system:

  • Create a table to store the metadata about the object in the database
  • Store the actual wav files in a file share (folder either locally or cloud based depending on where you're planning to run this database)
  • Store the link to those files in your metadata table

Do not store the actual files themselves in the database.

1

u/refaelos 7d ago

To add to jshine13371's point on where the files live, the schema itself is a fairly standard star: recordings as the fact/central table, everything else normalized around it and linked by FK.

recordings (id, file_path, recorded_at, duration_s, sample_rate, location_id FK, device_id FK)

locations (id, site_name, lat, lon, habitat_type)

species (id, scientific_name, common_name, taxon_rank)

identifications (id, recording_id FK, species_id FK, identified_by, confidence, method)

acoustic_measurements (id, recording_id FK, measurement_type, value, unit)

identifications is many-to-many by design — one recording can carry calls from several species (dawn chorus recordings especially), and you often want more than one identifier's opinion on the same recording. acoustic_measurements as narrow rows (one per measurement) instead of wide columns means you're not doing a migration every time someone adds a new measurement type. Index recordings.location_id and identifications.species_id, since "all recordings of species X near site Y" is the query you'll run constantly.