Search data from dynamic tables using Microsoft SQL Server

Question

I have an organization table like this:

OrgID | OrgInviteCode | OrgName       | Status | ProjectTableName | InsertOn
-------------------------------------------------------------------------------------------
 1    | RC12T67       | Organization1 | Active |  Project1        | 2015-12-19 15:37:43.333
 2    | BC56uI7       | Organization2 | Active |  Project2        | 2015-12-19 15:37:43.333
 3    | ORG1456       | Organization3 | Active |  Project3        | 2015-12-19 15:37:43.333
 4    | ORG2856       | Organization4 | Active |  Project4        | 2015-12-19 15:37:43.333

And I have a stored procedure to create a dynamic table for project.

If any new organization is created successfully then we call the stored procedure to create the project table for that organization.

Each organization has its own project table. So the project table name is dynamic for each org and it's name stored in organization table.

Organization1 --> Project table

ProjectID | OrgID | ProjectName | ProjectInvideCode |   Address1        | Address2 |  City      |State    |   ZIP      |  Country
-------------------------------------------------------------------------------------------------------------------------------
1         |   1   |  Org1Proj1   |  XJ34590         | 235 Harrison St. |            | Syracuse  | AK      |  23456234    |  US
2         |   1   |  Org1Proj2   |  JKI8907         | 35 Sterling  St. |            | Syracuse  | NY      |  23456456    |  US

Organization2 --> Project table

ProjectID | OrgID | ProjectName | ProjectInvideCode |   Address1 | Address2 |  City       |State    |   ZIP    |  Country
-------------------------------------------------------------------------------------------------------------------------------
1         |   2   |  Org2Proj1   |  RUIO90          | 90 Ram St. |          | Los Angeles | CA    |  23456234    |  US
2         |   2   |  Org2Proj2   |  KLOP907         | 35 Wide St.|          | Chicago     | IL    |  23456456    |  US

I'm currently working in integrating the search feature. Users or Anonymous users may search data based on the below logic:

• Search with organization name or organization invite code.
• Search with project name or project invite code.
• Search with project address, city, state, country

I know it's very simple to find the search result for organization name and invite code, because all the content is resides in same table.

But it's more complicated to get the search result for the projects(name or invite code) due to dynamic table name. I found this link in How to fetch data from dynamic multiple tables?, so I think it's not a better solution because search needs to very fast.

The reason why we separate the project tables based on organization because in our requirement they clearly mention that "We have 1000000 organization, but each organization having more then 1 Million projects". Hope you understand the concept we don't want to dump the 1000000(Organization) * 1 Million = XXXXXX projects in single table.

Questions:

• How we can search the projects data in effective way?
• Does our project separate table concept is worst? Do you have any suggestion to handle in much better?
• Is there any effective way in C#?

Tools and Technology:

• Asp.Net 4.5, C#
• MVC 5
• Entity Framework code first
• SQL Server 2012

Answer 1

I think you can create VIEW combining all project tables

SELECT
    REPLACE(
        REPLACE( 
            REPLACE(
            (
                SELECT DISTINCT 'SELECT * FROM Organization O JOIN ' 
                    + ProjectTableName  
                    + ' PT ON O.OrgId = PT.OrgId WHERE O.OrgId = ' + convert(varchar(10), OrgId) 
                    as [text()] FROM Organization
                FOR XML PATH ('DELIMITER')  
            ), '</DELIMITER><DELIMITER>', ' 
            UNION ALL 
            '), '</DELIMITER>', '')
    ,'<DELIMITER>', 'CREATE VIEW Organization_Projects 
AS
')

it must be quite near in efficency to queries on source tables.

Answer 2

If you want faster lookup, you can do this by compromising during write/insertation time. Out of following three points:

• Search with organization name or organization invite code.
• Search with project name or project invite code.
• Search with project address, city, state, country

First is straigt forward as you said. Lets look at 2 and 3. I would suggest a generic way by which you can handle both the points, lets take address as example.

1. Create table Address with column AddressId and Address, store only unique address in this table.You can make address as unique key. Store addressId in Organisation and Project table and not complete address. This will save space and avoid spelling error
2. Create table OrganisationAddressMap with column id, AddressId, ProjectId and OrganisationId. Store only unique row in this table
3. When you search for address, look in above two table to identify relevant projectId and OrganisationId. And then based on these two ids query correct table.

In above scenario you will be searcing for very less no of rows for address. Similarly create two tables for each query term like project name, project invite code, city, state and country.