Table questions

[Earl L]

I have a few questions about how customizing tables works on the SN platform. And first I want to apologize if these questions are answered in the sysadmin training but I haven't been able to attend that just yet. I also haven't seen a clear answer in the documentation, so if someone wants to point me there I'm more that happy to be directed to the right document.

First, the premise I'm working from is that some existing tables don't fully satisfy our business requirements, meaning that I want to add new columns to the tables. Let's use the cmn_building table as an example. Let's say I want a column to hold some code for each building. Based on the documentation that I've read I have a few choices, and again please feel free to correct me if I'm wrong.

1) Add a column to the existing table
2) Create a custom table that extends the existing table
3) Create a custom table that does not extend the existing table

What I'm really interested in are the pros/cons of each of these approaches. I'm making some assumptions from here on out so this is where I can really use the help.

My assumptions:

With option 1 I have changed the definition of the base table, and as I understand it this will get overwritten when we do an upgrade. Is that correct? The upside of option 1 appears to be that I've got all my existing data with no additional work required, anything that used the cmn_building table will have the new column available and I don't have to change any table references anywhere that table is used. But I'm kind of hosing myself for future upgrades.

Option 2 would seem to protect us during upgrades but the data from the extended table isn't directly populated into the new custom table so I'd need to do that myself. And in addition, I would have to point any existing references to the old cmn_building table to the new custom table.

Option 3 seems to be essentially the same as option 2 with the exception that I would need to create the entire table structure on my own, so I don't get the benefit of jumpstarting the table definition.

Am I correct in my assumptions? Either way, whether I'm correct or not, what do others do when they find themselves in this situation? Thanks for any feedback that you can offer. And again, if there is some documentation about this that you can point me to I'm more than happy to do some reading. Thanks!

Earl

[Aaron40]

From my experience, if a table has all you need minus a few extra fields.. and the fields are related to the table, then add the the new fields directly to the table. If the fields you want to add aren't exactly related to the table, or if there is a whole hodgepodge of fields you want to add, a new non-extended table is probably what you want to do.

If you ever fear you are going to cause problems with the original table, make a new table. New tables don't hurt the system and often times it is a good way to consolidate newly added fields.

You would not be extending a new table if all you're doing is creating a few new fields. You'd extend a table if you're doing a project like creating a new application and a particular table in your environment has all the fields you need. Instead of creating a new table, you would extend that table.

Does that make sense?

[Earl L]

Do you have any feedback or comments on my assumptions regarding upgrades and creating references to new tables, when you choose to do that?

Earl

[Aaron40]

Hi,

If you add a column to a table and you go through an upgrade, you're not going to be hosed for future upgrades. If a change to a table does happen, it's not going to remove user created fields. It'll overwrite fields that came out of the box.

For option 2, you're absolutely correct. If you extend a table, you're not getting any of the data of the original table. It wouldn't benefit you to create an extended table in this situation. You could just add the fields directly to the original table and be fine.

For option 3, you are right about your assumptions. If you go this route, you start with a clean slate. You define most every field about a table. If you have a need for many, many fields, you may prefer to extend another table. If you have a dozen fields or so, it'd be cleaner to build your own table and create your new fields.

Only extend a table if you want to create a new table with the exact same properties (not data) of another table. Otherwise, create a new table and define it however you need.

Let me know if you have further questions!

[Earl L]

Aaron

This is good news. I was under the impression that changes to built-in table would be lost through the upgrade process. Do you know if changes to labels for baseline columns are retained/lost during upgrades?

Thanks for your responses.

Sincerely,

Earl