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?