Code Structuring for Databases

I am working a project with some fairly basic data structures, some having references of others and needing to store them in a database. I am not new to programming but i am new to using databases and wondering what is the best practice with rust for structuring code to be used with databases. I am planning to use rusqlite. I have already got an insert and get working for a single table but don’t have much of an idea to best handle the relational part of the database.

I have looked at the rusqlite docs and nothing sticks out to me as being helpful as i assume you understand how to work with MySQL in general.

Does anyone have any references that i could refer to better understand how to structure my code?

The “relational” part in a relational database is usually done by leveraging row IDs. Whenever there’s a relationship between two elements you’ll typically have one store the row ID of the other. So a row ID is almost the database equivalent of a pointer.

Normally for 1-to-1 or 1-to-many relationships you can store the ID “inline” (e.g. a billing address may have a column containing the ID of the user who owns it). Then you can retrieve the billing addresses for a particular user with something like “SELECT id FROM billing_addresses WHERE user_id = ?1”.

For more complex relationships you’ll often use a side table which just stores pairs of IDs. An example might be having a table with loads of rows saying “user 42 is friends with user 7”. From there, you can figure who is friends with who by checking for any row containing that user’s ID.

Thanks fo the response. So that makes sense to me but where I get hung up and I should have provided a code sample to show where my thought process was, is how is that managed? Do I have a field for both the id and a pointer to the instance of that referenced item if I plan to load it in memory or do I just keep a field for the I’d and always poll the database for the item?

This isn’t going to be a huge database or over a network so I don’t have performance concerns right now.

Here is a code sample of what I was thinking.

Keep a reference to B and B contains its ID and when I load A out of the database i go and load B. A would have a serialized column containing the ID of B.

pub struct A{
   pub b: &B,
}

pub struct B{
   pub id: i32,
}

Only keep track of the id in the struct and just always reference the database.

pub struct A {
   // ID value of B from database
   b_id: i32,
}