Trying to design/model simple database app

Question

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"&gt; **the default feed for everyone, but they can change it**
feed 2: <input type="text" value="http://content.usatoday.com"&gt;
feed 3: <input type="text" value="http://newsrss.bbc.co.uk/rss/newsonl.../world/rss.xml"&gt;
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.

Answer 1

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.

Answer 2

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.

Answer 3

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 the users table, then add a row to the feeds 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 change feed_id to feed_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, the email field is not big enough (60 is way to small, 320 is the real maximum I think). For another, I wonder whether you store pass as plain text, which would be a massive security issue. I also noticed that pass is declared as CHAR rather than VARCHAR. You don't say, but does your use of CHAR 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.