Finding data from dynamic tables using Microsoft SQL Server

Question

I have an organization table as follows:

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 for creating a dynamic table for a project.

If any new organization is created successfully, we call the stored procedure to create a project table for this organization.

Each organization has its own project table. Thus, the name of the project table is dynamic for each organization, and this name is stored in the 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 am currently working on integrating a search function. Users or anonymous users can search for data based on the logic below:

• Search with an organization name or organization code.
• Search with a project name or project invitation.
• Search with project address, city, state, country

I know that it is very simple to find a search result for the name of the organization and invite the code, because all the content is in one table.

But it’s more difficult to get a search result for projects (name or invitation code) due to the dynamic name of the table. I found this link in How to get data from multiple dynamic tables? , so I think this is not the best solution, because the search is very fast.

The reason we share project tables based on organization is because in our requirement they clearly state that "we have 1,000,000 organizations, but each organization has more than 1 million projects." I hope you understand the concept that we do not want to drop projects 1,000,000 (Organization) * 1 million = XXXXXX in one table.

Questions:

• How can we effectively search for project data?
• Does our concept mean a separate project table is worse? Do you have any suggestion to handle much better?
• Is there an efficient way in C #?

Tools and technology:

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

Answer 1

I think you can create a VIEW by joining 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 should be close enough to query efficiency in source tables.

Answer 2

If you need a quick search, you can do this by jeopardizing the recording / insert. Of the following three points:

• Search with the name of the organization or organization.
• Search with a project name or project invitation code.
• Search with project address, city, state, country

First you say as you said. Let's look at 2 and 3. I would suggest a general way with which you can process both points, let's take the address as an example.

• Create an Address table with the AddressId and Address columns, save only a unique address in this table. You can make the address unique. Keep the address in the Organization and Project table, not the full address. This will save space and avoid spelling errors.
• Create an OrganizationationAddressMap table with the column identifier, AddressId, ProjectId, and OrganizationIdation. Save only the unique row in this table.
• When you are looking for an address, look above the two tables to determine the corresponding projectId and OrganizationIdation. And then, based on these two identifiers, it queries the correct table.

In the above scenario, you will look for a narrower number of lines for the address. Similarly, create two tables for each query: project name, project invitation code, city, state, and country.