From Domain types to SQL tables

In my last post I created a simple domain we could use to learn about SQL in the F# ecosystem.
Today I want to design our tables!

Designing our tables

Record table ❌

type Record = { Wins: uint; Losses: uint }

From the domain this is the simplest table to create. But is it needed?
In our case probably not as the Record type is only ever associated to a PokemonTrainer so let's avoid uneeded complexity!

Pokemon trainer 💪

When starting out with SQL I found it really helpful to use Visual Studio's designer, as I was able to learn the different data types available and think about how they would be represented in F#.

For example:

F#/dotnet SQL string nvarchar/nchar bool bit Guid uniqueidentifier

Let's step through the PokemonTrainer to see how we could translate this to SQL:

type PokemonTrainer =
    { Id : PokemonTrainerId
      Name : string
      Record: Record
      Pokemon : Pokemon list }

1. Id : uniqueidentifier

   On inserting data we would have to unwrap our Single Cased Union, and re-wrap the Guid when reading it back out again.

2. Name : nvarchar

3. Record: Instead of having another table we'll flatten the structure to Wins and Losses

   On inserting data we would have to flatten our Record type, and on reading we would have to recreate it.

4. Pokemon: As a PokemonTrainer can have many Pokemon this is a One-to-Many relationship. This means our Pokemon table will have a PokemonTrainerId column which will link to PokemonTrainer.Id

Therefore our PokemonTrainer table currently looks like:

CREATE TABLE [dbo].[PokemonTrainer]
(
    [Id] uniqueidentifier PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL,
    [Wins] INT NOT NULL,
    [Losses] INT NOT NULL
)

Pokemon 🐉

Moving on to our Pokemon type.

type Pokemon =
    { PokeIndex : PokeIndex
      Name : string
      Level: int
      EvolutionName: string option
      PokemonTypes: PokemonType list }

1. PokeIndex : uniqueidentifier
2. Name : nvarchar
3. Level: int
4. EvolutionName : nullable nvarchar
5. PokemonTypes : This is the most complicated relationship. A Pokemon can have many PokemonTypes but a PokemonType can be associated to many Pokemon. So we have a Many-to-Many relationship! In order to handle this we will need to create a third intermediary table.

So our Pokemon table should look like:

CREATE TABLE [dbo].[Pokemon]
(
    [PokeIndex] INT NOT NULL PRIMARY KEY,
    [Name] NVARCHAR(50) NOT NULL,
    [EvolutionName] NVARCHAR(50),
    [PokemonTrainerId] INT NOT NULL,
    CONSTRAINT [FK_Pokemon_To_PokemonTrainer] FOREIGN KEY ([PokemonTrainerId]) REFERENCES [PokemonTrainer]([Id])
)

Remember that PokemonTrainerId is used to rebuild our trainer's Pokemon due to the One-to-Many relationship defined earlier.

PokemonTypes ⚡

We need a PokemonType table that will have all the cases of our Discriminated Union.

type PokemonType =
    | Rock
    | Grass
    | Poison
    | Fire
    | Psychic
    | Ghost
    | Ice

CREATE TABLE [dbo].[PokemonTypes]
(
    [PokemonTypeId] INT NOT NULL PRIMARY KEY,
    [PokemonTypeName] NVARCHAR(50) NOT NULL,
)

Think of this as a key value pair where each type like Fire has an associated integer value 4. That way if we realise Fire should be actually be called Flame we only need to update the PokemonTypeName.

With that we can get back to our intermediary Table PokemonType_Pokemon.

PokemonType_Pokemon ⚡🐲

This kind of table is called a 'junction table' (or sometimes 'link table'). It helps model Many-to-Many relationships.

CREATE TABLE [dbo].[PokemonType_Pokemon]
(
    [PokemonTypeId] INT NOT NULL,
    [PokeIndex] INT NOT NULL,
    CONSTRAINT [FK_PokemonType_Pokemon_To_PokemonTypes] FOREIGN KEY ([PokemonTypeId]) REFERENCES [PokemonTypes]([PokemonTypeId]),
    CONSTRAINT [FK_PokemonType_Pokemon_To_Pokemon] FOREIGN KEY ([PokeIndex]) REFERENCES [Pokemon]([PokeIndex])
)

The constraints are the most important aspect to this table. They create the links between each part of the Many-to-Many. For example:

• Bulbasaur(PokeIndex = 1) is both Grass and Poison
• In our above table we will see two entries with PokeIndex 1:
  • One entry with the PokemonTypeId = 2(Grass)
  • The other with PokemonTypeId = 3(Poison)

With that we should have all the tables and relationships we need!

Next time! 🚀

In the next blog I'll go through how we can insert and query data using plain old ADO.NET! 😎