Requesting peoples opinion on creating child tables or just use the one table for everything...?

[DrewW]

So we built a scoped app to track the process behind a form that is filled out by a customer.  Initially the design was to have table we will call form_a and the two US States would just use that one table since 99% of the fields where the same and they where ok with seeing one or two fields they did not use.  So now they like the app enough that they want to bring on another US State. The new US State has about 6 new fields and there is some conversation about possibly bringing on all of the US States we deal with or just some of them.  So we have no idea if this will be the last US State added or not.

So the the question has become do we continue using table form_a for everyone and just use UI polices to hide and show things or do we start creating child tables called things like form_a_<US_STATE_ABREVIATION>.  We have been going back and forth about which is easier to maintain/develop and which one is a better design.  With one we will have ~50 child tables and some of them will not have any extra fields.  The other way we have a bunch of UI Policies on one table.  The security design is the same either way so we are not considering that in this part of the decision.

Some of our thoughts on the two options

1. One table for all
   • Creating records is easy, the user clicks new and then picks the right State.
   • UI Policies could get complicated and difficult to manage but this can be helped by using a naming convention.
   • All fields in one table will make cross State reporting easier.
2. Multiple Child tables
   • We need to make sure when creating a new record the user is directed to the correct table to get the correct form.
   • Adding new fields for new States does not impact other States.
   • Possible duplication of fields since we will not know what each State wants until we bring them in.
   • UI Policies are simpler.

Any feedback/thoughts would be helpful, please and thanks.

[Kieran Anson]

Hi, 

Not sure if you're referring to a completed scoped application that's unrelated to anything SN provides, but your question made me think of CSM and case types. Case types are extensions of the base case table that allows for business processes to be more easily managed. 

https://youtu.be/56IAivbwCK4?si=IQkbv8P5Oux_9UTa

You might not be using CSM atm, but some of the indications within this video might be of help for your architecture 

[Bert_c1]

You ask a general relational database design question, nothing specific to Servicenow. Choose your favorite approach in Servicenow.

[DrewW]

Its actually more than that.  There are issues that are specific to ServiceNow and I was hoping for input from people who may have had to deal with the same type of thing and how it went.

[Bert_c1]

See:

Table Flattening

Increases performance.