1

What's the standard way of storing ``subtypes'' if you will in relational databases like MySQL?

As an example, think of a single user's Facebook feed. All it contains are "entries", but these entries can vary significantly in type and what needs to be stored. A status might require a VARCHAR(255) for example, while a picture might want a BLOB and a note might need a TEXT.

Completely separate tables make it necessary to make seemingly needlessly complex queries to get all of the recent entries of any type. It just seems awkward, inefficient and not completely stable to have a lot of extra columns in which only one can be not NULL.

I understand that this must be a common question but I cannot find something similar, so please feel free to tell me a duplicate and I'll happily close the question.

Aaron Yodaiken
  • 19,163
  • 32
  • 103
  • 184

2 Answers2

0

I think what you call subtypes is a hierarchy. The Entries are actually abstract entities and the concrete subentities are Status, Picture and Whatever. So a user can have many entries the will be "mapped" somehow to one of those subentities.

You can take a look at this question to see how to transform a hierarchy into tables: What is the best database schema to support values that are only appropriate to specific rows?

Community
  • 1
  • 1
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
0

I would create separate tables for each type (i.e. status, note, etc.), and use a table to link entries to users with three columns - user id, item type, item id. Then use the item type to figure out which table to join. Then all the data can be stored as the correct type, and appropriate indexing will make the joins work well.

kitti
  • 14,663
  • 31
  • 49
  • What MySQL type should the `item` type be to facilitate joins in that fashion, and could you provide a link/example about how the SELECT would look? – Aaron Yodaiken Mar 11 '12 at 01:28