0

I need to make all the fields on my website database driven so I can do field level tracking and assign validations/field types etc to each field type. Current schema:
L_SystemFields
sys_field_id
name

L_Fields
field_id
sys_field_id (fk to L_SystemFields)
name

This allows me to link this:
Field 1 = Username on login page
Field 2 = Username on signup page

Now both fields are technically the same - 'username' so they have the same sys_field_id & name in L_SystemFields. But to do field tracking they need a unique ID so they have a different field_id and name in L_fields like username1, username2.

The question is: In the html form what am i pulling in - the field_id, sy_field_id, name (from L_SystemFields) or name (from L_Fields) or something else? As far as i know, field names should be unique per page which works in this case but wont work in a case where i have multiple of the same fields on a page like user profile where i can add multiple colleges i attended. In this case how to name the fields and what values to use?

Ray
  • 1
  • 1
  • Suggest editing your question to include a sample HTML form to illustrate. – p.campbell Apr 19 '11 at 01:30
  • Sorry, I'm not getting it. What's a "field_type_id" and what do you want to use it for? Are you talking about creating dynamically generated schemas, i.e. a CMS with user-defined fields, or just a website with database content? – deceze Apr 19 '11 at 01:32
  • I've updated question with schema and purpose. – Ray Apr 19 '11 at 01:48
  • What do you mean by "field tracking"? – deceze Apr 19 '11 at 01:51
  • field tracking is many things: which fields are answered per page, which give the most error, which values go with which fields, etc. To do all this i need to uniquely identify fields + link them together. – Ray Apr 20 '11 at 01:44

1 Answers1

1

Suggest that you don't derive the database schema to fit the HTML form. You'll want to make your application easy to change for the future. Tying the database design to your HTML form would be painful down the road. It would also allow you to implement v2 or another front-end (mobile app, desktop app, etc) independent of your initial HTML implementation.

Consider implementing your database design first. Achieve 2nd or 3rd normal form as you see fit.

In general, give a name/id to your HTML elements to help them make sense. Different elements on different pages (contact vs profile vs signup) don't need to be uniquely named.

It's not clear why you need to incorporate/decorate your field with their field_type_ID.

<input type="text" id="customerEmail_1" />
<input type="text" id="homePhone" />

For multiples: 

<input type="text" id="customerEmail_2" /> //etc

If you need multiples, another option would be to use and style a <textarea>.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • I updated the question with the field schema and why. So customerEmail_1, customerEmail_2 are the field names or field IDs? (Id meaning the Primary key from the field table)? And id has to be unique for entire system or only per page? – Ray Apr 19 '11 at 01:47