What Database model should I use to store this data format?

Question

I basically have a bunch of objects with the fields;

a string identifier of this object instance id : "banana"
a dictionary of string keys and values data : {}
an array of ids of instances related to this one friends: ["apple", "orange" ]
an array of related (in a different way) instance ids. followers: ["grapefuit"]

You may have deduced this data will model a Twitter network.
Over the life of my data (which will extend beyond the life of any particular program execution), the number of instances and the data stored in the fields will change (expectedly grow), so I would like to store all this data in a MySQL database to be interfaced by a PHP script.

I'm a complete rookie in the world of databases, but I somewhat understand the table model and query structure.
How should I structure the database for this particular problem?

please, you need to explain more, what are the entities that you want to store their information: friends, follwers, ? — mostafa khansa
– mostafa khansa, Commented Sep 5, 2013 at 7:06

William Gaul · Accepted Answer · 2013-09-05 07:07:09Z

1

In working with a MySQL-like database, one thing to remember is that you should avoid having things like dicts and arrays in a particular column. The reason is that this makes querying on these values horrible.

What I would do is have, for example, a "Friends" schema with two columns, FriendA and FriendB. For every friend pair you have, this will be a row in this database. You can do the same with with "Followers", have a Follower and a Followee column.

Now querying across these tables just requires a join, and more importantly if two friends get unfriended or someone decides to unfollow someone else, this is just one delete instead of two (and no arrays!)

And of course, unless you're planning on having unstructured key/value data, expand that to a full schema.

answered Sep 5, 2013 at 7:07

William Gaul

3,1812 gold badges18 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

What Database model should I use to store this data format?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related