Solved: Best practice for enums with normalised database schemas

Let's say I have a database which holds animals. Each animal has an animal_type_id where cat is 1, dog is 2, snake is 3 and capybara is 4. Each animal record has a name field and an animal_type_id of 1, 2, 3 or 4.

I want to provide an API to allow users to create new animals in the database and give them a drop down list of allowable animal types so that they don't need to enter the ID number.

What would be the best way to represent that in Rust? Currently the project is using sqlx to read from the database and serde to generate JSON to send to the web clients. I have my enum set up in Rust like so:

#[derive(Debug, Serialize)]
pub struct Animal {
  animal_id: u8,
  animal_type: AnimalType,
}

#[repr(u8)]
#[derive(Debug, Serialize)]
pub enum AnimalType {
    #[serde(rename = "cat")]
    Cat = 1,
    #[serde(rename = "dog")]
    Dog = 2,
    #[serde(rename = "snake")]
    Snake = 3,
    #[serde(rename = "capybara")]
    Capybara = 4,
}

This works well with sqlx and I can create structs from the database and serve them to the web as e.g. { "animal_id": 1, "animal_type": "cat"}. But is there a "nice" way to serialise the AnimalType enum definition to give me something like this (the exact JSON format isn't set in stone) to populate a dropdown for creating new animals?

[
  { "cat": 1 },
  { "dog": 2 },
  { "snake": 3 },
  { "capybara": 4 }
]

I could maintain an AnimalType database table but then I'd have to add new animal types to the database and the enum. I'd like to have the data in one place if that's possible - either in the database or the Rust code.

What is the problem with this format?

This isn't serialization. For conversions of this type, you may want to look at the strum crate. It won't be direct. But you can at least make it work.
Edit: On second thought, it is pretty direct. Use the EnumVariantNames to get the enum variant names. Then its just a single map operation to convert case (if you want) and add the number. Or don't add the number, it is anyway the index (plus one).

Well, considering that the title of the question says "normalized", I wouldn't put structured data as a serialized string into a giant VARCHAR field. That's by no means normalized: it doesn't prevent duplication of identical entities, and it results in an effectively non-atomic field type, therefore it already doesn't conform to 1NF (and transitively to any other higher normal form).

The most normalized way of achieving effective "subtyping" or "sum-typing" is by splitting up individual cases into their own relational table, each having all relevant attributes and only the relevant attributes. There would then be a junction table, with the PK of each entity corresponding to exactly one of the case/variant tables. This could easily be enforced by a sum-of-not-nulls = 1 constraint in the junction table and several FK constraints between the junction table and the variant tables.

1 Like

There's no problem with the first format, it's what I want for displaying data in a table and works as-is. My problem is when I come to show the user a list of allowable values for AnimalType. strum's EnumVariantNames looks like it might do what I need, I'll check it out tomorrow. Thanks.

I'm familiar with the idea of normalised data, thanks. Obviously my question was badly worded - the point is that the Animal table has an animal_type_id which is a lookup of a Rust enum called AnimalType. I'm trying to work out how to get a list of available AnimalTypes in JSON to present a drop-down list of options to the users.

That doesn't seem to be related to the database, then. You probably want something like strum::VariantNames.

Solved using strum::VariantNames:

#[repr(u8)]
#[derive(Serialize, EnumVariantNames)]
pub enum AnimalType {
    #[serde(rename = "cat")]
    #[strum(serialize = "cat")]
    Cat = 1,
    #[serde(rename = "dog")]
    #[strum(serialize = "dog")]
    Dog = 2,
    #[serde(rename = "SNAKE")]
    #[strum(serialize = "SNAKE")]
    Snake = 3,
    #[serde(rename = "capybara")]
    #[strum(serialize = "capybara")]
    Capybara = 4,
}

async fn get_animal_types() -> Result<impl warp::Reply, warp::Rejection> {
    Ok(warp::reply::json(&AnimalType::VARIANTS))
}

Gives ["cat","dog","SNAKE","capybara"] which I can use to create selection options on the front end. The serde annotations aren't needed for this example but I'm also using them to generate lists of Animals with the overridden discriminant names.