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"]
此对象实例的字符串标识符id:“banana”字符串键和值数据的字典:{}与这一个朋友相关的实例ID数组:[“apple”,“orange”]相关数组(in不同的方式)实例ID。粉丝:[“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.
您可能已推断出此数据将为Twitter网络建模。在我的数据生命周期中(将超出任何特定程序执行的生命周期),实例的数量和存储在字段中的数据将发生变化(预计会增长),因此我想将所有这些数据存储在MySQL中数据库由PHP脚本连接。
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?
我是数据库领域的一个完整的新手,但我有点理解表模型和查询结构。我应该如何为这个特定问题构建数据库?
1 个解决方案
#1
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.
在使用类似MySQL的数据库时,要记住的一件事是你应该避免在特定列中使用dicts和数组之类的东西。原因是这使得查询这些值非常可怕。
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.
我要做的是,例如,“朋友”模式有两列,FriendA和FriendB。对于您拥有的每个朋友对,这将是此数据库中的一行。您可以使用“关注者”执行相同操作,拥有“关注者”和“关注者”列。
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.
当然,除非您计划使用非结构化键/值数据,否则将其扩展为完整模式。
#1
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.
在使用类似MySQL的数据库时,要记住的一件事是你应该避免在特定列中使用dicts和数组之类的东西。原因是这使得查询这些值非常可怕。
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.
我要做的是,例如,“朋友”模式有两列,FriendA和FriendB。对于您拥有的每个朋友对,这将是此数据库中的一行。您可以使用“关注者”执行相同操作,拥有“关注者”和“关注者”列。
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.
当然,除非您计划使用非结构化键/值数据,否则将其扩展为完整模式。