SQLite3 simple database design question

database-designschemasqlite

I'm an ruby hobbist with no experience at all related to databases. I'm writing an application that is going to gather and backup tweets and blog posts (via rss feed) in an SQLite3 database.

The first version had support just for backing up a specfic user's tweets as I wanted to create a backup for my twitter account. So the SQLite3 schema was easy: 1 database, 1 table per user.

Now I want to add backup support for: Location based tweets (i.e. Tweets from Germany), username based tweets, and hashtag based tweets plus support rss feeds.

Since SQLite3 does not support any sort of sub-tables and I'd like to create a schema that will not need change as soon as I begin testing, I'd like to have opinions on how to approach this.

I've read a lot or articles online about SQL db design and found a couple of book also. Didn't purchase any yet, I'm just considering reading some basics about db design since I don't need an extensive book just an intro I'm evaluating. I'm not sure if these articles confused me more or not.

What I came up with a db schema is an INDEX table which user's info will be held and then a table per option per user, like:

➜  lib git:(dev) ✗ cat sqlite_scheme.txt 
SQLite3 Table Scheme

MRParent
--------
db_key
table_id
twitter_id
twitter_option {what and how}
feed_url
short_description

MRChild-id-tweet-usr
---------
db_key
tweet_id {from MRParent}
table_id
tweet_date
tweet_location
tweet_text

MRChild-id-tweet-location
-------------------------
db_key
table_id {from MRParent}
tweet_id
tweet_date
tweet_location
tweet_text

MRChild-id-tweet-hashtag
------------------------
db_key
table_id {from MRParent}
tweet_hashtag
tweet_id
tweet_date
tweet_location
tweet_text

MRChild-id-rss
--------------
db_key
table_id {from MRParent}
post_author
post_date
post_title
post_text

So all tables will link to the INDEX table via table_id (which I've not deciced if it's gonna be a sort of hash or a combination of user's data, ideas here also welcomed).

Does this schema look 'rational' for an SQLite3 db or not? Any comments and/or questions are welcome.

Thanks for your time!

PA

Best Answer

After reading a lot around and receiving a hint about SQL data normalization I came with this schema. The rest will be handled by simple requests:

  1. Table MRindex - All details about a entry will be listed here: key db | user_made_id | description | date | blob_picture | rss_feed | twitter_alias/keyword/hashtag |
  2. Table MRtwitter - All twitter related data will be stored here: key db| index-at-table1| tw_option|tw_user | tw_date | tw_location | tw_name |tw_text
  3. Table MRrss - All rss data will be stored here: key db | index-at-table1| rss_post_author | rss_post_date | rss_post_title | rss_post_text

Any comments & hints would be more than welcomed :-)

EDIT: To clarify a bit...

MRIndex (also referred as table1) holds basic data, filled by the user: Might be user tweets, hashtags or location based tweets. Once this entry is filled up

MRtwitter will hold the tweets and related info. The field index-at-table1 is the "link" to table MRindex.

MRrss it's the the same for blog posts. Same thing applies for 'index-at-table1'.

For MRindex:

description: short user description date: date of creation blob_picture: a picture if any (jpg, png, etc) rss_feed: An RSS_feed URL belonging to this user twitter_alias/keyword/hashtag: either @user, Greece (for location) or #hashtag

Related Question