Database that have a column that can have multiple type of data

Hi guys. I have a question regarding database and rendering the data

I have a table called ‘vehicles’
id | type | car_id | motorcycle_id | truck_id

defmodule Hello.Repo.Migrations.CreateVehicle do
use Ecto.Migration

def change do
create table(:vehicles) do
add :type, int
add :car_id, references(:cars)
add :motorcycle_id, references(:motorcycles)
add :truck_id, references(:trucks)

Car, motorcycle and truck have different properties

But it does not seems to be a good way to do it

How can I make the table to only have a type_id where it will show the id for each type of car or motorcycle or truck and how to render it?

Json example:
[ { "id":1, "type":"car", "car":{ "brand":"Toyota", "number_of_seat":4 } }, { "id":1, "type":"motorcycle", "motorcycle":{ "brand":"Yamaha", "some_property":2 } }, { "id":1, "type":"truck", "truck":{ "brand":"Truck brand", "load_capacity":10000 } } ]

Postgres has a feature called table inheritance but has too many caveats to be worthwhile for most use cases I’ve thought of applying it to.

Ecto offers a few other possibilities. One is to use the map type to store the properties, which is stored in Postgres as a JSONB field, but Ecto represents it as a regular map to all of your code. Querying externally using Postgres’s json functions is still quite manageable, but if you have to write a lot of external reports it might be best to stick to tables.

If you do that, I’d make this relationship a has_one/belongs_to. So instead of a truck_id in vehicles you have a vehicle_id in trucks, cars, etc. The has_one in vehicles can be 0 or 1, so you’d have nils (in your struct) for the types that don’t apply to a particular vehicle.

1 Like