Can anyone tell me Sage Software's stance on adding custom fields to their core tables vs using an extension table?  What is best practice and what is common practice?

How about fields that are not 1:1 (i.e., account manager phone number)?  Is that considered acceptable?

I appreciate the feedback.

Since a while back (v7.2/5 I think) - the stance is now to allow the extension of core tables. However, just be careful of common column names as, in the future, Sage may use them and you will clash. I typically use cn_co. When cn = customername. That way, unlikely to clash.

Quote: Originally posted by Mike Spragg Since a while back (v7.2/5 I think) - the stance is now to allow the extension of core tables. However, just be careful of common column names as, in the future, Sage may use them and you will clash. I typically use cn_co. When cn = customername. That way, unlikely to clash.

The one concern with using Underscores is that sometimes certain features in SLX don't work well with them.

For instance (not sure if this has been fixed yet), there were early issues with filters not working when the Field or table names contained underscores.

Never the less, your point is valid regarding using some sort of Standard naming convention to identify custom fields.

Hi Raul

I'd be interested to see a defect# for this. As you know, been using this a while and have to honest I've *never* seen an issue with doing this ? The only time was in an early version of 6.x whereby I used E1_ (previous company) and SLX interpreted that as an exponent number ! But, they fixed that years ago.

Regards
Mike

I ran into this on 7.5 (no SP).

We had added quite a few fields using the "c_" naming convention for our Custom fields.

When we tried to use Filters, they would not work for this Custom Fields. I recall going through the code back then and the Workaround at the time was to rename the fields.

I don't recall the Defect information around it, but I do recall having a ticket open to SAGE directly by the Customer for this issue.

Thanks everyone for the feedback.  Does Sage have an official stance on the Best Practice for this topic?  Also, I can understand why it's nice to use the core tables for a 1:1 relationship, but what if it is 1:M?  Does Sage recommend to use the core tables in that situation? 

For every table it's fine. I'm not sure of your question (not entirely convinced you are either). If it's a 1:M on custom data then simply create a new 1:M anyway?

Hello Mike,

I am actually quite sure of my question.  I have been working hard to keep my personal view out of the post, so that may have made it a little confusing.

I have been told that it is a best practice and the standard to put all custom fields in the core tables.  The question initially arose because a developer added several fields to a core table that constituted a one-to-many relationship where each core table row would be linked to one custom field value, but each custom field value could exist many of the core table rows.  This is obviously not as bad as if it were the reverse, but was still surprising to me that it would be recommended as best practice.

For example, say we wanted to suddenly include "sub products" in our database.  Each product could have only one sub product and there are 10 sub products.  Rather than adding six or seven fields to the product table that would essentially repeat themselves, I would choose to create an extension table and link them by ID.  What I am hearing is that this is not recommended practice anymore and I am trying to see if that is truly the case.  If it is, why?  I like to stay up on the best practices for all products I work with and could not understand the reasoning behind this one.

Thanks.

Right, that makes more sense now! No, I wouldn't have advocated doing it this way (SLX also do the same with History.LeadID and History.TicketID etc. where the table's extended to handle several entities rather than an intermediary. It makes things very messy at the end of the day - once you start to extend it past the 1 or 2 it was probably designed for.

Your suggestion (ext table plus link) is the way to go - otherwise you'd have to recode anything that only expected 1-6 sub-fields/products.

But, at the end of the day, you are {now} free to do whatever you wish and to keep to whatever standards you prefer (Sage/SLX don't make recommendations as far as I know) and you need to weigh up best-practice -v- cost. If you are an end-user and want to do it right (do it your way). If you are a BP with a schedule/costs to keep to then you do whatever's needed to get the job done.

You can modify an standard/core table to include what you need (naming conventions recommended). You can extend to any number of tables (1:1, 1:M or none) and, if they are extension tables, then unlikely Sage will use the same name (but, of course, beware).

Hope that helps.
Mike

I think the reasoning behind such a stmt was that field level security only worked on core tables in some versions.

Now that was a while ago and dependent on license type as well, several options can be used today.

My standard is to always add the 1:1 fields to the base tables (watch the record size limit) and keep the names from being too generic.

A sql compare of the slx default db system tables to the custom db, will show you all custom field name/size/type differences and is 100% reliable.

I have only run into a naming conflict a few times and has always been due to an existing field name becoming a reserved word in the new version of MSSQL, ORACLE or SLX.