Designing Mysql Tables For Dynamic Form Elements

[Danny]

Hi all

 

Well, after buying Larry's book almost 7 months ago! and a particular project in mind, I am now in a position to be able to get moving full steam ahead.

 

And so, enjoying the book immensely and throwing myself in at the deep end with my project which is based around what you would no doubt term dynamic forms.

 

To try and explain as simply as possible, imagine a business with 4 offices, each handling data for a particular object, let's say as a fictional example - building a car. To build the car at each office, a form has to be submitted with the relevant data to be able to build the car, things like wheel size, colour, number of doors etc.

Now, in an ideal world of 'one business, one process', all four offices will use the same form system, but, they will use them slightly differently. Office 2 for example may only be able to make black cars so would not need the 'colour' option enabled on the form. I'm sure you can imagine various scenarios like this.

 

So, my thought process has gone like this..... Create a database driven form where each form field and its attributes are held in a table within mySQL. There are then relational tables that will define things like whether a field is enabled based on the office that is filling in the form.

 

Now, my initial question is, am I thinking along the right lines! i would hate to invest a week of my time designing the db structure and then have someone tell me it is the wrong thing to do

 

The next two questions are related to the tables I have created or have yet to do so.

 

Firstly, I have a table - 'frm_fields' and this is where the form field elements are stored, example columns are:-

• field_id
  
• field_title (the title that appears beside the field on the form)
  
• field_level (defines the level of the organisation at which the field is represented e.g. global or office level)
  
• field_view (defines which view the field relates to in the erp system used by the business)
  
• field_block (defines the block of fields it will appear in on the form)
  
• field_technicalName
  
• field_side (value is either 1 or 2 which defines whether it appears on the left or right of the 2 column block)
  
• field_type (defines whether it is a text, select, checkbox field etc.)
  
• field_length (defines the max character length of the field)
  
• field_width (defines the size of the field as it appears on the form)
  

​This is just a subset of the table columns and I think I'm up to about 14 at the moment, my question being, is there a 'best practise' limit to the number of columns per table?

 

 

Secondly, once the form is shown to the user, they will need to complete and then submit the form for 2 separate approvals. I obviously need to build a table which will hold the data they have provided and have therefore created a table called 'frm_active' which currently consists of only 3 columns:-

• form_id (each form request will of course have a unique id)
  
• field_id (foreign/primary key of the field from the table above)
  
• field_value (the value they have entered for this field)
  

​And so, my question here is about the field_value datatype. It appears that I will need to have a generic datatype for this field of varchar(40) to accommodate all field values in the form, some of which may be a simple "1" or "yes"/"no" scenario. This seems a little overkill to me and I'm wondering, considering I may eventually end up with 2-300 fields in total, will it affect the performance of the db.

 

Well, I'll halt the post there for now and welcome your feedback. I'm pretty sure there will be lots more to come as I 'find my feet' with all this coding malarkey!

 

Many thanks in advance

 

Danny

[HartleySan]

1) Within reason, you can have a bunch of columns in a table. Can't really put a number on it, but I wager you could have 30 or more without any serious performance hits.

 

2) A table will often times have millions of rows in it and still work very quickly, so I wouldn't worry about 300 rows.

[Danny]

Thanks for the response, HartleySan.

 

I'm plodding along the same path and am navigating table joins. I have to say, I think I'm actually grasping it! I'm one of those guys that can read what Larry has written all day long, but the penny won't drop until I'm tapping away at the keypad myself

[HartleySan]

I think we're all like that. All the same, if you're fully understanding joins on your first read through, then you're way ahead of the curve. It took me a couple of years of approaching the topic several times before I really felt comfortable with joins.

 

Anyway, good luck with your project, and by all means, ask questions whenever you feel the need.