Array Data Type insert into MySQL

Question

While playing with theYelp Dataset using MySql I ran into an interesting issue, evidently i'd like to run some query's, and in the user.json file an "array" of data is described.

How can this "array" be stored in a MySql table?

According to MySql Reference Documentation for Data Type Storage there doesn't seem to be a data type that will give this result.

// array of strings, an array of the user's friend as user_ids

"friends":[ "wqoX...", "KUXL...", "6e9r..." ]

So far, i'm doing a simple model to focus on the User table, my creation statement:

create table Friends(
friends VARCHAR(255),
primary key (friends));

create table User(
userid VARCHAR(22),
primary key (userid),
friends VARCHAR(22),<--- user-id is 22 so friends-id is 22
foreign key (friends) references UserFriends (friends));

Some sample data for fun:

insert into User("abcde-ghijk-mnopq-stuv","NONE"); <- person1, no friends
insert into User("abcde-ghijk-mnopq-stuw","abcde-ghijk-mnopq-stuv"); <- person2, person1 friend

Perhaps to achieve the results I have to use a database normalization approach?

Barmar · Accepted Answer · 2018-11-09 21:07:09Z

1

The Friends table should contain foreign keys that refer to Users:

CREATE TABLE Friends (
    user1 VARCHAR(22),
    user2 VARCHAR(22),
    PRIMARY KEY (user1, user2),
    CONSTRAINT user1 REFERENCES User (userid),
    CONSTRAINT user2 REFERENCES User (userid)
);

A row in this table indicates that user1 and user2 are friends.

In general, you should avoid putting lists into table columns, this is a violation of 2nd Normal Form.

answered Nov 9, 2018 at 21:07

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EvOlaNdLuPiZ Over a year ago

is there an approach to achieve this using an "n" number of users as friends?

Barmar Over a year ago

You can, but it's not a good idea. See stackoverflow.com/questions/3653462/…

Collectives™ on Stack Overflow

Array Data Type insert into MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related