By atmosx


2012-08-16 19:03:31 8 Comments

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

1 comments

@atmosx 2012-08-20 19:41:40

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

@dezso 2012-08-31 07:52:10

I'm afraid I don't quite understand what a 'specific entry' is. At first glance those data stored there are unrelated.

@atmosx 2012-08-31 08:31:47

Sorry, bad English. Not specific, just 'entry'. What do you mean by 'unrelated'? (I removed the word 'specific')

@dezso 2012-08-31 08:49:45

I mean that I can't see how do those thing belong together. Maybe a bit of clarification on what description, date etc. is could help.

@atmosx 2012-08-31 10:01:10

I added some clarifications above. I'm not sure I've answered your question though.

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] Create Sqlite3 database

  • 2015-10-21 08:14:35
  • reggie
  • 1028 View
  • 3 Score
  • 3 Answer
  • Tags:   sqlite

3 Answered Questions

[SOLVED] Design for CE Events / Training Database

1 Answered Questions

[SOLVED] Design of small tables: clustered index or heap

1 Answered Questions

[SOLVED] Database Design question

  • 2016-06-10 16:02:58
  • Raneej
  • 96 View
  • 1 Score
  • 1 Answer
  • Tags:   database-design

1 Answered Questions

[SOLVED] Question regarding database design

  • 2016-03-22 01:05:42
  • g_b
  • 42 View
  • 0 Score
  • 1 Answer
  • Tags:   database-design

2 Answered Questions

[SOLVED] Database table design question

  • 2013-05-16 09:17:37
  • User007
  • 1702 View
  • 7 Score
  • 2 Answer
  • Tags:   database-design

1 Answered Questions

[SOLVED] Designing a database for a site that stores content from multiple services?

1 Answered Questions

[SOLVED] How could I improve my DB design?

1 Answered Questions

[SOLVED] creating a historical database, need help making a decision

  • 2014-01-02 06:25:39
  • Drewdin
  • 101 View
  • 0 Score
  • 1 Answer
  • Tags:   database-design

Sponsored Content