探索数据建模（如何一起处理一个明智的数据库） [英] Exploring data modelling (how to hobble a sensible database together)

问题描述

我正在开发一个项目，人们可以在其中创建一个播放列表，并将其作为对象存储在localStorage中。

所以我现在想向前飞跃，建立一个用户登录系统（我可以使用php mysql和fb connect做到这一点或oauth系统，还有其他建议吗？）。问题是决定我是否为每个用户创建一个sql数据库并存储其播放列表（带有媒体信息），或者是否有其他解决方法。

我如何仅创建一个数据库，如下所示：

用户数据库--->一个包含{user（主键）的表传递someotherInfo}，然后每个USER表{包含播放列表），每个播放列表的第三个表（包含userID和媒体信息，可以是我的主键？）

示例：
i有10个注册用户，每个用户有2个播放列表

  1.table 1：10个条目
 2.table：用户名-播放列表（10个表）||我与一个字段用户组成一个表，另一个字段的播放列表名称为
 3.tables：每个播放列表-媒体信息，所有者（20张桌子）
  

还是有更简单的方法？

我希望我的问题很清楚。

PS：我是php和数据库的新手（所以这可能很愚蠢）

解决方案

出乎意料的是，大多数答案似乎都遗漏了这个问题，但是我会尝试一下；

这就是所谓的数据建模（您如何bble步数据库中的一堆表，以便以最好的方式表达您想要的内容），并且对询问不会感到愚蠢；有些人花了所有的时间来调整和设计数据模型。它们对任何系统的福祉都至关重要，实际上，它们对大多数人来说值得称赞。

听起来像您在正确的道路上。定义实体并为每个实体创建表格始终是一个很好的技巧，因此，在这种情况下，您将获得用户，播放列表和歌曲（例如）。从而定义您的表；用户，歌曲，播放列表。

接下来的事情是定义字段和表的名称（也许上面建议的简单名称很简单）。有些引入了虚假的命名空间（即MYAPP_USER而不是USER），特别是如果他们知道数据模型将来会在同一个数据库中扩展和扩展（或者，有些因为他们知道这是不可避免的），而另一些则只是深入研究

最大的问题永远是关于规范化以及与此相关的各种问题，即在性能与适用性之间取得平衡，并且有成千上万的关于该主题的书籍，因此我无法给您任何有意义的答案，但是对我而言，要旨是；

表中的数据字段在什么时候值得拥有自己的表？一个示例是，根据您希望如何拆分数据，您可以只使用一个表，两个表或6个表来创建应用程序。这是我认为您的问题真正出现的地方。

我会说您的假设非常正确，需要牢记的是一致的命名约定（关于如何命名标识符有很多意见）。对于您的应用程序（具有上述表格），我想做；

  USER {id，用户名，密码，名称， coffee_preference} 
歌曲{id，艺术家，专辑，标题，流派} 
 PLAYLIST {id，userid} 
 PLAYLIST_ITEM {id，songid，playlistid，songorder} 
  

现在您可以使用SQL来获取用户的所有播放列表；

 从播放列表中选择* userid = $ userid 
  

或获取所有歌曲在播放列表中；

  SELECT * FROM SONG，PLAYLIST_ITEM在哪里playlist_item.playlistid = $ playlist.id和song.id = playlist_item。 songid ORDER BY playlist_item.songorder 
  

等。再次，有关该主题的书目已被撰写。这是有关在草拟技术解决方案的同时进行清晰和语义思考的方法。而且有些人只是将其作为职业（例如DBA）。会有很多意见，尤其是关于我在这里写的内容。祝你好运。

i am working on a project in which people can create a playlist and its stored in localStorage as objects. everything is client side for the moment.

so i will now like to take a leap forward, make a user login system (i can do it using php mysql and fb connect or oauth system, any other suggestions?). the problem is deciding if i make a sql database for each user and store their playlist (with media info) or is there any other way to go around. will handling a large number of databases be a trouble for me(in terms of speed)?

how about i create only one db as follows:

user database ---> one table containing{ user(primary key) pass someotherInfo} , then tables per USER {contains playlists) , 3rd table per playlist (containing userID and media info, what could be my primary key?)

example: i have 10 registered user, each user has 2 playlists

1.table 1: 10 entries
2.table(s): username - playlists (10 tables) || i make one table with one field user other field playlist name
3.tables: each playlist - media info, owner (20 tables)

or is there a simpler way?

i hope my question is clear.

PS: i am new to php and database (so this might be very silly)

解决方案

Surprised most answers seems to have missed the question, but I'll give this a try;

This is called data modeling (how you hobble a bunch of tables in a database together in order to express what you want in the best possible way), and don't feel silly for asking; there are people out there who spend all their waking hours tweaking and designing data models. They are hugely important to the well-being of any system, and they are, in truth, far more important that most people give them credit for.

It sounds like you're on the right path. It's always a good tip to define your entities, and create a table per each, so in this case you've got users and playlists and songs (for example). Define your tables thusly; USER, SONG, PLAYLIST.

The next thing is defining the names of fields and tables (and perhaps the simplistic names suggested above are, well, simplistic). Some introduce faux namespaces (ie. MYAPP_USER instead of just USER), especially if they know the data model will extend and expand in the same database in the future (or, some because they know this is inevitable), while others will just ram through whatever they need.

The big question will always be about normalization and various problems around that, balancing performance against applicability, and there's tons and tons of books written on this subject, so no way for me to give you any meaningful answer, but the gist of it for me is;

At what point will a data field in a table be worthy of its own table? An example is that you could well create your application with only one table, or two, or 6 depending on how you wish to split your data. This is where I think your question really comes in.

I'd say you're pretty much correct in your assumptions, the thing to keep in mind is consistent naming conventions (and there's tons of opinions of how to name identifiers). For your application (with the tables mentioned above), I'd do ;

USER { id, username, password, name, coffee_preference } 
SONG { id, artist, album, title, genre } 
PLAYLIST { id, userid } 
PLAYLIST_ITEM { id, songid, playlistid, songorder }

Now you can use SQL you get all playlists for a user ;

   SELECT * FROM PLAYLIST WHERE userid=$userid

Or get all songs in a playlist ;

   SELECT * FROM SONG,PLAYLIST_ITEM WHERE playlist_item.playlistid=$playlist.id AND song.id=playlist_item.songid ORDER BY playlist_item.songorder

And so on. Again, tomes have been written about this subject. It's all about thinking clearly and semantically while jotting down a technical solution to it. And some people have only this as a career (like DBA's). There will be lots of opinions, especially on what I've written here. Good luck.

这篇关于探索数据建模（如何一起处理一个明智的数据库）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！