Database within a database (a la CCK without Drupal)

Question

I am hoping to build a Codeigniter library similar to Drupal's CCK, albeit in an extremely simplified version. I am wondering what kind of database structure would work best to achieve my ultimate goal, which can best be described by the following use case:

Interface:

Existing content types:

• Page [edit type] [list all] [add new Page]
• Rating [edit type] [list all] [add new Rating]
• DVD Movie [edit type] [list all] [add new DVD Movie]

[create new content type]

The web developer clicks on [create new content type]:

Form: New content type

Enter title: [_Visitor Comment____________]

Add field:

• Field name: [_Name__________]
• Field type: [ Text (<128 characters) ] (dropdown w. basic options)
• Required value: [x]
• [Add field]

[Save content type]

The web developer adds a couple of fields:

Form: New content type

Enter title: [_Visitor Comment____________]

Current fields:

1. Name | Text (<128 chars) | required
2. Email | Text (<128 chars) | optional
3. Content | Text (<1024 chars) | required
4. ForPage | Page | required

Add field:

• Field name: [_____________]
• Field type: [ -choose below- ]
• Required value: [ ]
• [Add field]

[Save content type]

The web developer saves the content type, and the system creates...... well, what exactly?

Idea 1: I could easily let it create a new DB table with the corresponding fields (which would essentially form a very rudimentary phpmyadmin system) -- but is this wise? Wouldn't that just litter the database with a lot of tables for this and that?

Idea 2: Or I could create a "database-within-a-database", something like:

table ContentType (e.g. "Book")
-- id
-- title

table CustomField (e.g. "Author Name")
-- id
-- ContentTypeID (linking the "Author Name" field to the "Book" type)
-- valueType (linking the field to the correct db table of values)

table DBRecord (e.g. "Lord Of The Rings vol.1")
-- id
-- ContentTypeID (linking the LotR record to the "Book" type)

table ValueText128 (e.g. "J.R.R. Tolkien")
-- id
-- DBRecordID (linking the value to the LotR record)
-- CustomFieldID (linking the Tolkien value to the "Author Name" field)
-- value : char(128)

table ValueSmallInt (e.g. "1954")
-- id
-- DBRecordID (linking the value to the LotR record)
-- CustomFieldID (linking the "1954" value to the "Published Year" field)
-- value : smallInt

...and so on, with a table for each of the datatypes

This could probably work, but I suspect it might be horribly inefficient in practice, given that every integer value from every item of every type in the whole system would end up in the same massive table - and the same for every char(128) value, etc.

So - what do you think? How does CCK do it? What would work best?

Answer 1

A generic database is very hard to optimize for performance, especially if you don't impose any limitations at all on the type, structure and usage of the data. Typically, such systems will store values with a type ID, so in order to query in the database, you end up doing many joins, which the database will be much less able to optimize for you.

Try to see if you can identify a set of distinct archetypes within the domain you intend to use this system for. For example: "Page", "Rating", "Media", "Comment", "User", etc. Set up fixed schemas and implementations for these cases, but allow specific instances to customize the labels of the fields as well as what fields to show. For even more flexibility, you could offer (limited) ways to customize field validation rules as well.

You can augment the archetypes with custom defined generic fields as you describe in idea 2, as long as the main content is covered by the fixed schema.

I think dynamically creating ad-hoc tables would quickly create a mess and it might be harder to debug and support. If you make it easy to dynamically create tables, what about modifications later on? You can alter existing tables, but migrating old data can be really hard, if not impossible, depending on the type of changes.

Answer 2

I found an article by Robert Douglass that pretty much answers my question:

What is the Content Construction Kit? A View from the Database