Table Design Strategy

[mickeyl]

For several years I have been (successfully) working with various NoSQL-databases on the server and YapDatabase on the (mobile) client. Since GRDB seems to be better suited for the new world of SwiftUI and Swift Concurrency, I want to give it a try.

YapDatabase's idea is to use SQLite as a pretty dumb storage where only the relevant meta information gets stored in "the usual" columns, but the actual model object is stored as an encoded BLOB. I wonder whether I should continue with this strategy or whether I should reconsider this and do some actual actual table design.

As an example: My current app wants to hold a bunch of VehicleScan objects in a database. A scan roughly looks like that:

struct VehicleScan {

    let capture: UDS.CaptureFile
    let vin: Cornucopia.Core.VIN
    var timestamp: Date { capture.timestamp }
}

The heart of the information is contained in the UDS.CaptureFile, which in turn looks like that:

    @frozen struct CaptureFile: Codable {

        public let timestamp: Date
        public let busProtocol: UDS.BusProtocol
        public let scan: [UDS.Message: UDS.Message]
        }

and last but not least, a UDS.Message is simple that:

    @frozen struct Message: Codable, Hashable, Equatable {
        
        /// The request arbitration id controls the suggested receiver of this message.
        public let id: UDS.Header
        /// The expected reply id. Often, this is the UDS.Header with the 4th bit set (`| 1 << 4`)
        public let reply: UDS.Header
        /// The message payload
        public var bytes: [UInt8]
}

What's the best strategy for storing such a nested structure of VehicleScan objects with GRDB?

[groue]

Hello @mickeyl,

Your question is more about database schemas than GRDB itself, so it's somewhat out of scope.

Yet, here are some hints:

• The split between VehicleScan and CaptureFile does not look relevant at the database level: you should wonder if you really want two distinct tables for those. Put SQLite on your side. If you want to enforce, at the database level, that the relationship between VehicleScan and CaptureFile is a strict "one-to-one", without possibility for a missing side, then you should use a single table.

• [UDS.Message: UDS.Message] is not a trivial database scalar (int, double, text, blob), so you'll have to find a way to encode this into the database. You may use JSON, or dedicated database tables, surely other options, depending on the kind of queries you intend to perform on those values.