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?