Database flexible table design in Ecto?

Questions / Help
, ,
1

Hey folk!

I have a database table design dilemma. I want a flexible design for some of my table, say, for example, the user table and I want to store some other information in dedicated table (for example, information about the other or other metadata).

CREATE  TABLE users (
  id PRIMARY KEY,
  name STRING
);

CREATE TABLE user_info (
  user_id INTEGER,
  metadata STRING,
  FOREIGN KEY user_id REFERENCES users (id)
);

With this solution, I have lot of difficulty to insert a correct data, and retrieve information based on the user name or id. I was thinking to create an embedded schema to correct that, but, I don’t know if it’s a good idea.

Another solution is to create a table with a new column dedicated for the information

CREATE  TABLE users (
  id PRIMARY KEY,
  name STRING,
  info_id INTEGER,
  FOREIGN KEY info_id REFERENCES user_info (id)
);

CREATE TABLE user_info (
  id INTEGER,
  metadata STRING,
);

In this case, it’s seems a lot easier to use it with Ecto and Ecto.Repo.preload/3, it return a user map containing the User fields but also all information linked to him.

Finally, the last one, is to create an associative table for users and user_info.

CREATE TABLE users (
  id PRIMARY KEY,
  name STRING
);

CREATE TABLE users_info (
  id PRIMARY KEY,
  metadata STRING
);

CREATE TABLE users_info_association (
  user_id INTEGER,
  user_info_id INTEGER,
  FOREIGN KEY user_id REFERENCES users (id),
  FOREIGN KEY user_info_id REFERENCES users_info (id)
);

What could be the best way to do it for you and in particular with Ecto?

1 Like
2

I’m not really sure how those three options should affect ecto. It should be able to easily handle all three of those setups. Can you describe your difficulties?

1 Like
3

Yeah, for sure. I think its mostly about how to write the code to insert value easily without too much code. For example, if I want to retrieve information about the user in the second method, it already give me all the good information in one data-structure:

require Ecto.Query
q = Ecto.Query u in users
q |> Repo.all |> Repo.preload(:users_info)

Maybe I am wrong (I am not really good at designing database), but, it seems depending on the method, it will make my code harder to manage?

4 
user
|> cast(params, […])
|> cast_assoc(:users_info)
|> Repo.…

This should work for any assocation be it belongs_to, has_one has_one through or even many_to_many if you have a list.

1 Like