Grow your CSS skills. Land your dream job.

How to setup a database table?

  • # June 30, 2013 at 7:26 pm

    > I would, so if you wanted to add extra info about the user down the road that data will be abstracted.

    That’s a developers perk. Solve problems that don’t even exist yet :) You can add a date column as well, so you know when you’ve read/watched something.

    Go with `utf8_unicode_ci`, because we are using `utf8` almost everywhere already anyway it makes sense to store data as `utf8`. There is something on stackoverflow about `utf8_unicode_ci` vs `utf8_general_ci`, comes down to that `unicode` is more precise. `ci` just means case insensitive.

    # June 30, 2013 at 7:31 pm

    @CrocoDillon Just read that! Thanks for the info.

    I really appreciate all the information everyone has given me. Thanks for helping out and teaching me a few things.

    # June 30, 2013 at 7:34 pm

    Yeah ignore that part I quickly generated it here on my machine. Go with whatever you need.

    # June 30, 2013 at 7:41 pm

    @Alenabdula Ok thanks. Now the fun starts. Inserting the array strings into the database.

    # June 30, 2013 at 7:56 pm

    Just as extra food for thought -

    Is every URL you store going to be unique? If not, then will every URL+title combination be unique?

    What I’m getting at is that, while every table should have an index, that index doesn’t _need_ to be artificial. “Natural” indexes are preferable, in fact, while also bringing the performance benefits @CrocoDillon mentioned.

    **If** every URL+title combination will be unique, do
    `PRIMARY KEY( url,title )`
    and drop the `id` column.

    You _might_ want to index the `title` column (separately) also, if you ever plan on searching by title without the URL.

    Otherwise, **if** every URL will be unique, do
    `PRIMARY KEY( url )`
    and drop the `id` column.

    You _might_ want to index the `title` column also, if you ever plan on searching by title.

    Otherwise, keep your `id` column as the PK.

    # June 30, 2013 at 8:34 pm

    @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.

    I don’t ever plan on using any of the data to be searched. I only plan to list it as shown here.

    So should I keep my table as is (including `id`)?

    # June 30, 2013 at 11:15 pm

    that’s what I thought – if `url`, `title`, or combination are unique values, then use one, the other, or both as your PK*. You don’t need the `id` column. It’s just extra overhead to maintain.

    *I’d go with both, since you’re likely to be querying for both (correct?). If you do
    `PRIMARY KEY( url,title )`

    and make sure you always query like
    `SELECT url,title …`

    (meaning, _not like_ `SELECT title,url …`), then you’ll be able to read from the index (_FAST!!!_) and never have to touch the table itself.

    # June 30, 2013 at 11:31 pm

    @traq What’s the difference between

    `SELECT url, title…` and `SELECT title, url…`?

    # June 30, 2013 at 11:35 pm

    `SELECT title,url` can’t use a PK index created on `url,title`. It will have to actually access and read the rows in the table itself (which is much slower than just reading the index).

    `SELECT url,title …` (or even just `SELECT url …` (but not `SELECT title …`)) can get everything it needs from that index and bypass reading the table entirely.

    # June 30, 2013 at 11:38 pm

    @traq Even if I set the primary key to both?

    # June 30, 2013 at 11:44 pm

    >Even if I set the primary key to both?

    yes, order is important with compound keys.

    _(I edited my post above to clarify.)_

    You _could_ index each column separately (`PRIMARY` on one, `UNIQUE` on the other) if you wanted to, but if you’ll [almost] always be reading _both_, I’d recommend the compound PK.

    # June 30, 2013 at 11:47 pm

    @traq Ha. Quite hard to grasp when you’re just learning this. I think I’ll have to do some research to understand some of it.

    # June 30, 2013 at 11:55 pm

    I “plateaued” for a long time with SQL. Like, _five years_ of mediocre SELECT statements and not much else. It’s only in the last six months, really, that I’ve had an explosion of understanding… and I still have a long ways to go.

    # July 1, 2013 at 12:26 am

    @traq That’s reassuring.

    # July 1, 2013 at 12:36 am

    @ChrisBurton Not sure if you’re being sarcastic or not.

    : p

Viewing 15 posts - 16 through 30 (of 60 total)

You must be logged in to reply to this topic.

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