Nested table in mysql

Question

I have 2 tables. USER with all personal data and TWEETTABLE with all tweets from the user timeline obtained via twitter api

User table

id|twitter_user_name|firstname|lastname|email ----------------------------------------------- 1 | @johnsmith |john |smith | [e-mail] 

tweettable

 name|scr_name|tweets |created_at_ |followers_count ---------------------------------------------------------- CNN |CNN |*tweet text*| 14nov 12:32| 38713286 

I really want:

  id| twitter_details | -----|------------------------------------------------------- | |name |scr_name|tweets |created_at_ |followers_count| | ----|--------|------------|------------|-------------- 1 | CNN|CNN |*tweet text*| 14nov 12:32| 38713286 | ----|------------------------------------------------------ 2 | BBC|BBC |*tweet text*| 14Nov 16:43| 38713254 | ----|-----|--------|------------|------------|---------------|------ 3 

i.e. a table inside a column. Column 1 will have a user ID and a second column with all its corresponding timeline rows. I can do this in oracle by doing the following:

 CREATE TYPE tweet_typ AS OBJECT ( name VARCHAR2(20), scr_name VARCHAR2(25), tweets text, created_at timestamp; followers count VARCHAR2(25) ) MEMBER PROCEDURE display_details (SELF IN OUT twitter_typ ) ); 

and use this inside the twitter_details column as a nested table

 CREATE TABLE TWEETTABLE ( id INT, twitter_details tweet_typ ); 

And then insert

  INSERT INTO TWEETTABLE VALUES ( 1,twitter_typ (--extracted values goes here--)); 

But Mysql does not allow nested tables. So how can I define twitter as an object and make it a nested table inside the twitter_details column in mysql?

Answer 1

You cannot do this, but you can do as everyone does. Just identify

 tweettable 

a

 tweetID | userID |name|scr_name|tweets |created_at_ |followers_count ---------------------------------------------------------- 1 1 CNN |CNN |*tweet text*| 14nov 12:32| 38713286 

you add 2 columns. One (maybe autoinc) tweetID and one user ID associated with the user who hid.

Then you get your result with joining

 select * from user u join tweettable t where u.id = t.userID; 

Answer 2

Better use a crosstab. For example, "users_tweets". table structure:

 user_id | tweed_id 

Where user_id is the id of 'user_table' and 'tweet_id', this is the 'id' of tweettable

or

 user_id | tweet_name 

Another way is to convert twitter_details to string:

 $strDetails = json_encode($twitter_details) 

and paste this $strDetails line into the database