I am trying to explore db interactions that has me creating a local application to obtain began. Well, essentially, what I have done to date has already established some mixed results and I have transformed a lot stuff I am not really sure what I have change at this time, lol. I am less than sure among my tables was correct, so I have made the decision to simply start that more than. This is what I would like my silly local application to complete.

  • Store as much as 9 specific Nourishes (Used to do url/links before but I'd rather not get confused by anything Used to do before so I am altering it to Nourishes)
  • 1 feed is going to be populated automatically (so every user has that certain common feed - that they can alter)
  • Feeds ought to be saved in certain ordering plan to allow them to be retrieved/printed within the same order these were joined in.

You will see an edit screen with 9 text fields, populated by corresponding db records, so something similar to:

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 wish to have the ability to edit/add new feeds here and retrieve individuals feeds within the same order - it was a large supply of my confusion during my prior attempt.

You will see an output screen which results the feed Web addresses within the same order.

I've 2 tables, customers and today feeds, In my opinion my customers table is okay, it essentially stores just a little private information. I believe everthing there must be pretty apparent. The 'state' column will store a couple-character condition abbreviation from the choose/dropdown and I have indexed it as being Let me have the ability to look for customers by condition. I am not getting any issues locating/editing/upgrading 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)                                                                          
);

Whenever a user makes its way into a brand new feed, let us say for feed #2, the worthiness could be placed into feed_url and feed_id could be placed having a worth of 2. If feed #3 is joined, feed_id could be placed having a worth of 3. Which should produce something to buy BY to retrieve records so as, right?

The information edit screen must always display the feeds the way they were joined.
The information output screen must always display the feeds the way they were joined.

So, performs this look proper and shall we be held missing several things? My feed_url VARCHAR(255) is probably not foolproof but I am only likely to be testing with short web addresses. It might continually be easily knocked up, too.

As your feed table is going to be holding feeds for your customers, you will need to re-think this.

You've set the default to at least one for that feed_id, so you're going to get 1 for everything - you won't get 2 when adding another feed for any user. You'll need logic.

Possibly whenever a user produces a brand new feed, you'd query for those their records, discover the greatest feed id after which add 1 into it. You would then use that for the new feed id.

To locate your greatest id you'd make a move like(syntax might be wrong, It's my job to to T-SQL)

choose max(feed_id) from feeds where user_id = @user_id

I have never done anything, letting the consumer order products, so you might like to consider that before determining on anything.

Forget that last publish - I believed you possessed to have the ability to re-order your feeds. Otherwise, you need to simply add auto_increment to that particular feed-id area. Then use ORDER BY feed-id climbing.