CODEWITHSUNDEEP
mysql
EvOlaNdLuPiZ
EvOlaNdLuPiZ

Reputation: 600

Array Data Type insert into MySQL

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?

Upvotes: 0

Views: 38

Answers (1)

Barmar
Barmar

Reputation: 780818

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.

Upvotes: 1

Related Questions