Grow your CSS skills. Land your dream job.

How to setup a database table?

  • # July 1, 2013 at 9:06 pm

    @chrisburton, If this is the case you going about it all wrong. Your table violates 2 NF and that is all about redundancy. If you read and want to record more than one title(article) which is published under same URL you are in big trouble because you will have to repeat URL in each record which precisely violates 2NF. On top of all that you will not be able to enter URL if that attribute is indexed with no duplicates allowed. Therefore you should develop additional table (entity) tblTitles with TitlePK, URLID as FK and attribute URLTitle. That way you preserve uniqueness of URL and articles will be child records of URL Entity. And please, use surrogate keys as your primary keys. Let the system handle it instead using natural keys. After all they are not for human consumption but to assure DB integrity. HTH.

    # July 1, 2013 at 9:13 pm

    > If you read and want to record more than one title(article) which is published under same URL – @Jurotek

    Do multiple titles usually occur under one article?

    # July 1, 2013 at 9:21 pm

    @chrisburton, There can be more than one article (title) published under the same URL. That’s what I was referring to. That’s why I said that your present table would be in violation of 2NF. But it is more complex than that. Sometimes the article can start with just a few lines of text which follows with Read more … where the entire article appears in full under different URL.

    # July 1, 2013 at 9:28 pm

    @jurotek That shouldn’t be an issue, then. I only save articles to the feed that link directly to the article. Not on homepages with `read more…` links.

    Let’s use the CSS-Tricks homepage for example:

    I click on an article which directs me to a new page(URL), I read it and save it to my Readability list.
    The URL and Title is then saved into my feed from Readability which I use PHP to grab.

    Here’s a demo:

    https://vimeo.com/69105243

    __
    # July 1, 2013 at 9:39 pm

    >Your table violates 2 NF and that is all about redundancy. If you read and want to record more than one title(article) which is published under same URL

    @jurotek, note that we discussed this earlier:

    >will every URL+title combination be unique?
    @traq

    .
    >Every URL and Title will be unique. The only way that they wouldn’t be is if I read the same article (title) from the same website (url) continuously. But Readability does not allow duplicate articles so that would be impossible.
    @ChrisBurton

    The table, within the scope of its definition and usage, is perfectly normalized: the title is directly dependent on the url (one-to-one relationship), and no urls or titles will ever be duplicated.

    # July 1, 2013 at 9:42 pm

    @chrisburton, Let’s hope that this is always the case like this and that there’s no more that one full article under one URL you want to record, otherwise you must set up child records in related table on the many side of relationship to a parent which would be URL entity. If you sure what you saying then title (article) attribute in same table with URL attribute would suffice. Just make sure that this is always going to be the case.

    # July 1, 2013 at 9:47 pm

    Thanks @traq for mentioning my previous statement. I completely forgot about it.

    @jurotek Generally, articles that have more than one page, usually have URL changes to match. Such as an article that is 5 pages long, usually will have`/title/page?=2`, `/title/page/2`, etc. This is to allow people to link to them. But I understand what you’re saying and I appreciate the info.

    # July 1, 2013 at 10:02 pm

    @chrisburton, I know what you saying. In a real life if I was going to set up DB like this here’s how I would go about it. In your case tho is prolly going little overboard but I’ll show you anyways:

    tblURL
    URLID pk
    URLDesc

    tblArticles
    ArticleID pk
    URLID fk
    ArticleCategoryID fk
    ArticleName
    ArticleDate

    tblArticleCategories
    ArticleCategoryID pk
    ArticleCategory

    That way I could query data by article category also

    tblPages
    PageID pk
    ArticleID fk
    PageNumber
    PageURL

    __
    # July 1, 2013 at 10:04 pm

    >Just make sure that this is always going to be the case.

    The info is retrieved from [an API](http://readability.com/developers/api/reader) which enforces the 1-to-1 relationship. I don’t know if that was mentioned, specifically, in _this_ conversation.

    # July 1, 2013 at 10:21 pm

    @traq I’m not technically using an API here. Just grabbing this data: http://readability.com/christopherburton/latest/feed

    PHP:

    $xml = simplexml_load_file(“http://readability.com/christopherburton/latest/feed”);
    $json = json_encode($xml);
    $array = json_decode($json,TRUE);

    __
    # July 1, 2013 at 10:28 pm

    that’s an API. It’s something you can query to get specific info from the app.

    # July 1, 2013 at 10:31 pm

    @traq, that’s true if you have URL -> article. If you went the other way and have article -> URL the 1:1 would no longer apply because same article can appear under more than one (different) URL I suppose and therefore you’d have 1:M.

    # July 1, 2013 at 10:32 pm

    @traq Ah, gotcha. I’m going to tag you in a new discussion as I’m having issues with inserting data into the database.

    __
    # July 1, 2013 at 10:42 pm

    @jurotek, yes, I agree that this DB would not be suited to 1:M relationships. That’s why I originally asked chris in the first place. But this is basically a wrapper for an established service that already does things this way on purpose: I think it’s safe to rely on it.

    >I’m going to tag you in a new discussion as I’m having issues with inserting data into the database.

    Don’t bother ; )

    kidding. I don’t get notifications. (Don’t like them.)
    I try to browse everything, though, and seeing names catches my attention.

    # July 1, 2013 at 10:54 pm

    @traq, I agree with you. In this case given the real life constraints and or user requirement the one table solution is sufficient. Just wanted to show some other ways to do it if more information was required.

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".