I'm designing a database where individual nodes, representing statements in a discussion, are stored, but I don't want any unnecessary hard-coded fields (like statement text, date, title, etc.). I want the user to be able to define most of the fields that make up a statement. Everything is stored in a metadata system (meta for the statements, not database meta) that's completely flexible. Of course, the user interface handles certain types of metadata specially for convenience, but only for convenience.
Here's my first whack at this. I have a statements table with no real fields besides a key. I have a metadata_categories table for the different ways the user interface displays and edits the meta fields. I have a metadata_types table, referencing metadata_categories, for the actual user-defined metadata fields. Then I have a metadata table, referencing statements and metadata_types, that contains the actual data.
The problem I've come up against is that I'm not sure how to store the data in the metadata table. Do I make it a plain string, and just convert back and forth in the user interface when necessary? Do I have a bunch of fields with different types, and just make sure that only one is non-null at a time? I'm using PostgreSQL. Should I try using table inheritance for the different column types? Instead of having a metadata table, should I have the user code automatically create and maintain real tables in the database for each new user-defined field? I'm really missing a generic "object" database type here.
Is there a widely used patten I'm missing? If not, what are your experiences with this kind of problem?