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.
- 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
1 Answer
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 yourcreate table if not existsstatement, 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.