0

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?

EvOlaNdLuPiZ
  • 600
  • 1
  • 4
  • 21

1 Answers1

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • is there an approach to achieve this using an "n" number of users as friends? – EvOlaNdLuPiZ Nov 09 '18 at 21:50
  • 1
    You can, but it's not a good idea. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Barmar Nov 09 '18 at 22:14