探索数据建模（如何将合理的数据库组合在一起）

发布于 2024-11-10 02:42:45 字数 720 浏览 3 评论 0原文

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

所以我现在想向前迈出一步，制作一个用户登录系统（我可以使用 php mysql 和 fb connect 或 oauth 系统来完成，还有其他建议吗？）。问题是决定我是否为每个用户创建一个 SQL 数据库并存储他们的播放列表（带有媒体信息），或者是否有其他方法可以解决。处理大量数据库会给我带来麻烦（就速度而言）？

我只创建一个数据库怎么样，如下所示：

用户数据库 --->一个表包含 { user(primary key) pass someotherInfo} ，然后每个 USER 表 {包含播放列表) ，每个播放列表第三个表（包含用户 ID 和媒体信息，我的主键是什么？）

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

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)

，或者有更简单的方法吗？

我希望我的问题很清楚。

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

原文

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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

铜锣湾横着走 2024-11-17 02:42:45

令人惊讶的是，大多数答案似乎都错过了这个问题，但我会尝试一下；

这就是所谓的数据建模（如何将数据库中的一堆表组合在一起，以便以尽可能最好的方式表达您想要的内容），并且不要觉得问这个问题很愚蠢；有些人把所有醒着的时间都花在调整和设计数据模型上。它们对于任何系统的健康都非常重要，而且事实上，它们比大多数人认为的要重要得多。

听起来你走在正确的道路上。定义实体并为每个实体创建一个表始终是一个很好的提示，因此在本例中您拥有用户、播放列表和歌曲（例如）。如此定义你的表格；用户、歌曲、播放列表。

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

最大的问题始终是关于规范化以及与之相关的各种问题，平衡性能与适用性，并且有关于这个主题的书籍有很多很多，所以我无法给你任何有意义的答案，但对我来说，它的要点是；

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

我想说你的假设非常正确，要记住的是一致的命名约定（并且关于如何命名标识符有很多意见）。对于您的应用程序（使用上面提到的表格），我会这样做；

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

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

   SELECT * FROM PLAYLIST WHERE userid=$userid

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

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

等等。再次，关于这个主题已经有很多著作了。这一切都是为了在记下技术解决方案的同时进行清晰的语义思考。有些人只将此作为职业（例如 DBA）。会有很多意见，特别是关于我在这里写的内容。祝你好运。

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.

回复收藏 0 原文