sqlite - How do I simplify this model with a double reference? - Stack Overflow

admin管理员组

文章数量:1434904

My CRUD application's data models:

What is the proper way to set up the FilmGroup model? It has a one-to-many relationship with Film and a one-to-one relationship with FilmOrder.

My models for the other entities:

type Film struct {
    ID                 int     `db:"id"`
    FilmName           string  `db:"film_name"`
    FilmFormat         *string `db:"film_format"`
    FilmProcessingType *string `db:"film_processing_type"`
    ScansFolder        *string `db:"scans_folder"`
    RollLabel          *string `db:"roll_label"`
    Comments           *string `db:"comments"`
    DateShot           *string `db:"date_shot"`
    ScanCompleted      *bool   `db:"scan_completed"`
    CutAndSleeved      *bool   `db:"cut_and_sleeved"`
}

type FilmOrder struct {
    ID                    int        `db:"id"`
    FilmLabName           string     `db:"film_lab_name"`
    ToLabTracking         *string    `db:"to_lab_tracking"`
    FromLabTracking       *string    `db:"from_lab_tracking"`
    DateFilmReceivedByLab *time.Time `db:"date_film_received_by_lab"`
    DateFilmReceivedByMe  *time.Time `db:"date_film_received_by_me"`
    DateOrdered           *time.Time `db:"date_ordered"`
    DateMailedToLab       *time.Time `db:"date_mailed_to_lab"`
    ScansRequested        *bool      `db:"scans_requested"`
    ScansReceived         *bool      `db:"scans_received"`
    DateScansReceivedByMe *time.Time `db:"date_scans_received_by_me"`
}

And for FilmGroup:

type FilmGroup struct {
    ID          int        `db:"id"`
    Films       []Film     `db:"films"`
    FilmOrderID *int       `db:"film_order_id"`
    FilmOrder   *FilmOrder
    Status      string     `db:"status"`
    Comments    *string    `db:"comments"`
}

I need both FilmOrderID and FilmOrder to associate the FilmOrderID with this model, but also want easy access to the associated FilmOrder since I render all data to the user.

In my AddFilmGroup method I pass just the FilmOrderID:

func AddFilmGroup(db *sql.DB, filmGroup models.FilmGroup) error {
    insertGroupQuery := `
        INSERT INTO FilmGroup (status, comments, film_order_id)
        VALUES (?, ?, ?);`
    result, err := db.Exec(insertGroupQuery, filmGroup.Status, filmGroup.Comments, filmGroup.FilmOrderID)
    if err != nil {
        return fmt.Errorf("failed to insert FilmGroup: %w", err)
    }

    groupID, err := result.LastInsertId()
    if err != nil {
        return fmt.Errorf("failed to retrieve FilmGroup ID: %w", err)
    }

    for _, film := range filmGroup.Films {
        insertFilmGroupFilmQuery := `
            INSERT INTO FilmGroup_Film (film_group_id, film_id)
            VALUES (?, ?);`
        _, err := db.Exec(insertFilmGroupFilmQuery, groupID, film.ID)
        if err != nil {
            return fmt.Errorf("failed to associate Film %d with FilmGroup %d: %w", film.ID, groupID, err)
        }
    }

    log.Printf("FilmGroup '%d' added successfully.\n", groupID)
    return nil
}

It complicates fetching:

func GetFilmGroupWithDetails(db *sql.DB, groupID int) (models.FilmGroup, error) {
    var filmGroup models.FilmGroup

    query := `
        SELECT id, status, comments, film_order_id
        FROM FilmGroup
        WHERE id = ?;`
    err := db.QueryRow(query, groupID).Scan(&filmGroup.ID, &filmGroup.Status, &filmGroup.Comments, &filmGroup.FilmOrderID)
    if err != nil {
        if err == sql.ErrNoRows {
            return filmGroup, fmt.Errorf("FilmGroup with ID %d not found", groupID)
        }
        return filmGroup, fmt.Errorf("failed to fetch FilmGroup: %w", err)
    }

    // Fetch the associated films
    filmQuery := `
        SELECT f.id, f.film_name, f.film_format, f.film_processing_type, f.scans_folder, f.roll_label, fments, f.date_shot, f.scan_completed, f.cut_and_sleeved
        FROM Film f
        JOIN FilmGroup_Film fgf ON f.id = fgf.film_id
        WHERE fgf.film_group_id = ?;`
    rows, err := db.Query(filmQuery, groupID)
    if err != nil {
        return filmGroup, fmt.Errorf("failed to fetch films for FilmGroup %d: %w", groupID, err)
    }
    defer rows.Close()

    var films []models.Film
    for rows.Next() {
        var film models.Film
        var filmFormat, filmProcessingType, scansFolder, rollLabel, comments, dateShot sql.NullString
        var scanCompleted, cutAndSleeved sql.NullBool

        if err := rows.Scan(&film.ID, &film.FilmName, &filmFormat, &filmProcessingType, &scansFolder, &rollLabel, &comments, &dateShot, &scanCompleted, &cutAndSleeved); err != nil {
            return filmGroup, fmt.Errorf("failed to scan film row: %w", err)
        }

        // Handle nullable fields
        // Redacted

        films = append(films, film)
    }
    if err := rows.Err(); err != nil {
        return filmGroup, fmt.Errorf("error iterating film rows: %w", err)
    }
    filmGroup.Films = films

    var filmOrder models.FilmOrder
    if filmGroup.FilmOrderID != nil {
        filmOrderQuery := `
            SELECT id, film_lab_name, to_lab_tracking, from_lab_tracking, date_film_received_by_lab, date_film_received_by_me, date_ordered, date_mailed_to_lab, scans_requested, scans_received, date_scans_received_by_me
            FROM FilmOrder
            WHERE id = ?;`
        err = db.QueryRow(filmOrderQuery, *filmGroup.FilmOrderID).Scan(&filmOrder.ID, &filmOrder.FilmLabName, &filmOrder.ToLabTracking, &filmOrder.FromLabTracking, &filmOrder.DateFilmReceivedByLab, &filmOrder.DateFilmReceivedByMe, &filmOrder.DateOrdered, &filmOrder.DateMailedToLab, &filmOrder.ScansRequested, &filmOrder.ScansReceived, &filmOrder.DateScansReceivedByMe)
        if err != nil {
            if err == sql.ErrNoRows {
                return filmGroup, fmt.Errorf("FilmOrder not found for FilmGroup %d", groupID)
            }
            return filmGroup, fmt.Errorf("failed to fetch FilmOrder: %w", err)
        }
        filmGroup.FilmOrder = &filmOrder
    }

    filmGroup.FilmOrder = &filmOrder

    return filmGroup, nil
}

My database:

CREATE TABLE IF NOT EXISTS Film (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    film_name TEXT NOT NULL,
    film_format TEXT,
    film_processing_type TEXT,
    scans_folder TEXT,
    roll_label TEXT,
    comments TEXT,
    date_shot TEXT,
    scan_completed BOOLEAN,
    cut_and_sleeved BOOLEAN
);


CREATE TABLE IF NOT EXISTS FilmOrder (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    film_lab_name TEXT NOT NULL,
    to_lab_tracking TEXT,
    from_lab_tracking TEXT,
    date_film_received_by_lab DATE,
    date_film_received_by_me DATE,
    date_ordered DATE,
    date_mailed_to_lab DATE,
    scans_requested BOOLEAN,
    scans_received BOOLEAN,
    date_scans_received_by_me DATE
);

CREATE TABLE IF NOT EXISTS FilmGroup (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    status TEXT,
    comments TEXT,
    film_order_id INTEGER,
    FOREIGN KEY (film_order_id) REFERENCES FilmOrders (id)
);

// Junction table
CREATE TABLE IF NOT EXISTS FilmGroup_Film (
    film_group_id INTEGER NOT NULL,
    film_id INTEGER NOT NULL,
    PRIMARY KEY (film_group_id, film_id),
    FOREIGN KEY (film_group_id) REFERENCES FilmGroup (id),
    FOREIGN KEY (film_id) REFERENCES Film (id)
);

How do I simplify the FilmGroup model so it does not need FilmOrderID and a reference to a FilmOrder object? How to map FilmOrder *FilmOrder to a database field? I want to use the same struct for the database model and to display the object.

My CRUD application's data models:

What is the proper way to set up the FilmGroup model? It has a one-to-many relationship with Film and a one-to-one relationship with FilmOrder.

My models for the other entities:

type Film struct {
    ID                 int     `db:"id"`
    FilmName           string  `db:"film_name"`
    FilmFormat         *string `db:"film_format"`
    FilmProcessingType *string `db:"film_processing_type"`
    ScansFolder        *string `db:"scans_folder"`
    RollLabel          *string `db:"roll_label"`
    Comments           *string `db:"comments"`
    DateShot           *string `db:"date_shot"`
    ScanCompleted      *bool   `db:"scan_completed"`
    CutAndSleeved      *bool   `db:"cut_and_sleeved"`
}

type FilmOrder struct {
    ID                    int        `db:"id"`
    FilmLabName           string     `db:"film_lab_name"`
    ToLabTracking         *string    `db:"to_lab_tracking"`
    FromLabTracking       *string    `db:"from_lab_tracking"`
    DateFilmReceivedByLab *time.Time `db:"date_film_received_by_lab"`
    DateFilmReceivedByMe  *time.Time `db:"date_film_received_by_me"`
    DateOrdered           *time.Time `db:"date_ordered"`
    DateMailedToLab       *time.Time `db:"date_mailed_to_lab"`
    ScansRequested        *bool      `db:"scans_requested"`
    ScansReceived         *bool      `db:"scans_received"`
    DateScansReceivedByMe *time.Time `db:"date_scans_received_by_me"`
}

And for FilmGroup:

type FilmGroup struct {
    ID          int        `db:"id"`
    Films       []Film     `db:"films"`
    FilmOrderID *int       `db:"film_order_id"`
    FilmOrder   *FilmOrder
    Status      string     `db:"status"`
    Comments    *string    `db:"comments"`
}

I need both FilmOrderID and FilmOrder to associate the FilmOrderID with this model, but also want easy access to the associated FilmOrder since I render all data to the user.

In my AddFilmGroup method I pass just the FilmOrderID:

func AddFilmGroup(db *sql.DB, filmGroup models.FilmGroup) error {
    insertGroupQuery := `
        INSERT INTO FilmGroup (status, comments, film_order_id)
        VALUES (?, ?, ?);`
    result, err := db.Exec(insertGroupQuery, filmGroup.Status, filmGroup.Comments, filmGroup.FilmOrderID)
    if err != nil {
        return fmt.Errorf("failed to insert FilmGroup: %w", err)
    }

    groupID, err := result.LastInsertId()
    if err != nil {
        return fmt.Errorf("failed to retrieve FilmGroup ID: %w", err)
    }

    for _, film := range filmGroup.Films {
        insertFilmGroupFilmQuery := `
            INSERT INTO FilmGroup_Film (film_group_id, film_id)
            VALUES (?, ?);`
        _, err := db.Exec(insertFilmGroupFilmQuery, groupID, film.ID)
        if err != nil {
            return fmt.Errorf("failed to associate Film %d with FilmGroup %d: %w", film.ID, groupID, err)
        }
    }

    log.Printf("FilmGroup '%d' added successfully.\n", groupID)
    return nil
}

It complicates fetching:

func GetFilmGroupWithDetails(db *sql.DB, groupID int) (models.FilmGroup, error) {
    var filmGroup models.FilmGroup

    query := `
        SELECT id, status, comments, film_order_id
        FROM FilmGroup
        WHERE id = ?;`
    err := db.QueryRow(query, groupID).Scan(&filmGroup.ID, &filmGroup.Status, &filmGroup.Comments, &filmGroup.FilmOrderID)
    if err != nil {
        if err == sql.ErrNoRows {
            return filmGroup, fmt.Errorf("FilmGroup with ID %d not found", groupID)
        }
        return filmGroup, fmt.Errorf("failed to fetch FilmGroup: %w", err)
    }

    // Fetch the associated films
    filmQuery := `
        SELECT f.id, f.film_name, f.film_format, f.film_processing_type, f.scans_folder, f.roll_label, fments, f.date_shot, f.scan_completed, f.cut_and_sleeved
        FROM Film f
        JOIN FilmGroup_Film fgf ON f.id = fgf.film_id
        WHERE fgf.film_group_id = ?;`
    rows, err := db.Query(filmQuery, groupID)
    if err != nil {
        return filmGroup, fmt.Errorf("failed to fetch films for FilmGroup %d: %w", groupID, err)
    }
    defer rows.Close()

    var films []models.Film
    for rows.Next() {
        var film models.Film
        var filmFormat, filmProcessingType, scansFolder, rollLabel, comments, dateShot sql.NullString
        var scanCompleted, cutAndSleeved sql.NullBool

        if err := rows.Scan(&film.ID, &film.FilmName, &filmFormat, &filmProcessingType, &scansFolder, &rollLabel, &comments, &dateShot, &scanCompleted, &cutAndSleeved); err != nil {
            return filmGroup, fmt.Errorf("failed to scan film row: %w", err)
        }

        // Handle nullable fields
        // Redacted

        films = append(films, film)
    }
    if err := rows.Err(); err != nil {
        return filmGroup, fmt.Errorf("error iterating film rows: %w", err)
    }
    filmGroup.Films = films

    var filmOrder models.FilmOrder
    if filmGroup.FilmOrderID != nil {
        filmOrderQuery := `
            SELECT id, film_lab_name, to_lab_tracking, from_lab_tracking, date_film_received_by_lab, date_film_received_by_me, date_ordered, date_mailed_to_lab, scans_requested, scans_received, date_scans_received_by_me
            FROM FilmOrder
            WHERE id = ?;`
        err = db.QueryRow(filmOrderQuery, *filmGroup.FilmOrderID).Scan(&filmOrder.ID, &filmOrder.FilmLabName, &filmOrder.ToLabTracking, &filmOrder.FromLabTracking, &filmOrder.DateFilmReceivedByLab, &filmOrder.DateFilmReceivedByMe, &filmOrder.DateOrdered, &filmOrder.DateMailedToLab, &filmOrder.ScansRequested, &filmOrder.ScansReceived, &filmOrder.DateScansReceivedByMe)
        if err != nil {
            if err == sql.ErrNoRows {
                return filmGroup, fmt.Errorf("FilmOrder not found for FilmGroup %d", groupID)
            }
            return filmGroup, fmt.Errorf("failed to fetch FilmOrder: %w", err)
        }
        filmGroup.FilmOrder = &filmOrder
    }

    filmGroup.FilmOrder = &filmOrder

    return filmGroup, nil
}

My database:

CREATE TABLE IF NOT EXISTS Film (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    film_name TEXT NOT NULL,
    film_format TEXT,
    film_processing_type TEXT,
    scans_folder TEXT,
    roll_label TEXT,
    comments TEXT,
    date_shot TEXT,
    scan_completed BOOLEAN,
    cut_and_sleeved BOOLEAN
);


CREATE TABLE IF NOT EXISTS FilmOrder (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    film_lab_name TEXT NOT NULL,
    to_lab_tracking TEXT,
    from_lab_tracking TEXT,
    date_film_received_by_lab DATE,
    date_film_received_by_me DATE,
    date_ordered DATE,
    date_mailed_to_lab DATE,
    scans_requested BOOLEAN,
    scans_received BOOLEAN,
    date_scans_received_by_me DATE
);

CREATE TABLE IF NOT EXISTS FilmGroup (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    status TEXT,
    comments TEXT,
    film_order_id INTEGER,
    FOREIGN KEY (film_order_id) REFERENCES FilmOrders (id)
);

// Junction table
CREATE TABLE IF NOT EXISTS FilmGroup_Film (
    film_group_id INTEGER NOT NULL,
    film_id INTEGER NOT NULL,
    PRIMARY KEY (film_group_id, film_id),
    FOREIGN KEY (film_group_id) REFERENCES FilmGroup (id),
    FOREIGN KEY (film_id) REFERENCES Film (id)
);

How do I simplify the FilmGroup model so it does not need FilmOrderID and a reference to a FilmOrder object? How to map FilmOrder *FilmOrder to a database field? I want to use the same struct for the database model and to display the object.

• sqlite
• go
• database-design

Share Improve this question Follow edited Nov 18, 2024 at 22:13 user4157124 2,9961414 gold badges3131 silver badges4646 bronze badges asked Nov 16, 2024 at 23:51 FlameDraFlameDra 2,08777 gold badges3737 silver badges5656 bronze badges 4

• Consider using GORM. – Schwern Commented Nov 17, 2024 at 1:23
• Could you describe what you're trying to model? It seems like Film is the physical film, and FilmOrder is tracking processing a group of Films? FilmGroup seems unnecessary. What sort of questions do you want to ask about this data? – Schwern Commented Nov 17, 2024 at 1:29
• I need a model where I can connect multiple Films to a FilmOrder. I am using FilmGroup as that struct, which can also have some other info not related to the order (like comments, or other future fields). – FlameDra Commented Nov 17, 2024 at 1:40
• Can a Film be in more than one FilmGroup? – Schwern Commented Nov 17, 2024 at 1:43

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

...the FilmGroup model. It has a one to many relationship with Film, and a one to one relationship with FilmOrder.

One-to-one relationships are suspect and difficult to enforce.

Here's a simpler model assuming a Film can only be in one FilmGroup, and a FilmOrder can only be for one FilmGroup. Film and FilmOrder both reference FilmGroup.

Film -> FilmGroup <- FilmOrder

If a Film can be in more than one FilmGroup, you'd put the FilmGroup_Film join table back, but FilmOrder would still refer to FilmGroup.

Film <- FilmGroup_Film -> FilmGroup <- FilmOrder

Both of these have the possibility that a FilmGroup could be the subject of multiple FilmOrders. If you want to make it one-to-one, make FilmOrders.film_group_id unique.

Notes

• Avoid create table if not exists. It seems convenient, but what will happen is you'll create the table, change your create table if not exists statement, run your script again, and then spend hours trying to figure out why it doesn't work.
• Autoincrement is unnecessary in SQLite.
• Consider timestamps instead of booleans so you can track when it happened. Null means it didn't happen. This comes in handy and avoids redundant data to also track a timestamp.
• Use timestamps instead of dates for better tracking precision, unless you really really only want to store the date. You can't get precision back later, but you can truncate a timestamp to a date.
• Consider using the *_on and *_at conventions. film_received_by_lab_at
• Consider making a table to store film labs for referential integrity and eventually you'll probably want to store extra info about the labs.

本文标签： sqliteHow do I simplify this model with a double referenceStack Overflow