Adding Product Fields. Opinions?

[DerlinStiles]

My company has been trying to build a new website around AbleCommerce (currently 7.0) and they have asked me to take over the project (the former project manager left months ago). I am trying to pick up the pieces and figure out where to begin and was dissapointed to find little in the way of developer documentation. I have read many posts here and still feel like I am missing some basic concepts.

My primary concern right now is that they want to add about 25 custom fields for each product. It looks like my predecessor originally added columns directly to the the ac_products table (!), but then (I'm guessing) thought better of this idea, and then added all the fields to the ac_InputFields table.

But... is using the ac_ProductTemplates/ac_InputFields the best way to do this? Most of the products, regardless of product template, need these additional fields, so each custom field must be added several times in the ac_InputFields table, once for every ProductTemplateID.

The ac_ProductCustomFields seems like a better choice in this case. Or am I missing something? What is this table intended for (it seems to have no front end in Admin pages)?

Another option is to create a table with a 1-to-1 realtionship with the ac_products table (ProductID). This seems to me to be the best option for several reasons. I could specify data type for each new column. Querying product data would be easier (it would make importing/updating data from an external source (important) easier). Customizating of the pages would be a bit simpler. It should also be fairly easy to handle during an upgrade. Is this reasoning sound, or is it better for some reason to use one of the former 2 options?

Which option would be most upgrade friendly, and product-import friendly, etc.? Will Data Port play nice with a custom table?


To sum up... for additional product fields, should I:

A) use ac_ProductTemplates/ac_InputFields
B) use ac_ProductCustomFields
C) create "cstm_Products" table with 1-to-1 relationship with ac_Products (new fields as columns, not entries)

I guess I should add that I am not a web developer guy- once the tables are there and the data is importing succesfully from our in-house product db, I will hopefully be paying someone for the aspx stuff- IF my company can afford it. Any input is greatly appreciated, even if its just references to other sources to check out.

[jmestep]

If you add a separate table, the dataport will not access it since it's an Able table.
If you use template fields and assign the template to the product, all the fields will be there for you to populate. If you use custom fields, you are going to add each field to each product and populate them with the custom field name and the data. That was the way it was in Able 5 and the templates are so much easier and more versatile.
Here are a couple of screenshots of Able 5 custom fields vs. Able 7 template fields.

Able5custom.JPG

Able7Temp.JPG

[DerlinStiles]

A ha! So ac_CustomFields is essentially a legacy table in AC7 from the days of AC5. Thanks for the response!

And DataPort cannot be configured to work with custom tables. Check. But I'd still like to explore the option of creating a custom table, for the previously stated reasons. If I don't care about using Data Port and don't need the fields to display on the Admin side, are there any reasons you would advise against creating a new table for custom product fields? Let me rephrase my question for clarity:
________________
I'd like to create a table, cstm_Products, with a one-to-one relationship with ac_Products for custom product fields. I don't care about using Data Port and I don't care about the fields showing up on the Admin side (these fields should only be fed/modified by another source anyway). A major reason is that I'd like to add 25 to 30 fields. If I have 700 products this equates to 21,000 records - not exactly a scary number, but 700 seems better. Queries would be much simpler too. Also, since all templates will require these fields, I'd have to add each new field to every template (again, not too daunting, just seems a little redundant).

Are there other reasons (other than Data Port, and the automatic display of the fields in Admin) that I should avoid creating a new, custom table for custom product fields?

I am leaning toward this option as I think it would be easier to set up, but I need to be talked out of it if it is going to cause me more trouble down the road!

Again, any input is greatly appreciated- thanks!

[jmestep]

If you're not going to be using the dataport to update, then another table would be a workable way to go in your case.
Like you say, you will have fewer joins to deal with.
We have done that in cases for various tables in the store - w2m_products, etc. Just be sure to name your table something that Able wouldn't use- don't start it with ac_ .