Hi On Fri, Aug 11, 2017 at 4:19 AM, Joshua Root <j...@macports.org> wrote:
> On 2017-8-11 03:41 , Bradley Giesbrecht wrote: > >> I think Josh is referring to 3NF normalization (third normal form). I >> don’t think this use case warrants this complexity. I think it is fine for >> two snapshot id’s to reference the same port+variant combination. When a >> snapshot id is deleted, cascade delete. >> > > Again I'm not sure how this differs from what I wrote. A snapshot contains > any number of ports, and a port can be in any number of snapshots. > I tried to clear this in my other mail. > Don't worry about having a completely textbook normalised DB, but do avoid > replicating large amounts of the data. Sure. > And TBH a join query would be one of the simplest parts of all this... > I started with a join query actually. Something like this, char* query = "SELECT port_name, requested, variant_name, variant_sign " "FROM registry.snapshots " "INNER JOIN " "registry.snapshot_ports ON " "snapshots.id=snapshot_ports.snapshots_id " "LEFT JOIN " "registry.snapshot_port_variants ON " "snapshot_ports.id=snapshot_port_variants.snapshot_ports_id" "WHERE snapshots.id=?"; But then I was having a hard time passing the result to Tcl without a snapshot struct. And if there is a need for reg_snapshot, then need for methods as well to operate on it. So, I separated the SQL functions to fetch ports first and then variants. But, having said this all, now I think I could have solved this with JOIN query as well. All these ideas keep coming to me, the longer I spend my time. I can really use an idea on workflow here. - Umesh