尝试设计/建模简单的数据库应用程序
我正在尝试了解有关数据库交互的更多信息,这让我开发了一个本地应用程序来开始。 好吧,基本上,到目前为止我所做的结果有些好坏参半,而且我改变了很多东西,我什至不确定现在我改变了什么,哈哈。 我不太确定我的一张桌子是否正确,所以我决定重新开始。 这就是我想要我的愚蠢的本地应用程序执行的操作。
- 最多存储 9 个特定的 RSS 提要(我之前做过 url/链接,但我不想被之前所做的任何事情弄糊涂,所以我将其更改为 RSS 提要)
- 默认情况下将填充 1 个提要(因此每个用户都有一个常见的提要 - 他们可以更改)
- 提要应该存储在某种排序方案中,以便可以按照输入的相同顺序检索/打印它们。
将有一个包含 9 个文本字段的编辑屏幕,由相应的数据库条目填充,所以类似于:
feed 1: <input type="text" value="http://rss.news.yahoo.com/rss/topstories"> **the default feed for everyone, but they can change it**
feed 2: <input type="text" value="http://content.usatoday.com">
feed 3: <input type="text" value="http://newsrss.bbc.co.uk/rss/newsonl.../world/rss.xml">
feed 4: <input type="text" value="">
feed 5: <input type="text" value="">
feed 6: <input type="text" value="">
feed 7: <input type="text" value="">
feed 8: <input type="text" value="">
feed 9: <input type="text" value="">
<input type="submit" value="update">
我希望能够在此处编辑/添加新的提要并以相同的顺序检索这些提要 - 这是我在之前的尝试中感到困惑的一个重要根源。
将有一个输出屏幕,以相同的顺序输出提要 URL。
我有 2 个表,users 和现在 feeds,我相信我的 users 表没问题,它基本上存储了一些个人信息。 我认为那里的一切都应该很明显。 “state”列将存储来自选择/下拉列表的 2 个字符的州缩写,并且我已将其编入索引,因为我希望能够按州搜索用户。 我在检索/编辑/更新该数据时没有任何问题。
CREATE TABLE users (
user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
state CHAR(2) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(32) NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY(user_id),
UNIQUE (email),
INDEX login (email, pass),
INDEX state (state)
);
这是我的新 feeds 表
CREATE TABLE feeds (
user_id SMALLINT UNSIGNED NOT NULL,
feed_url VARCHAR(255) NOT NULL DEFAULT 'http://rss.news.yahoo.com/rss/topstories',
feed_id SMALLINT UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY(user_id, feed_url)
);
当用户输入新的 feed 时,假设 feed #2 的值将被插入到 feed_url 中,而 feed_id 将被插入值为 2。 如果输入 feed #3,则 feed_id 将插入值为 3。 这应该给我一些 ORDER BY 来按顺序检索记录,对吗?
数据编辑屏幕应始终显示提要的输入方式。
数据输出屏幕应该始终显示提要的输入方式。
那么,这看起来正确吗?我是否遗漏了一些东西? 我的 feed_url VARCHAR(255) 可能不是万无一失的,但我只会用短网址进行测试。 它也总是可以很容易地被提升。
I'm trying to learn more about db interactions which has me developing a local app to get started. Well, basically, what I've done so far has had some mixed results and I've changed so much stuff I'm not even sure what I've change at this point, lol. I'm not quite sure one of my tables was correct, so I've decided to just start that over. Here's what I want my silly local app to do.
- Store up to 9 specific RSS feeds (I did url/links before but I don't want to get confused by anything I did before so I'm changing it to RSS feeds)
- 1 feed will be populated by default (so every user has that one common feed - which they can change)
- Feeds should be stored in some ordering scheme so they can be retrieved/printed in the same order they were entered in.
There will be an edit screen with 9 text fields, populated by corresponding db entries, so something like:
feed 1: <input type="text" value="http://rss.news.yahoo.com/rss/topstories"> **the default feed for everyone, but they can change it**
feed 2: <input type="text" value="http://content.usatoday.com">
feed 3: <input type="text" value="http://newsrss.bbc.co.uk/rss/newsonl.../world/rss.xml">
feed 4: <input type="text" value="">
feed 5: <input type="text" value="">
feed 6: <input type="text" value="">
feed 7: <input type="text" value="">
feed 8: <input type="text" value="">
feed 9: <input type="text" value="">
<input type="submit" value="update">
I want to be able to edit/add new feeds here and retrieve those feeds in the same order - this was a big source of my confusion in my prior attempt.
There will be an output screen which outputs the feed URLs in the same order.
I have 2 tables, users and now feeds, I believe my users table is fine, it basically stores a little personal information. I think everthing there should be pretty obvious. The 'state' column is going to store a 2-character state abbreviation from a select/dropdown and I've indexed it as I'd like to be able to search for users by state. I'm not having any issues retrieving/editing/updating that data.
CREATE TABLE users (
user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
state CHAR(2) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(32) NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY(user_id),
UNIQUE (email),
INDEX login (email, pass),
INDEX state (state)
);
Here's my new feeds table
CREATE TABLE feeds (
user_id SMALLINT UNSIGNED NOT NULL,
feed_url VARCHAR(255) NOT NULL DEFAULT 'http://rss.news.yahoo.com/rss/topstories',
feed_id SMALLINT UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY(user_id, feed_url)
);
When a user enters a new feed, let's say for feed #2, the value would be inserted into feed_url and feed_id would be inserted with a value of 2. If feed #3 is entered, feed_id would be inserted with a value of 3. That should give me something to ORDER BY to retrieve records in order, right?
The data edit screen should always display the feeds how they were entered.
The data output screen should always display the feeds how they were entered.
So, does this look proper and am I missing some things? My feed_url VARCHAR(255) might not be foolproof but I'm only going to be testing with short urls. It could always be easily bumped up, too.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
由于您的 Feed 表将保存所有用户的 Feed,因此您可能需要重新考虑这一点。
您已将 feed_id 的默认值设置为 1,因此所有内容都会得到 1 - 为用户添加第二个 feed 时不会得到 2。 你需要逻辑。
也许当用户创建新的 feed 时,您会查询他们的所有记录,找到最高的 feed id,然后向其添加 1。 然后您可以将其用作新的 feed ID。
要找到你的最高id,你会做类似的事情(语法可能是错误的,我通常是T-SQL)
select max(feed_id) from feeds where user_id = @user_id
我从来没有做过任何事情,让用户订购物品,所以你在做出任何决定之前可能想调查一下。
Since your feed table will be holding feeds for all your users, you might need to rethink this.
You have set the default to 1 for the feed_id, so you will get 1 for everything - you will not get 2 when adding a second feed for a user. You will need logic.
Perhaps when a user creates a new feed, you would query for all their records, find the highest feed id and then add 1 to it. Then you would use that for your new feed id.
To find your highest id you would do something like(syntax may be wrong, I usually to T-SQL)
select max(feed_id) from feeds where user_id = @user_id
I've never done anything, letting the user order items, so you might want to look into that before deciding on anything.
忘记上一篇文章 - 我以为你需要能够重新排序你的提要。 如果没有,您只需将 auto_increment 添加到该 feed-id 字段即可。 然后使用 ORDER BY feed-id 升序。
Forget that last post - I was thinking you needed to be able to re-order your feeds. If not, you just need to add auto_increment to that feed-id field. Then use ORDER BY feed-id ascending.
为了强制每个用户都有一个可以更改的初始默认 Feed,我建议您删除
feeds
表中的默认值,并在应用程序代码中实现该功能。 在创建(注册)新用户的代码中,将该行添加到users
表中,然后将一行添加到包含该用户的第一个 Feed 的feeds
表中使用默认 URL(您可以将其存储在应用程序配置设置中)。如果您必须维持提要的顺序,那么有两个主要选项。 使用您的
feed_id
列,但将其填充为自动增量。 但是,在某些情况下这可能会中断(不能保证始终增加)。 另一个选项是将feed_id
更改为feed_sequence
并从代码中填充它,使每个用户始终为 1-9。我发现您不惜一切代价保留 feed URL 顺序的要求是非常不典型的。 为什么需要它,为什么它如此重要?
我注意到您的
users
表存在一些问题。 其一,email
字段不够大(60 太小了,我认为 320 才是真正的最大值)。 另一方面,我想知道您是否将pass
存储为纯文本,这将是一个巨大的安全问题。 我还注意到pass
被声明为CHAR
而不是VARCHAR
。 您没有说,但是您使用CHAR
是否表明您没有将密码存储为明文,而是将其混淆到固定大小 (32) 字段中?当然,提要 URL 也很容易超过 255 个字符。
很高兴看到您已经合理地标准化了数据,例如不执行
feedUrl1
...feedUrl9
列。最好的祝愿。
To enforce the feature that every user has an initial default feed that they can change, I recommend that you remove the defaults in the
feeds
table and implement the feature in your application code. In your code to create (register) a new user, add the row to theusers
table, then add a row to thefeeds
table that contains the first feed for this user with the default URL (which you might store in an application configuration setting).If you must maintain the order of the feeds, then there are two primary options. Use your
feed_id
column, but populate it as an auto-increment. However, that can break under certain scenarios (it is not guaranteed to always be increasing). The other option is to changefeed_id
tofeed_sequence
and populate it from your code to always be 1-9 for each user.I find your requirement to preserve the order of the feed URLs, apparently at all costs, to be quite atypical. Why is that needed, and why is it so overwhelmingly important?
I noticed a couple of issues with your
users
table. For one, theemail
field is not big enough (60 is way to small, 320 is the real maximum I think). For another, I wonder whether you storepass
as plain text, which would be a massive security issue. I also noticed thatpass
is declared asCHAR
rather thanVARCHAR
. You don't say, but does your use ofCHAR
indicate that you are NOT storing the password as plaintext, but are instead obfuscating it into a fixed-size (32) field?Of course, the feed URLs could easily exceed 255 characters as well.
It is good to see that you have reasonably normalized your data, such as by NOT doing
feedUrl1
...feedUrl9
columns.Best wishes.