Page 1 of 1

which schema to use..? feedback appreciated

Posted: Wed Jul 18, 2007 5:42 am
by nathanr
Morning All,

I'm working on a rather large project which has the need for the admin at the back end to be able to specify fields to be used throughout the system, any 1 object (say profile) may have 80 custom fields, where as another profile type may have a compeltely different set of 80.
none are fixed or even known at this point.

I've mapped out 1 idea which I've implemented previously, and another dev has another idea.

Option 1:
db schema such as
profile
profile_fieldindex
profile_fields
_fieldtypes
_fieldvalues

now the schema itself is far more complex, storing data in the appropriate field datatype table, 1 for int, 1 for varchar, 1 for timestamp, uplaod selects etc etc, but this is the core

Option 2:
Create a single table for each "profile type" with columns for all values

I prefer option 1 myself, main question is - any think of a 3rd alternative?

Many Regards

Nathan

Posted: Wed Jul 18, 2007 6:21 am
by feyd
Option 2 isn't normalized, nor does it sound like a good idea. Option 1 seems overly complex.

What's the problem with generic containers for the values?

Posted: Wed Jul 18, 2007 6:36 am
by nathanr
Cheers for the reply feyd,

How to explain.. I'll keep stick to "profiles" while describing, even tough it's not a profile system!

Admin needs to be able to create 1 empty profile template
Admin needs to be able to add in any amount of random fields per profile template
user fills in profile fields, but not everybody will have to fill in all fields, and likewise some fields may be required based on input from previous fields.

There will be approx 50 profile templates added per month, each 1 will have about 500 users filling the template in per month.

Each profile template could have either 1 or 250 fields, and at design time we have no idea what they are..

The functionailty we need to add is to let the admin basically add a new field, specify it's datatype and then allow it to be displayed at the front end, and hold user input from 500 different users.

Therefore it is impossible to create a set table design to handle this, hence my proposal of solution 1

I've set doen something similar (with profiles actually) in the past, I've set it up online so you can see a rough idea of the functionaility needed
http://urisecure.uk.to/_admin
click profile.fields - add in a few fields, then go to profiles, edit/add a new and you'll see the fields there with ability to save data into them.
This version has no "templates" as it where, but the whole adding fields on the fly thing is covered.

Any Ideas?

Posted: Wed Jul 18, 2007 6:47 am
by feyd
The approach I would likely take would be to use the following...

Template - id, name, description, any extra "global" level fields as required
TemplateFields - id, field name, field description, datatype metainformation (this could be regex or all sorts of information depending on the formatting used)
Template-TemplateField (linking table) - Template id, TemplateField id, possibly position information (where it's located in the template,) requirement status, any other individual toggles that may be more associated with the template than the field itself (since fields could be reused.)
TemplateField-TemplateField (subfield linking system) - TemplateField id (parent), TemplateField id (child), position information, requirement status, , any other individual toggles that may be more associated with the template or parent field than the field itself (since fields could be reused.)

Posted: Wed Jul 18, 2007 10:22 am
by nathanr
feyd.. where do we store the data? given that 1 field can be available to 200 people, therefore 200 bits of data.
if we store it in the TemplateField-TemplateField table, then the field column type would be..?

Posted: Wed Jul 18, 2007 11:04 am
by nathanr
got it sussed have to go with option 1 to keep datat normalised, usign a couple of stored procedures to make things simple when php'ing would love to explain but it will take me all day!

Posted: Wed Jul 18, 2007 2:54 pm
by feyd
Users would be associated with the templates, or rather the group would be associated with templates and users with groups.