- This topic is empty.
-
AuthorPosts
-
July 1, 2013 at 12:56 am #141108
__
Participantno, 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_.
July 1, 2013 at 1:01 am #141109chrisburton
Participant@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.
July 1, 2013 at 8:48 am #141125Alen
Participant> @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.
July 1, 2013 at 12:10 pm #141150__
Participant>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.July 1, 2013 at 1:23 pm #141157chrisburton
Participant@CrocoDillon @traq Thanks for the info.
@traq I’m getting a syntax error?July 1, 2013 at 2:13 pm #141164__
Participantsorry, there should be backticks around the table name (it’s a reserved word).
SHOW CREATE TABLE `read`
July 1, 2013 at 2:46 pm #141168chrisburton
Participant@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_ciJuly 1, 2013 at 4:28 pm #141181__
ParticipantLooks fine. Select in `title,url` order.
July 1, 2013 at 4:36 pm #141184chrisburton
Participant@traq Thanks!
July 1, 2013 at 8:51 pm #141222jurotek
Participant@chrisburton, what is the purpose of this database? Is it to track URL’s you visit and articles (titles) you read?
July 1, 2013 at 8:54 pm #141223chrisburton
Participant@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).
July 1, 2013 at 9:06 pm #141224jurotek
Participant@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 #141226chrisburton
Participant> 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 #141228jurotek
Participant@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 #141229chrisburton
Participant@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:
-
AuthorPosts
- The forum ‘Other’ is closed to new topics and replies.