It always pains me to suggest it but theres a reasonable case for making use of an XML column here.
Create appropriate tables to handle commonly used fields i.e. name, address, email, age, gender etc. I'd be tempted to store the custom fieldsets as an XML column. Once the number of subscribers they are emailing reaches a level where performance may become a problem, trigger a notification to the DBA so they can review the custom fieldsets created by your users and see if there is justification for moving them out of XML columns to defined sub-type tables.
Obviously you end up with two code paths for handling XML and sub-type definitions but you retain flexibility while having a process for dealing with the potential performance implications.
If SQL2008 was an option, I'd consider making use of sparse columns for the custom fields (generic definitions, along the lines you mentioned) and combine with filtered indexes for the larger customers.
I don't know if this will help, and this maybe a little much for your situation, but here goes our solution in use today.
All users are grouped into logical entities under a domain (something.com) umbrella. Our particular scenario required an additional layer of Domain->Company->Group->User break out. Not sure if you ever worked with Active Directory or domain trees, but it follows that logic.
Using the domain model, the server itself is at the top, presumably the client of your services would be the administrator. Each company flows like a branch from the server itself. Then flows into user-defined groups which contain users. Each company in this scenario would have a Domain Admin which can administer the xyz.com, abc.com, etc domains, but can't access any other domain.
Each of the service containers (the databases) use a full trust model with the security database to provide services to the users contained in this security database (Think OpenID for perspective). We use a home brewed C++ compiled module for Apache 2.x to provide an application firewall and session security host.
Each run, the module "asks" the security database to record the page hit, produce a random (64 character string) cookie and a session cookie (two total cookies), and authenticate the session. A new session or the same session is returned based on if the cookies match. The session key and anything else relevant is provided to the end-user UI application code over HTTP headers (so the firewalls could sit in front of Google Apps or AppEngine).
Once the UI has the code, the UI can act on behalf of the user and the service database accepts this token and provides direct permissions to the user based on the token. Our application provides the username as well to match a unique user within the service database to provide extended permissions. Since we also use per-transaction random keys, the state is unable to be cached.
This model supports isolation (UI developers are unable to eavesdrop), allows a central security with a delegation aspect, and the ability to provide centralized services. Such as providing a central forum, trending (statistical compiled) data stored centrally (which maybe restricted to paying clients), or maybe even weblogs as we provide to each of our service databases to make decisions to gauge a fraudulent checkout request.
SERVERS TABLE Minimum of id (unique key), domain, adminuser
|
| -------> OPTIONAL Access Control List
|
SERVER USER CONTROL LIST Minimum of id (unique key), serverid, actual url you wish to protect.
OPTIONAL restrictions on AdminOnly, NoSearchEngines, Restricted (Authentication Required)
|
|
| --------> OPTIONAL ENTITY (Sub-Company) Abstraction
|
| --------> OPTIONAL GROUPS
|
|
USERS Minimum of id (unique key), username, serverid (unless you use the entity container which are already tied to the server)
|
|
|
PERMISSIONS Minimum of id (unique key), server_ucl_id, groupid/entityid/userid (Depending on preference)
SESSIONS Minimum of id (unique key), serverid, userid if authenticated
Provide the sessionid and either userid or username to tamper-resistant proxy code that will provide it to the end-user UI.
We have a lot of bells and whistles in our app to provide a more robust multi-tenant solution, but this is the basics of what we are doing.
The objective is to totally isolate each company into it's own container. Proxy code of some sort should help. C++ is not required, just need code to sit in front of the web application. Also if you want to provide security in one database and shared services in another database, your shared database, which presumably will also be under your client's control, would be allowed to query directly to the security database to see if the user is logged in based on the tokens provided to it, such as when the user posts.
There are no error messages to humans using this model. If anything, any errors would either be pushed via some type of messaging que, email, or log files. The authentication layer is between the proxy code and the security database. User management is within an application that you create for the end-user companies.
This model also scales well with SQL Azure in the middle if needed as it does not need anything like CLR, FT, or etc. The only true weak point will always be the proxy code for security enthusiast. Physical security, and limited user access should lock that down.
Hope I wasn't to verbose and that this helps!
Best Answer
You could utilize a sequence object to facilitate the mechanism of generating the IDs if that is the sole purpose of the CommentParents table. Take a look at the TechNet documentation for the TechNet SQL Server 2014 Sequence Documentation for more detailed information. What this sequence object will do that your current table can't do is allow you to grab a value and assign it to a variable ahead of time without worrying about doing an INSERT/SELECT SCOPE_IDENTITY() process. The advantage here is at great scale the current design you have would break down due to the need to maintain metadata overhead where SQL Server has caching for Sequence objects.