Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/#!/StackDBAs/status/617592939112472576
edited tags
Link
James Anderson
James Anderson
  • 5.8k
  • 2
  • 26
  • 43
Source Link
AllwinP
AllwinP
  • 43
  • 3

Single table or multiple table design when records have a clear distinction but are queried together?

Let's say I have the following types of recurring data (all types periodically come in).

  • Post (Title, Summary, Text, Timestamp)
  • Image (URL, Caption, Timestamp)
  • Event (StartDate, EndDate, Title, Description, Location)

As the style of the data suggests, we can split them into their own separate tables, Post, Image and Event respectively. However the majority of the queries on these data are of the "timeline" variety (Return me all posts, images and events within a given timeframe sorted by time), though there are also some queries that work on a single type alone.

Someone suggested that we combine all three into a single table and encode the unique attributes into a json field (We're using Postgres 9.3+) rather than use a UNION of the three tables.

So according to that model, we'd have

  • Info (Type, Timestamp, Attributes)

where Attributes is a JSON field which will have different keys based on the Type value.

Which is the better approach here?

P.S. This is a simple example for explaining the question but in reality there are closer to 9 different "types" of data and the number of unique attributes is much larger. We might need to join on one of those unique attributes as well in the future (not sure if Postgres supports this).