如何在sqlite for Android中实现一对多对象数据库 [英] How implement one-to-many objects database in sqlite for android

问题描述

我对 SQLite 和 SQL 还是很陌生，我正在努力解决以下问题:

I'm quite new to SQLite and SQL and I am struggling with how to approach the following:

我的应用程序将显示社区成员列表.如果单击成员，则可以看到成员发表的帖子列表.帖子是带有名称，时间和消息的对象.如何将其存储在 SQLite 数据库中，以便可以通过 userid 查询数据库并获取特定用户的帖子列表.

My app will display a list of community members. If I click a member, I can see a list of posts made by the members. A post is an object with name, time and message. How can I store this in an SQLite database so that I can query the database by userid and get the list of posts for a specific user.

我有一个包含以下列的Users表:

I have a Users table with these columns:

USER_ID | NAME

我有一个包含以下列的Tweet表:

I have a Tweet table with these columns:

USER_ID | NAME | TIME | MESSAGE

我的问题是: link 这两个表的最佳方法/结构是什么?我是为每个用户创建一个新的推文表，还是将所有推文存储在一个长表中，其中先包含用户1的推文，然后是用户2的推文，等等?

My questions are: what the best approach / structure to link these two tables? Do I create a new tweet table for every user, or do I store all tweets in one long table with tweets for user 1 first then for user 2 etc?

我不一定要寻找代码转储，而是对逻辑进行解释.

I'm not necessarily looking for code dumps but rather an explanation of the logic.

推荐答案

这是典型的加入"方案，在该方案中，用户和帖子之间存在一对多的关系.

This is a typical "JOIN" scenario where you have a one-to-many relationship between Users and Posts.

以下是显示所有用户及其帖子的查询示例:

Here is an example of a query that would display all users and their posts:

SELECT u.User_ID, u.Name, p.Time, p.Message
FROM Users u INNER JOIN Posts p ON u.User_ID = p.User_ID

这将产生一个包含四列的结果集.每个推文"都将显示其相关的用户记录."u"和"p".语法是用于使查询更易于阅读的表别名.

This will produce a resultset with four columns. Each "Tweet" will be displayed with its related User record. The 'u.' and 'p.' syntax are table aliases used to make the query easier to read.

这篇关于如何在sqlite for Android中实现一对多对象数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！