Techniques to create an object designer

Question

I've been trying to create an application where everything is effectively an object with a series of fields. I've abstracted it to the level that you have the following tables:

• ObjectTemplate
• Field
• LinkObjectTemplateField
• FieldType

Each ObjectTemplate has a series of fields (a many-to-many relationship), which can be found in LinkObjectTemplateField. Field is linked to FieldType (many-to-one relationship). Field also has an ObjectTemplateID field - so let's suppose we have an object template called Section, and another object template called Question (as in for a questionnaire). Section would have Question as a field for questionnaire designers to use to define which questions appear in a section. Each Question would then be linked to a series of Values (or none at all in the case that it is of FieldType 'Text'.

We're able to create fields, field types and object templates so far. However I've come to realise that actually all 3 of these could be represented within the above tables, and I could probably kill off one of these tables too (so I only have ObjectTemplate and LinkObjectTemplateField, where Field is an ObjectTemplate in it's own right so there is a link simply between ObjectTemplate and itself via LinkObjectTemplateField).

My aim is to have one table structure for ALL object types, both as it currently stands and in the future. I'll have a class which picks up all of the fields for a particular object, and the fields it is expecting based on the objecttemplate, and decides how to present the fields based on the template. This seems to be getting very complex and I keep finding myself getting confused. I have a week left to work on this, so my questions are: should I plough on with this? Are there any better techniques to achieve this, or any flaws in my approach? Should I have stuck with the old structure (an entire table for each object type, with the same fields as most other object types for the core details - name, description, deleted etc.)?

Edit

I have been going over my approach again and come to the following conclusions:

• Each object type, including object template itself, should have its' own record in the objecttemplate table.
• Each object template, field and fieldtype should then have its' own row in the object table.
• In this way, for example, Text, Dropdown etc. will be objects using the fieldtype object template. The IDs of these will be used in the functions for writing the forms - they will be declared as constants and referenced via MAIN::TEXT, MAIN::DROPDOWN and so on.

Answer 1

You are effectively trying to implement o form of EAV, and unless you actually need the flexibility it brings, is considered an anti-pattern.

Such "inner platform" is usually a poor replica of the real thing. In a nutshell:

• It's difficult to enforce constraints that are otherwise available to "normal" tables and fields, including data types, NULL-ability, CHECKs, keys, and foreign keys.
• You no longer have a good "target" for setting permissions or creating triggers.
• It's difficult to limit an index to a specific "column", or make it use a "native" type.
• It's difficult to reconstruct the "original" object. Usually, a lot of JOINing is required and the resulting object is not represented as a single row (which may be awkward to the client). Indexes and query optimizer can no longer work optimally.

So unless you absolutely have to be able to change data structure without changing database structure, just use what DBMS already provides through "normal" tables/columns/constraints...

My aim is to have one table structure for ALL object types, both as it currently stands and in the future.

Well, you kind of already have that built-in to your DBMS: it's called "data dictionary". Yes, you change it through CREATE/ALTER/DROP statements instead of INSERT/UPDATE/DELETE, but at the logical level it's a similar thing.

Should I have stuck with the old structure (an entire table for each object type, with the same fields as most other object types for the core details - name, description, deleted etc.)?

Probably.

BTW, if you have a lot of common fields (and/or constraints), consider putting them in a common "base" table and then "inheriting" other tables from it.