Database structure

[Norman0406]

Create the general database structure for a logbook database. Tables might include

• Logbook: QSO information with foreign key references to callsigns
• Callsigns: Entries for each callsign the user had contact with.

This is just a suggestion. Change it and add new tables as required.

[Norman0406]

How to handle callsigns that have different annotations, like /P, /M, or prefixes when the user is operating from a foreign country? This is QSO-specific and might change from time to time. Maybe this could be stripped down to the same user, and additional annotations would be supplied in the logbook table for the specific QSO.

[semaph0r]

Simple regex which filters slashes/dashes during input validation before save transaction to the database. Otherwise the user will have a dupe. Not very promising, but easy to implement.

[Norman0406]

Sounds good!

[Norman0406]

There is some information about amateur radio callsign formation on Wikipedia.

[Norman0406]

Table proposal:

• "info": contains general information about the loaded logbook. This is basically a table with only one row. It contains the following entries (and more as needed)
  • Name: Custom logbook name
  • Author: initial logbook author
  • DBVersion: database version
  • LISAVersion: LISA version
• "custom_fields": contains information about the custom database fields. This is a mapping between a custom field entry ("Custom1", "Custom2", ...) and an associated name.
• "logbook": contains the actual logbook qsos
• "stations": contains information about the qso partners that the user had contact with
• "profiles": stores the profile data that the user worked with

The "stations" and "profiles" table is being referenced from "logbook". When the user has contact with the same station twice, the logbook references the same entry from "stations". When the user changes a little detail for the worked station, the entry in "stations" will be duplicated with the new information.

[Norman0406]

Are custom fields unique per database or per QSO? I.e., when the user changes the name of a custom field in the database, will every QSO display the new name for the custom field? Alternatively, the custom field name could be stored for each QSO. But what with new QSOs? Will the custom fields be named in the same way as the last QSO or will they be reset?