How do you store Twitter profiles in your database?

[Arik Fraimovich]

I came to the point in my application development, that I need to
start caching profiles. I guess that many of you already doing such
caching and can share some tips from your experience.

Basically what I thought about is to store the cached profiles in a DB
table. What I wasn't sure about is the schema of the table. Thought of
two options:
1. store the user_id, screenname and created_at/updated_at fields (to
know where to get newer copy) and to store the rest of the fields as
JSON in a blob/text field.
Pros: no need to update structure whenever the API updates.
Cons: can't do interesting aggregations on the profiles.
2. create the table schema to be same as the fields the Twitter API
returns (+ created_at/updated_at).
Pros: can do interesting aggregations, can set indexes on key fields
(although I will usually retrieve by user_id/screename)
Cons: will have to update the schema each time the API updates, need
to create a lot of fields in the DB :)

Any other cons/pros I need to consider? Any other suggestions?

Thanks !
Arik (@arikfr)

[Patrick Burrows]

I think number 2 is the only good option here (and it is what I do.)

The problem with storing serialized data (such as JSON) in a database is
that if the schema changes, now you need to know what the original schema
version was of the data in the database in order to deserialize it. The
schema could change without you knowing and break things in a way that is
undetectable for weeks or months.

With a defined schema, you get an error immediately and can fix it
immediately.

--
Patrick Burrows
http://Categorical.ly (the Best Twitter Client Possible)
@Categorically

[weex]

Option two. That's what I do as well for Tweet Scan user search.
Database fields should be atomic and I bet the profile schema doesn't
change in a way that breaks your (properly coded) script for a long
while.

[Arik Fraimovich]

Patrick & weex - thank you both for your answers. I guess I will go
with #2. Thanks.