0

Relevant question: Android: SQLite one-to-many design

This is my first post on here, and it's also my first time dealing with databases. If there are any clarifications, or corrections I can make, please let me know.

I'm trying to set up an SQLite database to better store items collected from an RSS Feed. I'm trying to have a 3 table set-up. It looks like this:

Table 1: The Channel Table (items stay in constant order)

_id | Feed Location | Channel Title | Channel Description | ...


Table 2: The Item Table (The items should be stored in chronological order for ease of access and deletion)

_id | Item Title |...| Channel ID | ...

Where Channel ID has the row number that the Channel the item is part of is located.

That part is pretty simple, but here's my problem. I want to be able to query items by the categories that are relevant to them. They're included in the XML that'll be parsed, and it'd be great to sort by those values. So I started to set up another table, and implement the same one-to-many relationship described above, but then I ran into a problem. Because the id's of those items are likely to change when more items are parsed, the id's aren't static, and I can't have the categories link the way I did earlier. Also, another challenge I found was the fact that each category might've ideally needed to point to many different items because the Items' could've been relevant to the same Category.

And that's where I'm stuck.

Community
  • 1
  • 1
scottmacwatters
  • 1
  • 2

1 Answers1

0

Now I'm not sure I understand your situation correctly: are the id's of the Category's non-static? That seems a bit strange, since the entire idea of an id is that it stays constant through out it's life time.

If that still is the case, I would try to implement my own id structure and assure that it's constant. You could perhaps calculate an id based on the category name (use a md5 or sha1 sum as id, overkill but guaranteed to stay static and unique). Having a unique id structure for your categories would then allow you to use the same table structure as before (on the items and channels).

It would perhaps even be an idea to make all letters in the category name lowercase and remove non-alpha/numeric values before calculating the id. This way you'd get rid of Categories that are most likely the same ("Category" would then equal "Cat-e-gory").

I'm not sure I actually answered your question, but I hope you can make some use of it :-)

dbm
  • 10,376
  • 6
  • 44
  • 56
  • Well, you've answered part of my question. The Categories are subject to change because Items may be added and deleted from the database at any time. I've chosen to delete old items because nobody wants to look down an RSS reader and see old items. I'm going to implement unique ID's though to fix that problem. Thanks. – scottmacwatters Jan 13 '11 at 03:31
  • Ah! So the actual ID of a Category doesn't really change. The Category itself can, however, get deleted and, hence, the "one-to-many" relation between Items and Categories might get broken. Am I somewhat correct in my assumption? – dbm Jan 13 '11 at 17:27
  • Sorry for the late response, but yes. I think I'm going to end up using the "Item"s URL Link as it's ID. It's unique enough to identify it. And there shouldn't be two that go to the same URL. – scottmacwatters Jan 22 '11 at 00:55