How to setup a database table?

[__]

no, not at all – I actually meant that statement to be encouraging to you.

I think the biggest obstacle to learning SQL is that there are very few [good] resources about _how a database works_. In contrast, there are many, many [lousy] resources about _how to use them_.

[chrisburton]

@traq You know, after trying to find a decent article that explains indexing well (apparently there’s two types), I would agree with you. It’s incredibly complicated and intimidating to those just starting out. I feel like what I’ve read so far is more for those that already have an understanding of it.

Therefore, (since I’m paranoid about security) this looks correct to you? Both `title` and `url` are set to be the primary key.

[Alen]

> @traq, learned something new, didn’t know you could read compound keys from the index table like that, cool :)

> performance… you won’t see that much difference anyway so don’t break your head over it

+1.

[__]

>Therefore, (since I’m paranoid about security) this looks correct to you?

As @Crocodillon said, it’s not a security issue (and the performance issue isn’t very big either; I’m just dealing with it a lot right now, so I’m oriented that way).

As far as “correct,” umm… I dunno. Probably. In phpMyAdmin’s [SQL] tab, do
`SHOW CREATE TABLE read` (your table name is “read”, right?) and post the result.

[chrisburton]

@CrocoDillon @traq Thanks for the info.

@traq I’m getting a syntax error?

[__]

sorry, there should be backticks around the table name (it’s a reserved word).

SHOW CREATE TABLE `read`

[chrisburton]

@traq Ah, thanks. This is what I’m getting back: http://cloud.chrisburton.me/Py4V

CREATE TABLE `read` (
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`title`,`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[__]

Looks fine. Select in `title,url` order.

[chrisburton]

@traq Thanks!

[jurotek]

@chrisburton, what is the purpose of this database? Is it to track URL’s you visit and articles (titles) you read?

[chrisburton]

@jurotek I guess you could say that. This is more about learning than anything. I could easily just output that data since it’s already stored in an RSS feed (but not on my end).

[jurotek]

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

[chrisburton]

> 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?

[jurotek]

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

[chrisburton]

@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

[Author]

Posts